Date and Time Problems

Can anyone please help with the following:

I have a database of transations that are time stamped. The time stamp is as
follows:

24/05/2008 23:22:47

What do I need to put into my querey box to pick up transactons between two
times on every day. If it was just the time or the date it would be easy.
However I need all days to be measured and only transactions between two
times to be shown
EG
All transactions between 23:00 and 01:00 (Between 23:00 and 01:00)
Thanks

So you have these in a Date/Time field named (say) TranslateDateTime?

In query design view, enter this expression into the Field row:
TheTime: IIf([TranslateDateTime] Is Null, Null,
TimeValue([TranslateDateTime]))

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jurrasicway" wrote in message
...
Can anyone please help with the following:

I have a database of transations that are time stamped. The time stamp is
as
follows:

24/05/2008 23:22:47

What do I need to put into my querey box to pick up transactons between
two
times on every day. If it was just the time or the date it would be easy.
However I need all days to be measured and only transactions between two
times to be shown
EG
All transactions between 23:00 and 01:00 (Between 23:00 and 01:00)
Thanks

Hi Allen,

When I entered
TheTime: IIf([TransactionDate] Is Null,Null,TimeValue([TransactionDate]))
I got an invalid operator error
I then entered
IIf([TransactionDate] Is Null,Null,TimeValue([TransactionDate]))
but no records were shown. I take it that TheTime will prompt an entry box
for me to input the time that I need transactions for?

"Allen Browne" wrote:

So you have these in a Date/Time field named (say) TranslateDateTime?

In query design view, enter this expression into the Field row:
TheTime: IIf([TranslateDateTime] Is Null, Null,
TimeValue([TranslateDateTime]))

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jurrasicway" wrote in message
...
Can anyone please help with the following:

I have a database of transations that are time stamped. The time stamp is
as
follows:

24/05/2008 23:22:47

What do I need to put into my querey box to pick up transactons between
two
times on every day. If it was just the time or the date it would be easy.
However I need all days to be measured and only transactions between two
times to be shown
EG
All transactions between 23:00 and 01:00 (Between 23:00 and 01:00)
Thanks

Where TimeValue(TransactionTime) = #23:00:00#
Or TimeValue(TransactionTime) = #01:00:00#

If your time period did not overlap days then
WHERE TimeValue(TransactionTime) Between #13:00:00# And #14:30:00#

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Jurrasicway wrote:
Can anyone please help with the following:

I have a database of transations that are time stamped. The time stamp is as
follows:

24/05/2008 23:22:47

What do I need to put into my querey box to pick up transactons between two
times on every day. If it was just the time or the date it would be easy.
However I need all days to be measured and only transactions between two
times to be shown
EG
All transactions between 23:00 and 01:00 (Between 23:00 and 01:00)
Thanks

TheTime was just an alias for the field. If you don't do this, Access
provides Expr1 (which is fine, but not meaningful.)

Try just this in the Field row:
TimeValue([TransactionDate])
with this in the Criteria row:
Is Not Null

The reason for the criteria is that TimeValue() errors on Null. Once you
have that working, you can expand it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jurrasicway" wrote in message
...
Hi Allen,

When I entered
TheTime: IIf([TransactionDate] Is Null,Null,TimeValue([TransactionDate]))
I got an invalid operator error
I then entered
IIf([TransactionDate] Is Null,Null,TimeValue([TransactionDate]))
but no records were shown. I take it that TheTime will prompt an entry box
for me to input the time that I need transactions for?

"Allen Browne" wrote:

So you have these in a Date/Time field named (say) TranslateDateTime?

In query design view, enter this expression into the Field row:
TheTime: IIf([TranslateDateTime] Is Null, Null,
TimeValue([TranslateDateTime]))

"Jurrasicway" wrote in message
...
Can anyone please help with the following:

I have a database of transations that are time stamped. The time stamp
is
as
follows:

24/05/2008 23:22:47

What do I need to put into my querey box to pick up transactons between
two
times on every day. If it was just the time or the date it would be
easy.
However I need all days to be measured and only transactions between
two
times to be shown
EG
All transactions between 23:00 and 01:00 (Between 23:00 and 01:00)

John, Thanks for this
I got it by doing the following

Field TransationDate
Criteria TimeValue([TransactionDate])=#23:00:00#
Or TimeValue([TransactionDate])=#01:00:00#

Many Many thanks

"John Spencer" wrote:

Where TimeValue(TransactionTime) = #23:00:00#
Or TimeValue(TransactionTime) = #01:00:00#

If your time period did not overlap days then
WHERE TimeValue(TransactionTime) Between #13:00:00# And #14:30:00#

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Jurrasicway wrote:
Can anyone please help with the following:

I have a database of transations that are time stamped. The time stamp is as
follows:

24/05/2008 23:22:47

What do I need to put into my querey box to pick up transactons between two
times on every day. If it was just the time or the date it would be easy.
However I need all days to be measured and only transactions between two
times to be shown
EG
All transactions between 23:00 and 01:00 (Between 23:00 and 01:00)
Thanks