Date and Time Problems

[Jurrasicway]

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

 

[Allen Browne]

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]

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?

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[John Spencer]

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

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

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?

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]))

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)

 

[Jurrasicway]

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