Lookup a range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field that has the short time value. I need to lookup that time and determine which condition a student meets. It is for an attendance program that will allow a student to scan his ID card. I have worked out the time stamping and now need to determine if the student is present or tardy and in which period he scanned in. I tried the "between" function in the query but it does not work. Does any one have any suggestions?
 
ACCESS stores both date and time in a date/time field, even though you've
set the format to "short time".

Post an example of what you want to find via your query and an example of
the SQL statement that you've tried to use.
--
Ken Snell
<MS ACCESS MVP>

Andrew said:
I have a field that has the short time value. I need to lookup that time
Click to expand...
and determine which condition a student meets. It is for an attendance
program that will allow a student to scan his ID card. I have worked out the
time stamping and now need to determine if the student is present or tardy
and in which period he scanned in. I tried the "between" function in the
query but it does not work. Does any one have any suggestions?
 
I thought this would return all the students’ names that were tardy. IIf ([Time]>7:06, "Tardy", "Present"). I think I need to have a query for each period? I just get a error message

In Excel I use a Vlookup and the cell returns the text that I want. All I do is specify the time intervals in an ascending order and Vlookup return the condition of the time the student scanned in his or her ID card. There are 18 conditions
0:00 Present 0 Perio
7:26 Tardy 0 Perio
7:55 Present 1st Perio
8:13 Tardy 1st Perio
8:42 Present 2nd Perio
9:00 Tardy 2nd Perio
9:29 Present 3rd Perio
9:47 Tardy 3rd Perio
10:26 Present 4th Perio
10:45 Tardy 4th Perio
11:13 Present 5th Perio
11:31 Tardy 5th Perio
12:10 Present 6th Perio
12:29 Tardy 6th Perio
12:57 Present 7th Perio
13:15 Tardy 7th Perio
13:44 Present 8th Perio
14:02 Tardy 8th Perio

Here is what that looks like

123456 Doe, John 3/13/04 8:04 Present 1st Perio

One of the main problems with the Excel solution is having to manually sort the student’s names at the end of the day. I also want to include this in a student report that I can run at the end of the quarter that contains his or her grades and credits.

Thanks. We are a small school and I am trying to do things like this on my own time to help us have more time to teach and automate the paperwork. This is a big step in that process.
 
Can you tell me a bit more about how you're storing the time in that field?
Are you by chance using the Now function?

Once I know how you're writing the time value into the field, I think we can
work this out for you.

--
Ken Snell
<MS ACCESS MVP>

Andrew said:
I thought this would return all the students' names that were tardy. IIf
Click to expand...
([Time]>7:06, "Tardy", "Present"). I think I need to have a query for each
period? I just get a error message.
In Excel I use a Vlookup and the cell returns the text that I want. All I
Click to expand...
do is specify the time intervals in an ascending order and Vlookup return
the condition of the time the student scanned in his or her ID card. There
are 18 conditions:
0:00 Present 0 Period
7:26 Tardy 0 Period
7:55 Present 1st Period
8:13 Tardy 1st Period
8:42 Present 2nd Period
9:00 Tardy 2nd Period
9:29 Present 3rd Period
9:47 Tardy 3rd Period
10:26 Present 4th Period
10:45 Tardy 4th Period
11:13 Present 5th Period
11:31 Tardy 5th Period
12:10 Present 6th Period
12:29 Tardy 6th Period
12:57 Present 7th Period
13:15 Tardy 7th Period
13:44 Present 8th Period
14:02 Tardy 8th Period

Here is what that looks like:

123456 Doe, John 3/13/04 8:04 Present 1st Period

One of the main problems with the Excel solution is having to manually
Click to expand...
sort the student's names at the end of the day. I also want to include this
in a student report that I can run at the end of the quarter that contains
his or her grades and credits.
Thanks. We are a small school and I am trying to do things like this on my
Click to expand...
own time to help us have more time to teach and automate the paperwork. This
is a big step in that process.
 
I am using a NOW() as the time value. Showing the date in one cell and then using Short Time for another. I think I also had to seperate the Date/Time in Excel and then combine it later. I think I could use concatinate to make the step simpler.
 
OK - that is the explanation for why your query isn't working. Now stores
both the date and the time in the field. Thus, when you try to run a query
using just the time as the criterion, you won't get a match because of the
date.

ACCESS stores date/time values as a floating point number: date.time (where
date is the number of days since midnight of 30 December 1899 and where
..time is the fractional part of a 24-hour day represented by the time). For
example, the date and time of March 14, 2004 9:00:00 pm is stored as this
number:
38060.875

What you seek is easily done with your current data. Put a calculated field
in your query, using this expression:
TimeOnly: TimeValue([DateTimeStampField])

