Re: Sum Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

=?Utf-8?B?UGF1bCBNZW5kbGVzb2hu?=
<PaulMendlesohn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in news:A3686B39-2E91-
4F03-8DCD-E58B5FD4A94C@xxxxxxxxxxxxx:

> " WHERE ((tblReceipts.DateTime)> #" & varStartDate & "# And
> (tblReceipts.DateTime)< #" & varEndDate & "#);"
>

You don't say where you are posting from, but this is not a safe way to
pass dates in the USA and not a legal way anywhere else. You really need
to specify a Jet-compatible date format explicitly:

Const sJetDate As String = "\#yyyy\-mm\-dd\#"


jetSQL = "SELECT etc etc " & _
" WHERE tblReceipts.DateTime >" & Format(varStartDate, sJetDate) & _
" AND tblReceipts.DateTime <" & Format(varEndDate, sJetDate)

This line also really really helps:

Debug.Assert vbYes=MsgBox(jetSQL,"vbYesNo","Is This Okay?")

I have a sneaking suspicion that your inequality operators are wrong. You
are excluding receipts that occur on the start date and end date, where
people are generally used to specifying starts and ends inclusively.
Eyeballing the final SQL statement makes it easy to pick up errors like
this.

And finally, I don't really see the point of creating the temporary
querydef: you can just open the recordset directly.

ss = db.OpenRecordset(jetSQL, dbOpenSnapshot, dbForwardOnly)


Hope that helps


Tim F

.

Relevant Pages