Problems with IIF function in criteria formula 1

[therayster]

I'm trying to generate a field in a query that will group records by when, in the course of a week, they were made. This is the formula I've come up with:

[tt]
group:iif(Weekday([PRINT_DT_TM])<4,&quot;A&quot;,
iif(OR(
and(Weekday([PRINT_DT_TM])=4,timevalue([print_dt_tm])>#3:30:00 PM#),
and(Weekday([PRINT_DT_TM])=5,timevalue([print_dt_tm])>#6:00:00 AM#)
)
,&quot;A&quot;,&quot;B&quot)
[/tt]

But Access gives me an error, &quot;The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier...&quot;, with the first word after the opening parentheses of the second IIF function highlighted. I've rearranged the function, but continue to get the error. What am I missing?

 

[nickjar2]

after the 2nd iif what is the OR for?

Nick

 

[TTThio]

Probably like this:

iif(Weekday([PRINT_DT_TM])<4,&quot;A&quot;,
iif(Weekday([PRINT_DT_TM])=4,
timevalue([print_dt_tm])>#3:30:00 PM#),
iif(Weekday([PRINT_DT_TM])=5,
timevalue([print_dt_tm])>#6:00:00AM#),&quot;B&quot;)))

 

[therayster]

nickjar2: The OR should evaluate to true if either of the ANDs are true. I'm trying to perform a low range - high range comparision in one step rather than nesting another IIF.

 

[therayster]

TTThio: That's not gonna work. Let me be more specific. &quot;A&quot; group should be anything before Wednesday (Weekday 4). Wednesday splits at 3:30 PM, but the first part of Wednesday is Group B, the second part is group A and group A can roll over into Thursday(Weekday 5) morning, but after 6:00 AM on Thursday will be B group again (I know, my ears bleed when I think about it...). I need the ANDs to specify a day of week/time of day combination criteria.

 

[TTThio]

iif((Weekday([PRINT_DT_TM])<4)
OR
(Weekday([PRINT_DT_TM])=4 AND timevalue([print_dt_tm])
>#3:30:00 PM#)
OR
(Weekday([PRINT_DT_TM])=5 AND timevalue([print_dt_tm])
<#6:00:00AM#), &quot;A&quot;,&quot;B&quot

Let's try this.

 

[therayster]

That's what I get for making it complicated... That worked, thanks.