Help with a where clause in access query

[robertsdgm]

Hello All
How do I go about in access creating a where clause or using LIKE or something similar?
I have an access query that works just fine and returns 200 records. But now I want to further qualify by adding a where clause or using like and finding all the first names with or like DAN and then displaying the 50 or so records.
How do I do this with in design view? What type of expression might I build?
thks!
Dan

 

[lespaul]

in the criteria of the query designer add

LIKE "DAN*"

Any time you use like you need to have the '*'. If you have it at the beginning: LIKE '*OME'

it will find:

TOME
HOME

If you have it at the end: LIKE 'MEN*'

It will find:

MEN
MENTION

if you have it surrounded Like '*EM*'

It will find:

MEMBER
EMBER
HAREM


HTH




Leslie

 

[Yarcadian]

Under the field where you want to pull specific data, type this for the "Criteria" [Enter First Name Please]

What this does is prompt the user to key in a name, and it will only return results that match what they've keyed in.

Hope this has helped.

David

 

[robertsdgm]

Thks!
and how about if my query returns a null value for a particular attribute for a given record..how would I print the word "unavailable" for instance?
thks!
Dan

 

[KornGeek]

robertsdgm,
Look into the Nz() function. It allows you to replace a Null value with another value. If I understand correctly, this should help you do what you want to do.

Yarcadian,
As I matter of personal preference, I no longer use parameters of that style in my queries. I've instead created my own forms to prompt for the data, and then reference controls on the forms. This gives you more control over the look of the interface (including allowing combo boxes, etc.) as well as error checking on the data entered. You might want to try this sometime. My end users were impressed when I switched over.

 

[Yarcadian]

KornGeek,
I use both methods, it really depends on the type of data being queried as to which one I use. For names, I agree with you that a form with a combo box's datasource pointing to that table and grouped by name, would have been best.

I have used this in the query's FIELD box pretty succesfully:

Name:iif([field name]is null,"Unavailable,[field name])