Date/time comparison problem

[Jon]

Hi,

I am trying to pick up records from a date range without much luck. For my
example below I am trying to pick up all records made in the last month.
However none are being found (my code is tried and tested for all records).
My database is in Access format. Field "datetime1" is of Date/Time data type
getting the default value by using now()

The relevant part of my code is as follows. Can anyone see what I'm doing
wrong?
Many thanks.
Jon.
----------------

Dim dateVar1 'From date
Dim dateVar2 'To date

dateVar1 = dateadd("m",-1,Now()) ' this time last month
dateVar2 = Now() 'now

strSQL = "SELECT * FROM Log WHERE datetime1 >= #" & dateVar1 & "# AND
datetime1 < #" & dateVar2 & "#"

[Aaron Bertrand [MVP]]

Can't you do this in the query? Something like:

sql = "SELECT <always,list,column,names!> FROM Log WHERE datetime1 >=
DATEADD(""m"", -1, NOW()) AND datetime1 < NOW()"

If you really, really, really want to pass the values in from ASP for some
reason, then make sure the dates are in YYYY-MM-DD format, so that
05/09/2004 is not mistaken for 09/05/2004, for example.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"Jon" <Jo*@nospam.com> wrote in message
news:d3***************@newsfe1-gui.server.ntli.net...

Hi,

I am trying to pick up records from a date range without much luck. For
my
example below I am trying to pick up all records made in the last month.
However none are being found (my code is tried and tested for all
records).
My database is in Access format. Field "datetime1" is of Date/Time data
type
getting the default value by using now()

The relevant part of my code is as follows. Can anyone see what I'm doing
wrong?
Many thanks.
Jon.
----------------

Dim dateVar1 'From date
Dim dateVar2 'To date

dateVar1 = dateadd("m",-1,Now()) ' this time last month
dateVar2 = Now() 'now

strSQL = "SELECT * FROM Log WHERE datetime1 >= #" & dateVar1 & "# AND
datetime1 < #" & dateVar2 & "#"

[Bob Barrows [MVP]]

Jon wrote:

Hi,

I am trying to pick up records from a date range without much luck.
For my example below I am trying to pick up all records made in the
last month. However none are being found (my code is tried and tested
for all records). My database is in Access format. Field "datetime1"
is of Date/Time data type getting the default value by using now()

The relevant part of my code is as follows. Can anyone see what I'm
doing wrong?
Many thanks.
Jon.
----------------

Dim dateVar1 'From date
Dim dateVar2 'To date

dateVar1 = dateadd("m",-1,Now()) ' this time last month
dateVar2 = Now() 'now

strSQL = "SELECT * FROM Log WHERE datetime1 >= #" & dateVar1 & "# AND
datetime1 < #" & dateVar2 & "#"

Jet queries can use some VBA functions, including Now(), Date() and DateAdd.
You do not need the "<" part of this statement. Change your strSQL
definition to:

strSQL = "... datetime1 >= DateAdd('m',-1,Date())"

I also suggest you eschew the use of selstar (Select *) in production code.
You are impairing performance because ADO has to make an extra trip to the
database to resolve the * into actual column names when you use selstar.

The first thing you should do when you have a problem with a query built via
dynamic sql is to use "Response.Write strSQL" to see the actual query being
sent to the database. If you have built the statement correctly, you should
be able to copy it from the browser window into the SQL View of the Access
Query Builder and run it without modification (the exception is if your
query uses LIKE with wildcards - you must use the ODBC wildcards (%, _)
instead of the Jet wildcards (*, ?) when using ADO to execute a dynamic sql
statement.

You are much better off using saved queries instead of dynamic sql. Search
for posts by me that contain "saved parameter query" for more details.

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"