calculate a variable base on the time entered

B

Barbara

I wrote this in vb but it does not work. I don't know what I am doing wrong.
Please help.

If ([time] >= #9:00 AM# And [time] <= #12:00 PM#) Then meridian = "AM"
If ([time] > #12:00 PM# And [time] <= "8:00 PM#) Then meridian = "PM"
If ([time] > #8:00 PM#And [time] < "9:00 AM#) Then meridian = "Eve"
 
M

Marshall Barton

Barbara said:
I wrote this in vb but it does not work. I don't know what I am doing wrong.
Please help.

If ([time] >= #9:00 AM# And [time] <= #12:00 PM#) Then meridian = "AM"
If ([time] > #12:00 PM# And [time] <= "8:00 PM#) Then meridian = "PM"
If ([time] > #8:00 PM#And [time] < "9:00 AM#) Then meridian = "Eve"
Click to expand...

"does not work" is a singularly uninformative symptom to
help formulate a diagnosis ;-)

What is in the time field? If it contains a date part, then
the comparisons can not work and you need to strip away the
date part:
If TimeValue([time]) >= #9:00 AM# And TimeValue[time]) <=
#12:00 PM# Then ...

Once you verify that you do not have a date part in the time
value, then the first two If statements should work. The
third one needs to use OR instead of AND

In the future, please use Copy/Paste when posting your
expressions, VBA code, or queries so we don't waste time
discussing typos (e.g. the missing space in the third If.
 
J

John Spencer

Are you attempting to do this in a query? IF so, the expression should read
like the following:

IIF([time] >= #9:00 AM# And [time] <= #12:00 PM#, "AM",
IIF([time] > #12:00 PM# And [time] <= #8:00 PM#,"PM",
IIF([time] > #8:00 PM# And [time] < #9:00 AM#,"Eve")))

Note:
--- times are delimited with # signs, not quotes.
--- In a query, you use IIF and three arguments
--- Time is not a good name for a field, since there is a Time function to
return the current time.
--- Make sure the Time field does not contain a date in addition to a time.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Top