How to query time on access

[Guest]

I can't find the roght way to query time as in greater than or less than. For
example what stores that are open after a certine time.

 

[Van T. Dinh]

If you want to use explicit time (and date) in your query, you need to use
the hash (#) delimiters to indicate that it is a date/time value. For
example:

.... WHERE [OpenTime] >= #09:00:00#

will find stores that open on or after 9:00 am.

Post much more details if you need further help.

 

[Guest]

I am clueless about Access but I'm trying to figure it out. This is exactly
what I am trying to do in a query. Eventually I need to sum entries in an
Amount Paid field but I need seperate totals for entries made before noon and
those made after noon. I am just trying to select records using the following
criteria

#12:00:00 PM#

but this is not working. My field is Time from a table that already exists.
Should I not be using the criteria entry for this? Or am I missing something?
This should be easy I know but I'm going crazy! Any help would be greatly
appreciated.

If you want to use explicit time (and date) in your query, you need to use
the hash (#) delimiters to indicate that it is a date/time value. For
example:

.... WHERE [OpenTime] >= #09:00:00#

will find stores that open on or after 9:00 am.

Post much more details if you need further help.

--
HTH
Van T. Dinh
MVP (Access)

I can't find the roght way to query time as in greater than or less than. For
example what stores that are open after a certine time.

 

[Dale Fye]

Smilee,

Dates and times are stored as decimal values with the integer portion being
the number of days Dec 30, 1899 (or something like that), and the decimal
value representing the the percentage of the day that has gone by (Midnight
= .0, 4AM = .16667, Noon = .5, etc)

Depending on how you are populating your Time value, it is probably storing
a date and a time value. To get the time portion, you can use
TimeValue([yourTimeField]), but this will return the value formatted as a
time HH:mm:ss AM/PM. To get the decimal value of the field in your time
field, you need to convert this timevalue to decimal:
cdbl(timeValue([YourTimeField]).

However, if you want to divide the day into halves (AM, and PM), the easiest
way is to use the format command: Format([yourTimeField], "AM/PM"). This
will return a text value of AM or PM, which you can use to group your values
in the AmountPaid field. Something like:

SELECT DateValue([yourDateField]) as PaidDate,
Format([yourTimeField], "AM/PM") as TimeSegment,
Sum([AmountPaid]) as SumPaid
FROM yourTable
GROUP BY DateValue([yourDateField]),
Format([yourTimeField], "AM/PM")

HTH
Dale

I am clueless about Access but I'm trying to figure it out. This is exactly
what I am trying to do in a query. Eventually I need to sum entries in an
Amount Paid field but I need seperate totals for entries made before noon
and
those made after noon. I am just trying to select records using the
following
criteria

#12:00:00 PM#

but this is not working. My field is Time from a table that already
exists.
Should I not be using the criteria entry for this? Or am I missing
something?
This should be easy I know but I'm going crazy! Any help would be greatly
appreciated.

If you want to use explicit time (and date) in your query, you need to
use
the hash (#) delimiters to indicate that it is a date/time value. For
example:

.... WHERE [OpenTime] >= #09:00:00#

will find stores that open on or after 9:00 am.

Post much more details if you need further help.

--
HTH
Van T. Dinh
MVP (Access)

I can't find the roght way to query time as in greater than or less
than. For
example what stores that are open after a certine time.