If/Then Using Time (Over 24 hrs)

[corrinebean]

I'm a novice access user, self-taught...am trying to set up an If/Then statement in a query to display either "Night" (7PM-7AM) or "Day" (7am-7pm) given the time associated with an Incident Date/Time ([Incident Date]).

I know the problem I'm having is due to the fact that the times span over a 24 hour period...I've been reading different posts but can't seem to figure it out.

 

[student]

Welcome aboard - I'm all new to this as well.

One (rather messy) way to do it would be to turn it into four iif statements instead of trying to bunch into just two. i.e. 7pm-12pm & 0am-7am = night, 7am-12am & 0pm-7pm = day. I'm sure someone here will come up with a cleaner method though if you don't need it immediately...

 

[vbaInet]

Some aircode:

IIF([[COLOR=Red]Date/Time Field[/COLOR]] BETWEEN CDate(DateValue([[COLOR=Red]Date/Time Field[/COLOR]]) & " 19:00:00") AND CDate(DateValue([[COLOR=Red]Date/Time Field[/COLOR]] + 1) & " 06:59:59"), "Night", "Day")

The red bits are what you amend.

Your difference of hours overlaps too. I've used:

Night: 7 p.m. to 6.59 a.m.
Day: 7 a.m. to 6.59 p.m.

 

[corrinebean]

Perfect, thank you so much!

 

[vbaInet]

You're welcome!