Conditional Criteria

[croy]

I'm stumped over what should be such a simple criteria.

A DLookup value for a field who's table is not included in
this query could return numerical values of 1 (Red), 2
(Blue), 3 (Green), or 4 (All, or Any).

In this query, I want the criteria for a field [color] to
match the lookup value, *unless* the lookup value is 4 -- in
that case, I don't want any criteria at all.

I've read that using lookups for criteria can slow things
down, but I'm not sure how to go about this otherwise.

Hints appreciated.

 

[ghetto_banjo]

Not sure how you have things setup, but normally you wouldnt want a
DLookup in a criteria. Why not include that table in the query?

Anyways, something like this might work for the criteria statement

iif( [color] < 4, [color], Like "*")

 

[croy]

Not sure how you have things setup, but normally you wouldnt want a
DLookup in a criteria. Why not include that table in the query?

I know this is a little flakey, but it's primarily a
data-entry form. But it gets a lot of use for reviewing as
well. If I add the necessary table, the recordset is not
updatable.

Anyways, something like this might work for the criteria statement

iif( [color] < 4, [color], Like "*")

That's the sort of thing I've been trying, but... the field
is Long Integer. I tried

IIf(DLookup("Color", "tblColor")=4, <>, _
(DLookup("Color", "tblColor")

.... but Access (2002) didn't care for that.

I also tried a conversion field (ColorTest: Color"")
with
IIf(DLookup("Color", "tblColor")=4, "*", _
(DLookup("Color", "tblColor")

.... but no joy.

Thanks for the reply.

 

[John Spencer]

Not enough information. First problem, your DLookup would always return
the same value since you did not use the third argument to specify
criteria to identify a record in the tblColor.

If you are using a parameter prompt the criteria might look like

Field:SomeField
Criteria: = [What color] or [What color] = 4

If you enter any value from 1 to 3, the records returned will match the
value 1, 2, or 3. If you enter 4, all the records will be returned
based on the criteria after the or.

If this is not what you are attempting to do, try to explain in a bit
more detail - what field are you applying criteria against; how are you
getting the value (1 to 4) into the criteria?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[croy]

I don't know why my message appeared in the middle of this
thread. This thread does have exactly the same title as I
put on my new message, but I didn't think that was supposed
to matter. Oh well...