"Or" in criteria field?

nightflight

Registered User.
Local time
Today, 22:08
Joined
Mar 15, 2011
Messages
24
I have a combo box with three options, "EA" "FAW" and "FAWR"
If "EA" is selected i want the query to return results where certtype="EA",
if "FAW" is selected i want the query to return results where certtype="FAW"
if "FAWR" is selected i want the query to return results where certtype="FAW" or "FAWR"

So what've I done wrong?

Code:
SELECT DISTINCTROW tbl_cert2.certname, tbl_cert2.certtype, tbl_cert2.certnum, tbl_cert2.certexpires
FROM tbl_people, tbl_cert2
GROUP BY tbl_cert2.certname, tbl_cert2.certtype, tbl_cert2.certnum, tbl_cert2.certexpires
HAVING (((tbl_cert2.certname)=Forms!frm_Person!cbo_Name) And ((tbl_cert2.certtype)=IIf(Forms!frm_Person!cbo_cert="FAWR",(tbl_cert2.certtype)="FAW" Or (tbl_cert2.certtype)="FAWR",Forms!frm_Person!cbo_cert)))
ORDER BY tbl_cert2.certexpires DESC;
 
If you're running the query from the form, you could just put
Code:
comboname.column(x)
in the criteria, where x is the column number starting from 0.
 
that'll only pull up records with "FAWR" as [certtype] when cbo_cert is "FAWR" won't it?
I need cbo_cert="FAWR" to pull up both [certtype] "FAW" and "FAWR"
 
It will, well spotted!

I'm not sure but you might be able to use your iif statement as a criteria - give it a shot and let me know!
 
It works if cbo_cert is "EA" or "FAW" but when it is "FAWR" and the query is run an error pops up...
"this expression is typed incorrectly, or is too complex to be evaluated.....etc etc"
 
Annoying. Next thing I'd try is to put another (hidden) column in your combo box with the actual criteria in it, run it as before
 
Code:
IIF(Combo="EA",In('EA'),IIF(Combo="FAW",In('FAW'),In('FAW','FAWR')))
 
sorry for being abit limited in my access knowledge, What is the
Code:
In('EA')
(etc) bit meant to mean/be?
i've changed
Code:
combo=
to
Code:
[forms]![frm_person]![cbo_cert]
but left the rest the same as your code box, but now no matter what i select in cbo_cert it's coming up with the "expression is too complex" error.

2nd try, I've replaced my iif criteria in the SQL view with the suggestion above, but that has a "syntax error (operator missing)"?


James: not sure what you mean?
 
Last edited:
Here's an earlier anonymised incarnation from an earlier query i had,
http://www.sendspace.com/file/ww1qrp

just wondering if it'd be simpler to go for a criteria that excludes "EA" when "FAWR" is chosen by cbo_cert
something like
Code:
IIf([Forms]![frm_Person]![cbo_cert]="FAWR",Not ("EA"),[Forms]![frm_Person]![cbo_cert])
that, half works, as in, it'll display results for "EA" and FAW" but not "FAWR" (but its an improvement from first go as it will actually run the query, just it finds no results)
but why isn't it working fully? I presume it's something to do with the
Code:
,Not ("EA"),
section. how do i need to change it to make it work?
 

Users who are viewing this thread

Back
Top Bottom