dacs.doc electric

 

Syntax Refresher

By Richard F. DiFranco

 

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 I’m sure it is longer than it needs to be.

The problem

I recently started a new job in the telephone department of a very large company. I get all the bills that can’t 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 couldn’t get the right syntax for the criteria. Here is an example that I used:

Like "*[namestring]*"
("namestring" is the parameter).

When I used an explicit name like "*Smith*" I would get a good result. Or when I used the full name as a parameter - ex. Smith, Fred - I got a good result.

The Back Office Solution

I 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 didn’t quite get it. The best advice was to break the name field into two fields. This really wouldn’t help because I wanted to search on the larger memo field.

Experimentation

The 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 I’ll 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.

BackHomeNext