Retrieve records two dates (MS ACCESS)

[JohanKotze]

I design a report that will show all the records for a particular client and all the records do show in the report BUT when I try to search between dates than only half of the records are showing ....... records in the middle of the date range are missing and EndDate records also doesn't show. I use the following code on the OnClick Event of the OK button on my form

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click
Dim stCriteria As String
Dim stDocName As String
stLinkCriteria = "([ClientNr]=" & Me.ClientNr & ") AND ([Date] Between #" & Me.BeginDate & "# AND #" & Me.EndDate & "#)"
stDocName = "PersAmendmentMovByDate"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Exit_Command4_Click:
Exit Sub
Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click
End Sub

[NeoPa]

Almost certainly because the dates aren't treated consistently as dates.

A string with a formatted date in it will sort as a string, and not a date.

Does that help?

Welcome to Bytes!

[NeoPa]

PS. "#" characters are needed for dates it's true, but it not quite as simple as that. See Literal DateTimes and Their Delimiters (#) for the full lowdown. Particularly about formatting the date into SQL standard format to use in SQL.