vb SQL date issue

[Guest]

Hello all,

I seem to be having a little problem with a date/time field while running my
SQL in VB. The SQL works if I remove the WHERE clause which is a date. If I
manually run it in a query it works file, but when I run it in SQL and pass
the dates in a varible it errors.

Select case opSelect

Case 1 'Day Shift

vSDate = cal1 & " " & "6:00:00 AM"
vEDate = cal1 & " " & "17:59:59 PM"
ect…..

vSQl = "INSERT INTO tbResults ( Count, Area )SELECT
Count(VIAWARE_AFRM_TO_VIA_T.CONT) AS CountOfCONT, ""A-Frame"" AS Expr1 FROM
VIAWARE_AFRM_TO_VIA_T WHERE dtimecre Between #" & vSDate & "# and #" & vEDate
& "#""GROUP BY ""A-Frame"""

DoCmd.RunSQL (vSQl)

Date looks like this in table: 03/22/2006 7:28:18 AM

Here is what my SQL string reads:

INSERT INTO tbResults ( Count, Area ) SELECT DISTINCT Count(CONT) AS
CountOfCONT, "Carousel" AS Expr1 FROM viaware_CAR_TO_VIA_T WHERE Between
#04/11/2006 6:00:00 AM# and #04/11/2006 17:59:59 PM#"

Do you see anything wrong with the string?

Here is the query SQL that can run:

INSERT INTO tbResults ( Count, Area )
SELECT Count(CONT) AS CountOfCONT, "Carousel" AS epr1 FROM
VIAWARE_CAR_TO_VIA_T
WHERE (((DTIMEMOD) Between #4/11/2006 6:00:00 AM# And #4/11/2006 5:00:00 PM#))
GROUP BY "Carousel";

 

[Douglas J. Steele]

You're missing the name of the field you want to compare to the dates in the
WHERE clause.

 

[Tim Ferguson]

Here is what my SQL string reads:

INSERT INTO tbResults ( Count, Area ) SELECT DISTINCT Count(CONT) AS
CountOfCONT, "Carousel" AS Expr1 FROM viaware_CAR_TO_VIA_T WHERE
Between #04/11/2006 6:00:00 AM# and #04/11/2006 17:59:59 PM#"

Did you know that SQL is meant to be human-readable as well as
writeable? Try this:

INSERT INTO tbResults ( Count, Area )

SELECT DISTINCT
Count(CONT) AS CountOfCONT,
"Carousel" AS Expr1

FROM viaware_CAR_TO_VIA_T

WHERE BETWEEN #04/11/2006 6:00:00 AM#
AND #04/11/2006 17:59:59 PM#

GROUP BY "Carousel";

and the answer becomes obvious straightaway...


By the way, although I don't _think_ it's doing any harm, the GROUP BY
clause is meaningless. You don't have a field name <"Carousel">, nor
even one called <Carousel>. I am slightly surprised that this does not
cause an error; it's certainly not doing anything and I would leave it
out if I were you.

Hope that helps


Tim F