setting criteria for time values in select queries

[RobV]

I am using Access 2000 and 2003 (different locations). I have a need to be
able to look at records entered in various time ranges eg. AM. AFT or PM.
The underlying table has a time field with default set at Time(). On the
data entry form the Time field shows the system time at time of entry. So
does the select query I created to extract the data, until I input the
criteria - Between #12:00:00PM# And #5:00:00PM#, when I run the query with
this criteria I get no data at all, just the column headings. This format
works well for seperating data for given days, confused as to why it will not
work for Time. Any suggestions?

 

[Marshall Barton]

I am using Access 2000 and 2003 (different locations). I have a need to be
able to look at records entered in various time ranges eg. AM. AFT or PM.
The underlying table has a time field with default set at Time(). On the
data entry form the Time field shows the system time at time of entry. So
does the select query I created to extract the data, until I input the
criteria - Between #12:00:00PM# And #5:00:00PM#, when I run the query with
this criteria I get no data at all, just the column headings. This format
works well for seperating data for given days, confused as to why it will not
work for Time.

That should work if your time field is trally set using the
Time() function. However, if you set the field's value
using the Now() function, then the field contains a date
part and will not match your time only criteria. You can
test this by making sure the table field has a blank format
property and then viewing the table in sheet view.

If that is your problem, you can apply your criteria to a
calculated field in your query. The calculated field would
be like:
TOD: TimeValue([your datetime field])

 

[Dale Fye]

Rob,

If you have the format property of the field set to "Short Time" or "Long
Time", it will appear that the data is only storing time values, but it may
be storing date and time. To check this, you can remove the format property
of the field in table design view then view the table, or you could create a
simple query and wrap the field name in the cdbl( ) conversion function,
which will display the date as a double precision number. If there is
anything to the left of the decimal point other than a zero, you know you are
storing date values as well as time.

Additionally, if Marshall is correct (your data field is actually filled
using the Now() function), then using BETWEEN #12/1/2007# and #12/31/2007#
will not get you results for all of December, but only those values where
your date/time field values were entered before midnight on #12/30/2007#; you
will loose the results for all of #12/31/2007#. If this is the case, then
you should do your date comparisons using the DateValue( ) function.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I am using Access 2000 and 2003 (different locations). I have a need to be
able to look at records entered in various time ranges eg. AM. AFT or PM.
The underlying table has a time field with default set at Time(). On the
data entry form the Time field shows the system time at time of entry. So
does the select query I created to extract the data, until I input the
criteria - Between #12:00:00PM# And #5:00:00PM#, when I run the query with
this criteria I get no data at all, just the column headings. This format
works well for seperating data for given days, confused as to why it will not
work for Time.

That should work if your time field is trally set using the
Time() function. However, if you set the field's value
using the Now() function, then the field contains a date
part and will not match your time only criteria. You can
test this by making sure the table field has a blank format
property and then viewing the table in sheet view.

If that is your problem, you can apply your criteria to a
calculated field in your query. The calculated field would
be like:
TOD: TimeValue([your datetime field])

 

[RobV]

Thank you very much it now works just as I had hoped. The problem was me, I
had originally set the field in the table to Now() and had run a few entries
to check, and got the blank wall. Then I tried changing the field to Time()
but did not update the data already entered hence the blank wall. I have now
updated all the entries I made in my test plus added more and it works just
like it should. You know what they say "a little information can be
dangerous!" Thanks for your help

I am using Access 2000 and 2003 (different locations). I have a need to be
able to look at records entered in various time ranges eg. AM. AFT or PM.
The underlying table has a time field with default set at Time(). On the
data entry form the Time field shows the system time at time of entry. So
does the select query I created to extract the data, until I input the
criteria - Between #12:00:00PM# And #5:00:00PM#, when I run the query with
this criteria I get no data at all, just the column headings. This format
works well for seperating data for given days, confused as to why it will not
work for Time.

That should work if your time field is trally set using the
Time() function. However, if you set the field's value
using the Now() function, then the field contains a date
part and will not match your time only criteria. You can
test this by making sure the table field has a blank format
property and then viewing the table in sheet view.

If that is your problem, you can apply your criteria to a
calculated field in your query. The calculated field would
be like:
TOD: TimeValue([your datetime field])

 

[RobV]

Thank for you input as well. I wanted to rate both your response and
Marshall but I see nowhere on my screen to do this. There are no buttons
anywhere that I ca n see that would allow me to rate the response. But they
were both top marks. Thank you both.

Rob,

If you have the format property of the field set to "Short Time" or "Long
Time", it will appear that the data is only storing time values, but it may
be storing date and time. To check this, you can remove the format property
of the field in table design view then view the table, or you could create a
simple query and wrap the field name in the cdbl( ) conversion function,
which will display the date as a double precision number. If there is
anything to the left of the decimal point other than a zero, you know you are
storing date values as well as time.

Additionally, if Marshall is correct (your data field is actually filled
using the Now() function), then using BETWEEN #12/1/2007# and #12/31/2007#
will not get you results for all of December, but only those values where
your date/time field values were entered before midnight on #12/30/2007#; you
will loose the results for all of #12/31/2007#. If this is the case, then
you should do your date comparisons using the DateValue( ) function.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I am using Access 2000 and 2003 (different locations). I have a need to be
able to look at records entered in various time ranges eg. AM. AFT or PM.
The underlying table has a time field with default set at Time(). On the
data entry form the Time field shows the system time at time of entry. So
does the select query I created to extract the data, until I input the
criteria - Between #12:00:00PM# And #5:00:00PM#, when I run the query with
this criteria I get no data at all, just the column headings. This format
works well for seperating data for given days, confused as to why it will not
work for Time.

That should work if your time field is trally set using the
Time() function. However, if you set the field's value
using the Now() function, then the field contains a date
part and will not match your time only criteria. You can
test this by making sure the table field has a blank format
property and then viewing the table in sheet view.

If that is your problem, you can apply your criteria to a
calculated field in your query. The calculated field would
be like:
TOD: TimeValue([your datetime field])