Prompt in a Query

[GGleason]

I have a query where there is a prompt on a field. When I don’t put a value in the prompt it returns no records.

Is there something that you can place in the prompt where it will return all records?

tia,
GGleason

 

[ChrisCalvert]

I am guessing you have tried '*' ? (not using the quotes)
Can you post the query (or the pertinent part) ?

 

[WildHare]

GG - I've seen this in a recent ElementK monthly rag somewhere - I think you want to graft a Like("*&quot guy as an ORed part of your where-clause:

Select ....
where xxx = [Enter something] or
wehre XXX LIKE("*&quot

or something like that.I have to run to an off-site meeting but this should get you started.

Jim

How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[GGleason]

This is what I found out so far. The “*” does not work (returns no records). Changing the SELECT criteria works when you don’t have a value, but when you enter a value it returns all records, rather than the ones specified by the prompt. If I could figure out how to detect if the prompt is null, I might be able to lick the problem.

Thanks,
GGleason

 

[barbshop]

I think what you need to do is this:

In the field that you want the prompt to be for enter something like this in the criteria line:

[TYPE Member Key or press ENTER for ALL]
where "Member Key" is the name of the field that you want to prompt for.

Then set up a calculated field that will look something like this:
On the "Field" line
Expr1: [TYPE Member Key or press ENTER for ALL
again - "Member Key" would be replaced with the name of the field you are prompting for.

Then in the "Or" portion of the criteria line for the calculated field type:

Is Null

Now when the user is prompted for Member Key (or whatever name you give the field) they can either type in a value or if they want all records, press enter.

Hope this helps.

 

[GGleason]

barbshop,

Your suggestion worked! For the sake of clarifying for others, I had to put [Member Key] in the Criteria row for the expression in design view of the query. I tried experimenting by putting a wildcard in the prompt, but that didn’t work.

Thanks,
GGleason