Then use your time criterion on this calculated field for doing the
searching.

--
Ken Snell
<MS ACCESS MVP>

Andrew said:
I am using a NOW() as the time value. Showing the date in one cell and
Click to expand...
then using Short Time for another. I think I also had to seperate the
Date/Time in Excel and then combine it later. I think I could use
concatinate to make the step simpler.
 
I have built the following expression and got an error message saying it was too complex. Is there a fix

Condition: IIf([Time]>'14:02',"Tardy 8th Period",IIf([Time]>'14:00',"Present 8th Period", IIf([Time]>'13:15',"Tardy 7th Period",IIf([Time]>'13:13',"Present 7th Period", IIf([Time]>'12:19',"Tardy 6th Period",IIf([Time]>'12:21',"Present 6th Period", IIf([Time]>'11:31',"Tardy 5th Period",IIf([Time]>'11:29',"Present 5th Period", IIf([Time]>'10:45',"Tardy 4th Period",IIf([Time]>'10:43',"Present 4th Period", IIf([Time]>'9:47',"Tardy 3rd Period",IIf([Time]>'9:45',"Present 3rd Period", IIf([Time]>'9:00',"Tardy 2nd Period",IIf([Time]>'8:58',"Present 2nd Period", IIf([Time]>'8:13',"Tardy 1st Period",IIf([Time]>'8:11',"Present 1st Period", IIf([Time]>'7:26',"Tardy 0 Period",IIf([Time]>'7:24',"Present 0 Period")

It works if I only use one set. Is there a way to shorten it
 
I have built the following expression and got an error message saying it was too complex. Is there a fix?

Condition: IIf([Time]>'14:02',"Tardy 8th Period",IIf([Time]>'14:00',"Present 8th Period", IIf([Time]>'13:15',"Tardy 7th Period",IIf([Time]>'13:13',"Present 7th Period", IIf([Time]>'12:19',"Tardy 6th Period",IIf([Time]>'12:21',"Present 6th Period", IIf([Time]>'11:31',"Tardy 5th Period",IIf([Time]>'11:29',"Present 5th Period", IIf([Time]>'10:45',"Tardy 4th Period",IIf([Time]>'10:43',"Present 4th Period", IIf([Time]>'9:47',"Tardy 3rd Period",IIf([Time]>'9:45',"Present 3rd Period", IIf([Time]>'9:00',"Tardy 2nd Period",IIf([Time]>'8:58',"Present 2nd Period", IIf([Time]>'8:13',"Tardy 1st Period",IIf([Time]>'8:11',"Present 1st Period", IIf([Time]>'7:26',"Tardy 0 Period",IIf([Time]>'7:24',"Present 0 Period"))

It works if I only use one set. Is there a way to shorten it?
Click to expand...

By not doing it that way. <g>

Instead, create a table Periods with three fields: Start, End,
Message; fill this table with records like

07:24 07:26 "Present 0 Period"
07:26 08:11 "Tardy 0 Period"
08:11 08:13 "Present 1 Period"

etc. etc.

Then include this table in your Query with a criterion on [Time] of
 
I have built the following expression and got an error message saying it was too complex. Is there a fix?

Condition: IIf([Time]>'14:02',"Tardy 8th Period",IIf([Time]>'14:00',"Present 8th Period", IIf([Time]>'13:15',"Tardy 7th Period",IIf([Time]>'13:13',"Present 7th Period", IIf([Time]>'12:19',"Tardy 6th Period",IIf([Time]>'12:21',"Present 6th Period", IIf([Time]>'11:31',"Tardy 5th Period",IIf([Time]>'11:29',"Present 5th Period", IIf([Time]>'10:45',"Tardy 4th Period",IIf([Time]>'10:43',"Present 4th Period", IIf([Time]>'9:47',"Tardy 3rd Period",IIf([Time]>'9:45',"Present 3rd Period", IIf([Time]>'9:00',"Tardy 2nd Period",IIf([Time]>'8:58',"Present 2nd Period", IIf([Time]>'8:13',"Tardy 1st Period",IIf([Time]>'8:11',"Present 1st Period", IIf([Time]>'7:26',"Tardy 0 Period",IIf([Time]>'7:24',"Present 0 Period"))

It works if I only use one set. Is there a way to shorten it?
Click to expand...

Afterthought: In addition to the table-driven suggestion (which I
still prefer, but might be more complexity than you want), take a look
at the Switch() function. It takes an arbitrary number of pairs of
arguments, and goes through them left to right; if the first of a pair
evaluates to TRUE, the function returns the second and quits. E.g.

Condition: Switch([Time]>'7:24',"Present 0
Period",[Time]>'7:26',"Tardy 0 Period",
[Time]>'8:11',"Present 1st Period", ...)

Note also that if [Time] is a Date/Time value rather than a String you
should be delimiting it with # rather than with '; and as noted
elsethread, if it contains the date you'll need to either include the
date in your criterion or use the Timevalue() function to extract just
the time portion.
 
John I have a table with this information already. We do use several schedules for different events so I have built those. Could you elaborate on the first suggestion. I am not sure how to set it up. I put the >=Start < End in the criteria but it gave me no results. The second solution seems to complicate things as I would have to change the times when we changed schedules, with the table I can just do it once. Thank you for your time.
 
John I tried your second method. Here is a copy of the thing

Condition: Switch([Time]>'7:00',"Present 0 Period",[Time]>'7:23',"Tardy 0 Period",[Time]>'7:50',"Present 1st Period",[Time]>'8:10',"Tardy 1st Period",[Time]>'8:45',"Present 2nd Period",[Time]>'8:57',"Tardy 2nd Period",[Time]>'9:32',"Present 3rd Period",[Time]>'7:44',"Tardy 3rd Period",[Time]>'10:19',"Present 4th Period",[Time]>'10:41',"Tardy 4th Period",[Time]>'11:16',"Present 5th Period",[Time]>'11:28',"Tardy 5th Period",[Time]>'12:03',"Present 6th Period",[Time]>'12:25',"Tardy 6th Period",[Time]>'13:00',"Present 7th Period",[Time]>'13:12',"Tardy 7th Period",[Time]>'13:47',"Present 8th Period",[Time]>'13:59',"Tardy 8th Period"

It just says it is too complex. Did I miss something? Do I need to sub out the ' for a #?
 
Hi, Andre

Yes, Date and Times need to be delimited with '#' . (BTW, [Time] is a bad choice for a field name because it is a reserved word - Access could get the control named 'Time' confused with the function 'Time()' .

Another way to solve your problem would be to write a User Defined Function (UDF). You could call the UDF in controls, queries, etc. just like the length function or Left() function

Put the UDF in a standard module (see below
To call the function use

=TardyPresent(ScannedTime

if the name of the field in the query is ScannedTime or

=TardyPresent(Me.ControlName

if you have a control on a form

Here is an example of the Switch() or IIF() formulas youwere trying
'-------------------------------------------------------
Option Compare Databas
Option Explici

Function TardyPresent(ScannedTime As Date) As Strin

Dim TimeOnly As Dat

'extract time scanne
TimeOnly = TimeValue(ScannedTime

Select Case TimeOnl
Case Is < #4:30:00 AM
TardyPresent = "Before Classes Start
'0 Perio
Case Is <= #7:00:00 AM
TardyPresent = "Present 0 Period
Case Is < #7:23:00 AM
TardyPresent = "Tardy 0 Period

'1st Perio
Case Is <= #7:50:00 AM
TardyPresent = "Present 1st Period
Case Is < #8:10:00 AM
TardyPresent = "Tardy 1st Period

'2nd Perio
Case Is <= #8:45:00 AM
TardyPresent = "Present 2nd Period
Case Is < #8:57:00 AM
TardyPresent = "Tardy 2nd Period

'3rd Perio
Case Is <= #9:32:00 AM
TardyPresent = "Present 3rd Period
Case Is < #9:44:00 AM
TardyPresent = "Tardy 3rd Period

'4th Perio
Case Is <= #10:19:00 AM
TardyPresent = "Present 4th Period
Case Is < #10:41:00 AM
TardyPresent = "Tardy 4th Period

'5th Perio
Case Is <= #11:16:00 AM
TardyPresent = "Present 5th Period
Case Is < #11:28:00 AM
TardyPresent = "Tardy 5th Period

'6th Perio
Case Is <= #12:03:00 PM
TardyPresent = "Present 6th Period
Case Is < #12:25:00 PM
TardyPresent = "Tardy 6th Period

'7th Perio
Case Is <= #1:13:00 PM
TardyPresent = "Present 7th Period
Case Is < #1:12:00 PM
TardyPresent = "Tardy 7th Period

'8th Perio
Case Is <= #1:47:00 PM
TardyPresent = "Present 8th Period
Case Is < #1:59:00 PM
TardyPresent = "Tardy 8th Period

Case Is <= #3:00:00 PM
TardyPresent = "School Out

End Selec

End Functio
'-------------------------------------------------------

HT
Stev
 
Could you elaborate on the first suggestion. I am not sure how to set it up. I put the >=Start < End in the criteria but it gave me no results.
Click to expand...

Please open this query in SQL view and post the SQL text here. I can't
figure out what you've tried.
 
Back
Top