Determine Period from Time( )

[Guest]

I have a table that, as a default value, place the Time() into the field. I
would like to determine what period of the school day that event occurred.

ie: Time()=8:33:01am That period would be 1st

I was thinking of storing the start and end times of each period in a table,
tblPeriods, and then using a query to determine what period, I just don't
know how to do that.

Any input would be great.

 

[Douglas J. Steele]

If you had a table Periods with StartTime, EndTime and PeriodName, you could
use:

DLookup("PeriodName", "Period" "Time() Between StartTime And EndTime")

 

[John W. Vinson]

I have a table that, as a default value, place the Time() into the field. I
would like to determine what period of the school day that event occurred.

ie: Time()=8:33:01am That period would be 1st

You don't care which day? All Time() values are on December 30, 1899 - the
base date of Access' date/time values.

I was thinking of storing the start and end times of each period in a table,
tblPeriods, and then using a query to determine what period, I just don't
know how to do that.

Excellent thought. Make a table Periods:

Period StartTime EndTime
1st 08:00 08:50
2nd 08:55 09:45
<etc>

although it's best if there are no gaps.

You can then create what's called a "Non Equi Join" query. Start by creating a
query joining your table's time field (which, btw, should NOT use the reserved
word Time as a fieldname!) to the StartTime field in Period. Then switch to
SQL view in the query and edit

INNER JOIN Periods ON yourtable.timefield = Periods.StartTime

to

INNER JOIN Periods ON yourtable.timefield >= Periods.StartTime AND
yourtable.timefield <= Periods.EndTime

You can then pick up the Period field in your query.

John W. Vinson [MVP]

 

[Guest]

Great ideas. Thanks for your help as always!