Filter form by date

[Mpho Pole]

Hi
I have a form that feeds filter parameters to a report. My problem stems from using date parameters to filter.My code is as follows:

If IsNull(Me.Date.Value) And IsNull(Me.EndDate.Value) Then 
strDate = "Like '*'"      
Else          
strDate = "Between " & Me.txtDate.Value & " And " & Me.txtEndDate.Value      
End If

The value to be returned by the above code is supposed to any info between txtDate and txtEndDate
The date parameters are two textboxes on the form and they get values from a popup calendar.
What am I doing wrong?

[NeoPa]

If this is for a Filter propert then you need to specify the field that it's filtering.
Unlike in the Design Grid Criteria, the field being tested must be included.

[NeoPa]

If no filter required then it's better to pass no filter value than one that resolves (eventually) to all records.
.Value is not required in your formulas.
Dates should be formatted correctly if passed as literals (as yours are).
Replace

& Me.txtDate.Value &
with
& Format(Me.txtDate,'\#m/d/yyyy\#') &

[MMcCarthy]

As Adrian said you only need the filter if filter required. Something like the following.

Dim strCriteria As String
 
If Not IsNull(Me.Date) And Not IsNull(Me.EndDate) Then 
   strCriteria = "[DateFieldName] Between #" & Me.txtDate & "# And #" & Me.txtEndDate & "#"
   DoCmd.OpenReport "ReportName", acViewPreview, , strCriteria
Else
   DoCmd.OpenReport "ReportName", acViewPreview
End If
 
 

Mary

[Mpho Pole]

Thanks Mary
I have realised what my mistake was. The date value from the calendar and the field on the report had different date formats plus the I did not use the (#).

As Adrian said you only need the filter if filter required. Something like the following.

Dim strCriteria As String
 
If Not IsNull(Me.Date) And Not IsNull(Me.EndDate) Then 
   strCriteria = "[DateFieldName] Between #" & Me.txtDate & "# And #" & Me.txtEndDate & "#"
   DoCmd.OpenReport "ReportName", acViewPreview, , strCriteria
Else
   DoCmd.OpenReport "ReportName", acViewPreview
End If
 
 

Mary

[MMcCarthy]

Thanks Mary
I have realised what my mistake was. The date value from the calendar and the field on the report had different date formats plus the I did not use the (#).

No problem. I'm glad you worked it out.

Mary

[NeoPa]

No problem. I'm glad you worked it out.

Mary

I'm invisible! (Think scene from GoldenEye)

[MMcCarthy]

I'm invisible! (Think scene from GoldenEye)

I'm glad you realise it. :D

Mary