Help on Between Dates using Fields on Form

[Abdul Wahab]

Please help me to take out records between 2 given dates
which is BeginningDate and EndingDate on form. I'm
writing SQL as follows :

Dim strSQL As String
strSQL = "SELECT * FROM Invoices WHERE [Date] Between
#" & [BeginningDate] & " # AND # " & [EndingDate] & "#"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
With rst
Do Until rst.EOF
.Edit
rst![InvPosted] = 1
.Update
.MoveNext
Loop
End With
rst.Close
Set db = Nothing
End Sub

This is not selecting correct dates. Please help me.

 

[Allen Browne]

Literal dates in a SQL statement must be in American format. Try this:

Const conJetDate = "\#mm\/dd\/yyyy\#"
strSQL = "SELECT * FROM Invoices WHERE Invoices.Date Between " & _
Format([BeginningDate], conJetDate) & " AND " & _
Format([EndingDate] , conJetDate & ";"

Note also that Date is a reserved word in VBA (for the system date).
While you can get around the problem in this context (by specifying that you
mean the Date in the Invoices table), it would be a really good idea to
rename the field to InvoiceDate or similar. It will bite you sooner or
later.

If BeginningDate and EndingDate are unbound text boxes, it also helps to set
their Format property to Short Date or similar, so that Access knows to
interpret their contents as dates.

More information:
International Dates in Access
at:
http://allenbrowne.com/ser-36.html

 

[mo]

I think the problem might be that Access uses US date format, so you'll need
to format your dates so that they read like US format.

I usually use something like:

#" & Month([yourdatefield]) & "/" & Day([yourdatefield]) & "/" &
Year([yourdatefield]) & "#

Also, you appear to have a field called 'Date' in your table 'Invoices'.
'Date' I think is one of those Access reserved words and might cause you
some trouble in the future.

Failing either of these, perhaps you could try using '>=' and '<=' instead
of 'Between' and 'And'.

HTH

P.S. Not really wise to use 'SELECT *...'