proper day format

[ttuser4]

i have this query in vb6 program:
sSQL = "SELECT Count([WDATE]) From WHOURS Where [EMPID]=" & EMPID & " AND (([WDATE] Between (" & STD & "-31) And (" & STD & "-1)));"

debug.print sSQL looks like:
SELECT Count([WDATE]) From WHOURS Where [EMPID]=154 AND (([WDATE] Between (7/01/12-31) And (7/01/12-1)));

it doesn't give me the right number, it gives me zero every time. i suspect this criteria format '(7/01/12-31)' is causing the problem.

for example, this query in ms access gives me the right count
SELECT Count([WDATE]) AS Expr1
FROM WHOURS
WHERE (((WHOURS.EMPID)=154) AND ((WHOURS.WDATE) Between (NOW()-31) And (NOW()-1)));

can you please help me to get proper format for vb6?

 

[dhookom]

The expression: 7/01/12-31
7 divided by 1 divided by 12 minus 31.

Have you attempted to create date values prior to the sSQL? Also, dates are typically delimited with # depending on the database engine.

Duane
Hook'D on Access
MS Access MVP

 

[ttuser4]

excellent, thanks.

this is the proper format:
sSQL = "SELECT Count([WDATE]) From WHOURS Where [EMPID]=" & EMPID & " AND [WDATE] Between #" & DM31 & "# And #" & DM1 & "#;