calculate a variable base on the time entered

  • Thread starter Thread starter Barbara
  • Start date Start date
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"
 
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.
 
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
 
Back
Top