Date filter only works on certain dates?!?

[TrevorThielen]

I have one form with two date pickers and some buttons to open certain reports. It seems that if one of the dates is set to the 1st through 7th and 9th through 12th of a month, the filter just doesn't work at all, but any other dates and it works as it should. Has anyone else ever had this problem? Its in several spots of the same database. I have another form that just searches through the records on the form with an unbound date picker in the header and it does the same thing.

Thanks in advance for any help!

Heres some of the code I have, the rest is pretty similar:

Private Sub Command30_Click()


Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim txtFirstDate As Date
Dim txtLastDate As Date
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
Set db = CurrentDb
Set rs1 = db.OpenRecordset("Training")
Set rs2 = db.OpenRecordset("TrainingCodes")

If IsNull(Me.FirstDate) Or IsNull(Me.LastDate) Then
MsgBox "Please Pick Both Dates!"
Cancel = True
Else
If Me.LastDate > Me.FirstDate Then
txtFirstDate = Me.FirstDate
txtLastDate = Me.LastDate
Else
txtFirstDate = Me.LastDate
txtLastDate = Me.FirstDate
End If
strWhere = "CourseDate >= #" & txtFirstDate & "# And CourseDate <= #" & txtLastDate & "#"
DoCmd.OpenReport "TrainingOverview", acViewReport, , strWhere, acWindowNormal, OpenArgs


End If
Debug.Print Me.FirstDate
Debug.Print Me.LastDate
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
db.Close
End Sub

[pbaldy]

I suspect you forgot to use this:

Const conDateFormat = "\#mm\/dd\/yyyy\#"

[TrevorThielen]

I was getting a Type Mismatch error when I tried it. So I took out the code. I'll revisit it now and see if I can't get it going.


Thanks

[pbaldy]

I suspect you're running into this:

http://allenbrowne.com/ser-36.html#Format

http://access.mvps.org/access/datetime/date0005.htm

[TrevorThielen]

Got 'er figured! Thanks for your help!

Lessons learned:
1. When using Format(), change your variables to String type - hence the type mismatch errors!
2. When using String type variables, change your WHERE clauses accordingly - then the filters actually work!

[pbaldy]

No problemo!