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

Expand|Select|Wrap|Line Numbers

 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.

Retrieve records two dates (MS ACCESS)

re: Retrieve records two dates (MS ACCESS)

re: Retrieve records two dates (MS ACCESS)

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about