Invalid Syntax

[Kim]

I keep getting "Invalid Syntax" with the following
expression. Does anyone know why? I've been staring at
this for a while with no luck.

Flag: IIf(([correct_start_time]>=#6:00:00 AM#) And
([correct_start_time]<=#5:59:59 PM#) And
([correct_end_time]>=#6:00:00 AM#) And ([correct_end_time]
<=#5:59:59 AM#),"Day",IIf((([correct_start_time]>=#6:00:00
PM#) And ([correct_start_time]<=#11:59:59 PM#)) OR
(([correct_start_time]>=#12:00:00 AM#) And
([correct_start_time]<=#5:59:59 AM#) And (([End_Date]=
[Start_Date]+1) OR[End_Date]=[Start_Date])),"Night",IIf
(([Start_Date]=[End_Date]),"Split,"Other")))

 

[John Spencer (MVP)]

Don't know why your syntax error exists. I usually try to take something as
complex as this into a user-defined function written in vba. If I try to do it
in an SQL statement directly, then I would set it up in the format below in a
text processor to better see if I had the parentheses in the correct places and
if I had matching parens. The other technique would be to build it up one chunk
at a time.

Flag:
IIf(
[correct_start_time]>=#6:00:00 AM# And
[correct_start_time]<=#5:59:59 PM# And
[correct_end_time]>=#6:00:00 AM# And
[correct_end_time]<=#5:59:59 AM#,
"Day"

,IIf(
(([correct_start_time]>=#6:00:00 PM# And
[correct_start_time]<=#11:59:59 PM#)
OR
([correct_start_time]>=#12:00:00 AM# And
[correct_start_time]<=#5:59:59 AM#))
And
([End_Date]=[Start_Date]+1 OR
[End_Date]=[Start_Date]),
"Night"

,IIf (
[Start_Date]=[End_Date],"Split,"Other"
)
)
)

I think the above might do what you want and it should be syntactically(?) correct.