BETWEEN dates not work (the return:-))

[sal21]

Code: Select all

    SQL = "SELECT COUNT (COD_SPORT_RAD) AS TOT_CC FROM [CC_DAILY] WHERE [TIPO_DATO]='" & TEST_TIPO & "' AND [DATA_ACC] BETWEEN #" & Format(FIRST_DAY, "MM/DD/YYYY") & "# AND #" & Format(LAST_DAY, "MM/DD/YYYY") & "#"

here immediate windows result (debug.print sql)

SELECT COUNT (COD_SPORT_RAD) AS TOT_CC FROM [CC_DAILY] WHERE [TIPO_DATO]='00' AND [DATA_ACC] BETWEEN #01/30/2011# AND #02/02/2011#

... and is necessary to use >= and =< between the two dates? if yes how to?

[HansV]

There shouldn't be a space between COUNT and (COD_SPORT_RAD) but I don't think that causes the error.

Are you sure that DATA_ACC is a date field?

If so, delete the line and type it in again (do NOT copy/paste it).

[sal21]

There shouldn't be a space between COUNT and (COD_SPORT_RAD) but I don't think that causes the error.

Are you sure that DATA_ACC is a date field?

If so, delete the line and type it in again (do NOT copy/paste it).

HUMMMMMM!!!!

in another query i use directlly DATA_ACC >='01/01/2005' and work without error.
Peraphs the double quote not is necesary? if yes how to modify the query in error?
With a chr definition arrounf date value?

Infact with:
SQL = "SELECT COUNT (COD_SPORT_RAD) AS TOT_CC FROM [CC_DAILY] WHERE [TIPO_DATO]='00' AND [DATA_ACC] BETWEEN '01/30/2011' AND '02/02/2011'"

the code work fine!

[HansV]

For Access, you need # but for SQL Server you need ' because SQL Server stores dates as strings.

[sal21]

For Access, you need # but for SQL Server you need ' because SQL Server stores dates as strings.

infact (sorry me) the database are in sql server 2005!