Hi Hitesh,
I opened up your DB and ran the Form named 'Copy of 12-09-2011'.
This is the code behind the Search button:
Code:
Private Sub Command229_Click()
Dim strCriteria As String
Dim ReportDate As Date
ReportDate = Me.[Report Date]
strCriteria = "[Agent] = '" & Me.[Agent] & "' "
strCriteria = strCriteria & "AND [Report Date] = #" & [Report Date] - 1 & "#"
DoCmd.OpenForm "DetailForm", acFormDS, , strCriteria
End Sub
It is doing exactly what it is supposed to be doing.
It is displaying ALL records in the DetailForm that have a matching Agent and a date that is one day before the [Report Date] on the 'Copy of 12-09-2011' form.
I modified some dates in your table.
For agent 'Finance' - I changed the first three 'Report Date' values to 9/13/2011.
Then I opened the Form and with the Form on the first Record [Customer Name Colm] AND Report Date 9/13/2011 - I clicked the Search button.
I got 8 rows of data - all with Agent 'Finance' and Report Date 9/12/2011 - shown in Datasheet view - on DetailForm.
If you want to see only ONE record at a time on the DetailForm - change this:
Code:
DoCmd.OpenForm "DetailForm", acFormDS, , strCriteria
to
Code:
DoCmd.OpenForm "DetailForm", , , strCriteria
Let me know if I am not understanding your problem.