By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,400 Members | 981 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,400 IT Pros & Developers. It's quick & easy.

Filter form by date

P: 3
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:

Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me.Date.Value) And IsNull(Me.EndDate.Value) Then 
  2. strDate = "Like '*'"      
  3. Else          
  4. strDate = "Between " & Me.txtDate.Value & " And " & Me.txtEndDate.Value      
  5. 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?
Dec 12 '06 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,661
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.
Dec 12 '06 #2

NeoPa
Expert Mod 15k+
P: 31,661
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
Expand|Select|Wrap|Line Numbers
  1. & Me.txtDate.Value &
  2. with
  3. & Format(Me.txtDate,'\#m/d/yyyy\#') &
Dec 12 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
As Adrian said you only need the filter if filter required. Something like the following.

Expand|Select|Wrap|Line Numbers
  1. Dim strCriteria As String
  2.  
  3. If Not IsNull(Me.Date) And Not IsNull(Me.EndDate) Then 
  4.    strCriteria = "[DateFieldName] Between #" & Me.txtDate & "# And #" & Me.txtEndDate & "#"
  5.    DoCmd.OpenReport "ReportName", acViewPreview, , strCriteria
  6. Else
  7.    DoCmd.OpenReport "ReportName", acViewPreview
  8. End If
  9.  
  10.  
Mary
Dec 13 '06 #4

P: 3
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.

Expand|Select|Wrap|Line Numbers
  1. Dim strCriteria As String
  2.  
  3. If Not IsNull(Me.Date) And Not IsNull(Me.EndDate) Then 
  4.    strCriteria = "[DateFieldName] Between #" & Me.txtDate & "# And #" & Me.txtEndDate & "#"
  5.    DoCmd.OpenReport "ReportName", acViewPreview, , strCriteria
  6. Else
  7.    DoCmd.OpenReport "ReportName", acViewPreview
  8. End If
  9.  
  10.  
Mary
Dec 13 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 13 '06 #6

NeoPa
Expert Mod 15k+
P: 31,661
No problem. I'm glad you worked it out.

Mary
I'm invisible! (Think scene from GoldenEye)
Dec 14 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm invisible! (Think scene from GoldenEye)
I'm glad you realise it. :D

Mary
Dec 14 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.