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
'====================================================

 

[MGFoster]

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe you can use the IN predicate like the following instead of the
DLookUp() function:

Criteria: In (1,2,3)

Since the only values you want are 1,2,3 the In predicate "says" the
Color should equal any of the values in the list inside the parentheses.

DLookUp() requires a criteria, since it only returns one value:

DLookUp("column_name", "table_name", "criteria")

The criteria must return a unique row (record). If it returns more than
one row of data, the "first" row's value will be returned. The first
row could be a random row since the table's row are not ordered in any
way. Therefore, it is better to use a unique ID as part of the criteria
so you "know" exactly what should be returned. Read the Access Help
DLookUp() Function article for more info (hint: w/ Access open - in the
Debug window [hit Ctrl-G]; type in the function name; put the cursor on
the name & hit the F1 key).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBScgllIechKqOuFEgEQLPzACg15Pavgz87dStBLc1oaLEhGiKWNMAn1qs
xR4PEWw1p6m0TvwaHPLPb/+m
=yDKo
-----END PGP SIGNATURE-----

 

[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...