Date in SQL Query

[geebee]

hi,

I have the following in my code/VBA:

Dim strSQL As String

....rowsource = "SELECT DISTINCT tbl_test.status, count(test.tester) AS
countoftester FROM tbl_test " & strSQL3 & " GROUP BY tbl_test.status ORDER BY
tbl_test.status

I would like to add a date criteria to the above SQL, but I am not quite
sure of the syntax. I think it would be something like the following:

....rowsource = "SELECT DISTINCT tbl_test.status, count(test.tester) AS
countoftester FROM tbl_test " & strSQL3 & " AND [popenterdt] = "#" &
Date()-2 & "#" GROUP BY tbl_test.status ORDER BY tbl_test.status

but I am having no luck so far.

How could I achieve this?

thanks in advance,
-geebee

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Why not just enter the formula in the SQL string, not the result of the
evaluation. E.g.:

.... strSQL3 & " AND [popenterdt] = Date()-2 GROUP BY ....

The Date()-2 will be evaluated during the SQL execution.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBREfwEYechKqOuFEgEQKrkwCfdWainWyeHB4FXXLqzTjz1pPoF5kAn3eb
NkuQg98D9BpC2txoco5JKTdU
=EAGN
-----END PGP SIGNATURE-----

 

[Wayne Morgan]

There appears to be a problem with the quotation marks, they aren't matched
properly around the #'s and the closing quote is missing. Also, if your
system doesn't use the US date format by default, you'll need to format the
date in the US format (mm/dd/yyyy).

....rowsource = "SELECT DISTINCT tbl_test.status, count(test.tester) AS
countoftester FROM tbl_test " & strSQL3 & " AND [popenterdt] = #" &
Date()-2 & "# GROUP BY tbl_test.status ORDER BY tbl_test.status;"

If you need to format the date to US format:

....rowsource = "SELECT DISTINCT tbl_test.status, count(test.tester) AS
countoftester FROM tbl_test " & strSQL3 & " AND [popenterdt] = #" &
Format(Date()-2), "mm/dd/yyyy") & "# GROUP BY tbl_test.status ORDER BY
tbl_test.status;"

Also, for it to work properly, [popenterdt] will need to be defined in the
table as a Date/Time data type.