Criteria Problem With Iif Statement And Time Values 2

[xweyer]

I have a table (t_Times) with a Date/Time field (Times) formated as a Medium Time. I also have a form with a combo box linked to a query. I'm trying to use a option box to restrict the values displayed in the combo box to AM or PM values based on the option selected by the user.

It is the criteria for the t_Times field in the query that is giving me problems. I'm trying to use the criteria below to produce the desired results. If I enter any of the three criteria included in the IIf statement independently they work fine (<#12:00 PM# produces only AM times, >#12:00 PM# produces only PM times, etc.) but when I roll them into the Iif statement I get weird results. 1= no rows returned, 2 = 12:00 AM !?.

I know I've got to be missing something obvious but I can't locate the error.

IIf([Forms]![F_WrkWkDay]![opTmOn]=1,([t_Times].[Times])<#12:00:00 PM#,IIf([Forms]![F_WrkWkDay]![opTmOn]=2,([t_Times].[Times])>#12:00:00 PM#,[Times]))

 

[PHV]

The IIf is in the criteria cell of Times in the query grid ?
Switch to the SQL view pane and post the WHERE clause.

a starting point:
WHERE (([Forms]![F_WrkWkDay]![opTmOn]=1 AND t_Times.Times<#12:00:00 PM#)
OR ([Forms]![F_WrkWkDay]![opTmOn]=2 AND t_Times.Times>#12:00:00 PM#)
OR [Forms]![F_WrkWkDay]![opTmOn] Not In (1,2))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xweyer]

Thanks Phv,

That worked great.