Need immediate help doing an IIf statement in a query

[bcasper]

Here's a shortened version of my current SQL statement:
SELECT IMAGING_FOLDER_T.ICN
FROM IMAGING_BATCH_T INNER JOIN IMAGING_FOLDER_T ON
IMAGING_BATCH_T.BATCH_NM = IMAGING_FOLDER_T.BATCH_NM
GROUP BY IMAGING_FOLDER_T.ICN
HAVING (((IMAGING_FOLDER_T.ICN) Like [Enter the Julian
date:] And (IMAGING_FOLDER_T.ICN) Like "?????")); Here's
what I want the SQL to do: SELECT
[Contact/Division].Contract, IMAGING_FOLDER_T.ICN
FROM (IMAGING_BATCH_T INNER JOIN IMAGING_FOLDER_T ON
IMAGING_BATCH_T.BATCH_NM = IMAGING_FOLDER_T.BATCH_NM)
INNER JOIN [Contact/Division] ON
IMAGING_BATCH_T.PROCESS_NM = [Contact/Division].Process_NM
GROUP BY [Contact/Division].Contract, IMAGING_FOLDER_T.ICN
HAVING ((([Contact/Division].Contract)=[Enter the
contract:]) AND ((IMAGING_FOLDER_T.ICN)=IIf([Contract]
="Medicare",(IMAGING_FOLDER_T.ICN) Like "??" & [Enter the
Medicare Julian Date] & "??????",(IMAGING_FOLDER_T.ICN)
Like [Enter the Julian date:] & "?????"))); What I am
trying to do is if the user enters a certain value for the
contract field, it will can what the criteria is of the
ICN field but the IIf statement in the ICN criteria keeps
defaulting to true even when it shouldn't. Please help.

 

[John Vinson]

What I am
trying to do is if the user enters a certain value for the
contract field, it will can what the criteria is of the
ICN field

I'm sorry, you'll have to parse that request. "it will can"????

 

[John Spencer (MVP)]

Boy, is that hard to read. Paragraphs would be nice. You can't change the
operators or the where clause using an IIF statement. You have to do that by
building your SQL with VBA.

HOWEVER, you might try something like the following UNTESTED SQL.

SELECT [Contact/Division].Contract,
IMAGING_FOLDER_T.ICN
FROM (IMAGING_BATCH_T
INNER JOIN IMAGING_FOLDER_T ON
IMAGING_BATCH_T.BATCH_NM = IMAGING_FOLDER_T.BATCH_NM)
INNER JOIN [Contact/Division]
ON IMAGING_BATCH_T.PROCESS_NM = [Contact/Division].Process_NM
GROUP BY [Contact/Division].Contract, IMAGING_FOLDER_T.ICN
HAVING [Contact/Division].Contract=[Enter the contract:]
AND IMAGING_FOLDER_T.ICN LIKE
IIf([Contract]="Medicare",
"??" & [Enter the Julian Date:] & "??????",
[Enter the Julian date:] & "?????")

I did change your prompt so you would only get one prompt. If you need to
specify this different ways, you might consider using a form to get the
information and calling the query from the form.

 

[Guest]

This should be easier to read and clearer:

Here's a shortened version of my current SQL statement:
SELECT IMAGING_FOLDER_T.ICN FROM IMAGING_BATCH_T INNER
JOIN IMAGING_FOLDER_T ON IMAGING_BATCH_T.BATCH_NM =
IMAGING_FOLDER_T.BATCH_NM GROUP BY IMAGING_FOLDER_T.ICN
HAVING (((IMAGING_FOLDER_T.ICN) Like [Enter the Julian
date:] And (IMAGING_FOLDER_T.ICN) Like "?????"));

Here's what I want the SQL to somewhat look like: SELECT
[Contact/Division].Contract, IMAGING_FOLDER_T.ICN
FROM (IMAGING_BATCH_T INNER JOIN IMAGING_FOLDER_T ON
IMAGING_BATCH_T.BATCH_NM = IMAGING_FOLDER_T.BATCH_NM)
INNER JOIN [Contact/Division] ON MAGING_BATCH_T.PROCESS_NM
= [Contact/Division].Process_NM GROUP BY
[Contact/Division].Contract, IMAGING_FOLDER_T.ICN
HAVING ((([Contact/Division].Contract)=[Enter the
contract:]) AND ((IMAGING_FOLDER_T.ICN)=IIf([Contract]
="Medicare",(IMAGING_FOLDER_T.ICN) Like "??" & [Enter the
Medicare Julian Date] & "??????",(IMAGING_FOLDER_T.ICN)
Like [Enter the Julian date:] & "?????")));

What I am trying to do is if the user enters a certain
value for the contract field, it will change what the
criteria is of the ICN field but the IIf statement in the
ICN criteria keeps defaulting to true when it shouldn't.
Can this be done?? Please help.