Date Picker Format in code

[gsandy]

I have a query in form which has two date-pickers, a start date (txtDateStart) and finish date (txtDateFin), both being in UK format (dd/mm/yyy). The query runs OK but shows no results. The code is”

  If Not IsNull(Me.txtDateStart.Value) Then
             strSQL = strSQL & " AND tblHourEnter.Week_Ending>=" & Me.txtDateStart.Value & " AND tblHourEnter.Week_Ending<=" & Me.txtDateFin.Value & " "
  End If

If I remove the last AND portion the query runs OK but all results show, not those restricted to the selected date. The code is:

  If Not IsNull(Me.txtDateStart.Value) Then
              strSQL = strSQL & " AND tblHourEnter.Week_Ending=" & Me.txtDateStart.Value & " " 
  End If

How can I get Access to recognise the date (if that is the problem). Thank Sandy

 

[JHB]

Date shall be in US format when you use it in a query:

If Not IsNull(Me.txtDateStart.Value) Then
strSQL = strSQL & " AND tblHourEnter.Week_Ending>=" & Format(Me.txtDateStart,"mm/dd/yyyy") & " AND tblHourEnter.Week_Ending<=" & Format(Me.txtDateFin,"mm/dd/yyyy") & " "
End If

And sometimes you need a "#" in also:

If Not IsNull(Me.txtDateStart.Value) Then
strSQL = strSQL & " AND tblHourEnter.Week_Ending>=#" & Format(Me.txtDateStart,"mm/dd/yyyy") & "# AND tblHourEnter.Week_Ending<=#" & Format(Me.txtDateFin,"mm/dd/yyyy") & "#"
End If

Use Debug.Print in you code, (until it runs okay), and put the output from the Immediate Window into a new query, then you'll see what is wrong.

Debug.Print strSQL

 

[gsandy]

Thanks JHB, the second code with # did the tricks. Cheers Sandy

 

[JHB]

Good you got it.