In a recent DACS.DOC, Allan Ostergren asked for articles showing how people Solve every day problems. I want to share my recent experience. Many will find it to be a short refresher and Im sure it is longer than it needs to be. The problemI recently started a new job in the telephone department of a very large company. I get all the bills that cant be delivered to the right people. I also get all the calls for all phone problems, and have to make a sort of inventory of cell phones. My boss told me that I should start a database of all the problems that I came across. The best tool for the job seemed to be Microsoft Access. So I built a database with a table consisting of three fields - the phone number, a name field, and a notes field. The phone number field is just the three-digit exchange followed by the extension. The name field is last name, comma, space, first name. The notes field is a memo that includes short notes and text sentences, which I like to be wordy. So far the database is up to 376 records and growing. My problem was searching for records. Searching the phone field is easy because the format is exact. It is three digits hyphen and four digits and all the numbers are unique. The name field is different. Sometimes I want to search on the last name only. Doing a find on the notes field is just about impossible. I tried to develop a parameter query to search the name field but I couldnt get the right syntax for the criteria. Here is an example that I used: Like "*[namestring]*" The Back Office SolutionI took the question to the back office SIG--Mostly because that was the group that had the necessary expertise, and was meeting this month. After explaining the problem, it was suggested that I use an "instr" intrinsic function. We experimented with the instr but didnt quite get it. The best advice was to break the name field into two fields. This really wouldnt help because I wanted to search on the larger memo field. ExperimentationThe next day at work (in between real work) I tried to experiment with "instr" but found that it would just return a true or false and not give me the result of a correct sub set. I knew there had to be a way of using parameter and a wild card. So I tried the following in the criteria Like "[namestring] "*" This gave me a syntax error and a flash of insight. I next tried Like "[namestring] & "*" . . . and there it was. I could then generalize it to Like "*" & [namestring] & "*" . . . and I could search on any string in the field. For the notes field, I built another parameter query with the same syntax but a different parameter. Like "*" & [notestring] & "*" There probably are better ways of accomplishing the same thing, and maybe some day Ill find them. For now this will do me well. I hope this has helped someone else to solve a similar problem and maybe refreshed a few good memories. |
Rich DiFranco: DACS member since the Brookfield library days. Programmed in FORTRAN, COBOL, and Paradox Now learning the new platforms as well as producing an occasional essay. |