SQL query

25 Feb 2009 8:36 AM

Goerge Tikakis

Hello
I have a database with 2 date fields in which i'm storing the current
date and current time of a transaction.

I want to find transactions between specific dates and times for example
to give me all the transactions between 2/23/2009 22:00 and 2/24/2009
4:00

In every way that i tested it gives me wrong results

The database is Access 2000

Thank you

*** Sent via Developersdex http://www.developersdex.com ***

25 Feb 2009 12:40 PM

Norm Cook

Show quote Hide quote

"Goerge Tikakis" <nospam@devdex.com> wrote in message
news:ukaJ1QylJHA.3380@TK2MSFTNGP04.phx.gbl...
>
>
> Hello
> I have a database with 2 date fields in which i'm storing the current
> date and current time of a transaction.
>
> I want to find transactions between specific dates and times for example
> to give me all the transactions between 2/23/2009 22:00 and 2/24/2009
> 4:00
>
> In every way that i tested it gives me wrong results
>
> The database is Access 2000
>
> Thank you
>
> *** Sent via Developersdex http://www.developersdex.com ***

Would have been helpful to post the SQL you are using, but
this works for my DB:

"Select * From YourTable Where YourField
BETWEEN #2/23/2006 22:00:00# AND #2/24/2009 4:00:00#"

25 Feb 2009 5:21 PM

Ralph

Show quote Hide quote

"Goerge Tikakis" <nospam@devdex.com> wrote in message
news:ukaJ1QylJHA.3380@TK2MSFTNGP04.phx.gbl...
>
>
> Hello
> I have a database with 2 date fields in which i'm storing the current
> date and current time of a transaction.
>
> I want to find transactions between specific dates and times for example
> to give me all the transactions between 2/23/2009 22:00 and 2/24/2009
> 4:00
>
> In every way that i tested it gives me wrong results
>
> The database is Access 2000
>
> Thank you
>
> *** Sent via Developersdex http://www.developersdex.com ***

25 Feb 2009 5:56 PM

Kostas kousinovalis

"SELECT CDate([DateField] " & Chr(38) & Chr(34) & " " & Chr(34) &
Chr(38) & " [TimeField]) AS Expression1 FROM YourTable where
((CDate([DateField] " & Chr(38) & Chr(34) & " " & Chr(34) & Chr(38) & "
[TimeField]))>=#2/23/2009 22:00# And (CDate([DateField] " & Chr(38) &
Chr(34) & " " & Chr(34) & Chr(38) & " [TimeField]))<=#2/24/2009
04:00#))"

*** Sent via Developersdex http://www.developersdex.com ***

26 Feb 2009 2:45 PM

Jeff Johnson

"Goerge Tikakis" <nospam@devdex.com> wrote in message
news:ukaJ1QylJHA.3380@TK2MSFTNGP04.phx.gbl...

> I have a database with 2 date fields in which i'm storing the current
> date and current time of a transaction.

It seems your main problem was this silly design decision to store date and
time separately. See how much trouble it's causing you in queries? Splitting
them apart later in code (if you need to) is far easier than putting them
together to run queries.