Date Between

[JohannIcon]

Dear All,

I am trying to search for all the records that are between today's date and exactly last year, ie between 8/25/2003 and 8/25/2002. I have this sql which is not working:-

curDate = date()
diffDate = (DateAdd("yyyy",-1,date()))

sqlReading = "SELECT * from xermeter WHERE (machine_ref = '" & RefCode & "' AND date_col BETWEEN '" & diffDate & "' AND '" & curdate & "' ORDER BY Date_Col DESC"

The error is the following:-
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
Error In Expression. = 'DB0030' AND date_col BETWEEN ( '8/25/2002' , '8/25/2003' ) << Syntax Error >> ORDER BY Date_Col DESC


What I am doing wrong?

Thanks for your help and time

 

[shaddow]

Should be
date_col BETWEEN '8/25/2002' AND '8/25/2003'

________
George, M

 

[JohannIcon]

yeah I tried that:-
SELECT * from xermeter WHERE machine_ref = 'DB0030' AND date_col (BETWEEN '8/25/2002' AND '8/25/2003') ORDER BY Date_Col DESC

but it is not retreiving anything when I am sure that there is data in the table

 

[onpnt]

OK, I'm lost
this: BETWEEN '&quot; & diffDate & &quot;' AND '&quot; & curdate & &quot;'
does not equal this: BETWEEN ( '8/25/2002' , '8/25/2003' )

what am I missing or did you paste the wrong SQL statement for us to look at?



_____________________________________________________________________
_{You can accomplish anything in life, provided that you do not mind who gets credit.
Harry S. Truman}

http://www.onpntwebdesigns.com

 

[JohannIcon]

Oh sorry i did some amendments, you are right.

sqlReading = &quot;SELECT * from xermeter WHERE machine_ref = '&quot; & RefCode & &quot;' AND date_col (BETWEEN '&quot; & diffdate & &quot;' AND '&quot; & curdate & &quot;') ORDER BY Date_Col DESC&quot;

sorry about that

 

[onpnt]

no problem, just wanted to make sure the coffee wasn't funny this morning.

_____________________________________________________________________
_{You can accomplish anything in life, provided that you do not mind who gets credit.
Harry S. Truman}

http://www.onpntwebdesigns.com

 

[JohannIcon]

still it isn't working

 

[onpnt]

faq333-3048
reference 8 should show some light
eg: # #

provided I'm correct in this being access

_____________________________________________________________________
_{You can accomplish anything in life, provided that you do not mind who gets credit.
Harry S. Truman}

http://www.onpntwebdesigns.com

 

[JohannIcon]

I tried what you suggested:-

sqlReading = &quot;SELECT * from xermeter WHERE machine_ref = '&quot; & RefCode & &quot;' AND date_col (BETWEEN #&quot; & diffDate & &quot;# AND #&quot; & curDate & &quot;#) ORDER BY Date_Col DESC&quot;

but there is still this error:-

Column Referenced not found in table list. (#8) from xermeter WHERE machine_ref = 'AF0234' AND date_col ( BETWEEN << Syntax Error >> #8 / 25 / 2002# AND #8 / 25 / 2003#

Actually it is a dataflex database, but I have a view in Access, however, I am retreiving from Dataflex.

 

[clarkin]

wow, 'dataflex' - can't say i've ever heard of it.

In general SQL your BETWEEN clause shouldn't be bracketed as you have done. it should be:
[tt]... AND date_col BETWEEN someDateValue AND anotherDateValue ORDER BY ...[/tt]
So, basically, eliminate the brackets: you don't need them.

Secondly, you need to delimit date literals (as in, not a variable or a column) with the correct date-delimiter. In SQL Server its a ', in Access its a #. Better dust off that dataflex manual!

An, um, thirdly. I was going to suggest you not use date literals at all. Currently you create the two dates in VBscript, append them in your SQL string, and send it to the database. It will be less error-prone to create the two dates directly in your database. If you were using SQL server you could use the function GETDATE().
eg: (SQL Server)

sqlReading = "SELECT * from xermeter WHERE machine_ref = '" & RefCode & "' AND date_col BETWEEN GETDATE() AND DATEADD(yyyy,-1,GETDATE()) ORDER BY Date_Col DESC"

Obviously, for your database you'll have to find the equivalent of the GETDATE() and DATEADD() functions.

Good luck

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[JohannIcon]

Ok I found the solution. Since it is a Dataflex database that I am querying, I had to use the standard SQL date method, ie YYYY-MM-DD. Now it is working fine. Thanks for all your help!