Use of DateSelect in SQL filter

  • Thread starter Thread starter Ian Bayly
  • Start date Start date
I

Ian Bayly

I keep a hierachy of backups of a project.
My most recent copy fails at line 300 in the code below, with the message
"Enter Parameter" with parameter "value "DateSelect":
No changes have been made to this form and prior copies still run this code
OK.

Any ideas what I could have done. Unfortunately I am 6 hours into changes
which I don't want to redo!

All help appreciated

Ian B

210: Select Case obSelection
Case 1
230: sFilter = "EntrySourceCode = '" & Me.comboType & "'"
240: Case 2
250: sFilter = "RefNum = '" & Me.txID & "'"
260: Case 3
270: sFilter = "EntrySourceCode = '" & Me.comboType & "' AND
RefNum = '" & Me.txID & "'"
280: End Select
290: sFilter = sFilter & " AND (DateSelect BETWEEN #" &
Format(Me.txCalcFrom, "Medium Date") & "# AND #" & Format(Me.txCalcTo,
"Medium Date") & "#)"
300: DoCmd.OpenForm "frmLogView", acFormDS, , sFilter, acFormReadOnly
 
Not to answer your question, but:- this kind of code is asking for trouble:

... BETWEEN #" & Format(Me.txCalcFrom, "Medium Date") & "# AND #" ...

Dates supplied in the #...# format must be in month/day/year order,
regardless of your regional settings<. So you should really change "Medium
Click to expand...
Date" to "mm/dd/yyyy" in the code above. Some people say to use
"mm\/dd\/yyyy", which is even safer.

HTH,
TC
 
I agree with TC about the date format.

In line 290:, you refer to the Field [DateSelect]. Is [DateSelect] a Field
included in the RecordSource of the Form "frmLogView"?
 
Thanks for the advice.

290: sFilter = sFilter & " AND (([tbllogdata].[CreateTStamp]) Between
Format(#" & Me.txCalcFrom & "#,'mm/dd/yyyy') And Format(#" & Me.txCalcTo &
"#,'mm/dd/yyyy'))"

Works fine

Ian B
 
Back
Top