Output "night, morning, afternoon, evening" based on times
Experts:
I need some assistance with determining a query (in MS-Access) which outputs a text value (e.g., night, morning, afternoon, evening). Please see attached MDB.
The values are based on the following times:
00:00 to 05:59 AM = Night
06:00 to 11:59 AM = Morning
12:00 to 05:59 PM = Afternoon
06:00 to 11:59 PM = Evening
In my query, I converted times into a 24-hour format to show which is the equivalent of:
00:00 to 05:59 = Night
06:00 to 11:59 = Morning
12:00 to 17:59 = Afternoon
18:00 to 23:59 PM = Evening
Based on the module basConversion, I would like to produce the "night, morning, afternoon, and evening" output.
My question: How do I modify the query to accomplish this task?
Thank you,
EEH
BetweenTimes.accdb
I need some assistance with determining a query (in MS-Access) which outputs a text value (e.g., night, morning, afternoon, evening). Please see attached MDB.
The values are based on the following times:
00:00 to 05:59 AM = Night
06:00 to 11:59 AM = Morning
12:00 to 05:59 PM = Afternoon
06:00 to 11:59 PM = Evening
In my query, I converted times into a 24-hour format to show which is the equivalent of:
00:00 to 05:59 = Night
06:00 to 11:59 = Morning
12:00 to 17:59 = Afternoon
18:00 to 23:59 PM = Evening
Based on the module basConversion, I would like to produce the "night, morning, afternoon, and evening" output.
My question: How do I modify the query to accomplish this task?
Thank you,
EEH
BetweenTimes.accdb
ASKER
Shaun:
Did you check out the attached database.
The below code doesn't seem to work for me in SQL.
SWITCH(HOUR([EventTime]) < 6, "Night", HOUR([EventTime]) < 12, "Morning", HOUR([EventTime]) < 18, "Afternoon", HOUR([EventTime]) < 24, "Evening")
What's missing?
EEH
Did you check out the attached database.
The below code doesn't seem to work for me in SQL.
SWITCH(HOUR([EventTime]) < 6, "Night", HOUR([EventTime]) < 12, "Morning", HOUR([EventTime]) < 18, "Afternoon", HOUR([EventTime]) < 24, "Evening")
What's missing?
EEH
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Should this line be merely copied into a SQL window? Does this work in the database I provided?
If it works in the DB, would you please zip file and attach it? Thank you.
EEH
If it works in the DB, would you please zip file and attach it? Thank you.
EEH
I placed that statement into query1 and ran it.
ASKER
It doesn't work as is.
I will wait for someone else to respond and provide more helpful information/assistance.
I will wait for someone else to respond and provide more helpful information/assistance.
SWITCH(HOUR([your date/time field]) < 6, "Night", HOUR([your date/time field]) < 12, "Morning", HOUR([your date/time field]) < 18, "Afternoon", HOUR([your date/time field]) < 24, "Evening")