Filter Code Not Working 1

[Quintios]

I have a report that, when run, I would like to see the applicable records for the last 30 days.

In the Form_Open event, I have the following code:

Private Sub Report_Open(Cancel As Integer)
Dim varDate As Variant

    varDate = Now() - 30
    
    Me.FilterOn = True
    Me.Filter = "Date >= varDate"
    
End Sub

It's not working. I get a dialog box asking for a value for varDate.

How do I get this to work?

Thanks in advance,
Onwards,

Q-

 

[Steve101]

Q,

Your form is seeing the filter as a single string value. You need to pre-evaluate the date value.

Try the following slight modification

Private Sub Report_Open(Cancel As Integer)
Dim varDate As Variant

varDate = Now() - 30

Me.FilterOn = True
Me.Filter = "Date >= CVDate('" & varDate & "')"

End Sub

This will pre-evalaute vardate, and pass it as a "constant" value to the form's property. Hopefully it will do the trick.

Good luck,
Steve

 

[DavidWill]

Q,

You can try this:

Private Sub Report_Open(Cancel As Integer)
Dim varDate As Variant

varDate = Now() - 30

Me.FilterOn = True
Me.Filter = "Date >= '" & varDate & "'"

End Sub

Hope this helps,
Dave

 

[Quintios]

Steve, your code worked!

Dave, your code didn't. :-( I got a 'data type mismatch' when I ran it.

But thank you both for your help!!
Onwards,

Q-

 

[Steve101]

The "data type mismatch" is occuring as the date variant needs to be converted to a DATE datatype before the filter can be successfully applied; hense the CVDate function.
Cheers,
Steve

 

[Quintios]

Should I have not used the variant datatype then? Is there a 'date' datatype?
Onwards,

Q-

 

[Steve101]

Q,

Let me try and offer a more detailed explanation:

The 'data type mismatch' error message is being generated when the query tries to run, NOT when the assignment of the string is made to the form's filter property (although the one automatically follows the other.

You'll note that I included the CVDate() function call in the actual string which is passed to the filter. This is so when the filter runs, the string is converted before being compared to the "Date" date field.

Yes; there is a Date datatype. The other option we could have used was to convert the date "string" to a date "value" of type "date" by enclosing the mm/dd/yy in hash symbols; ie. the appropriate line of code would have become:

Me.Filter = "Date >= #" & varDate & "#"

but beware, vardate would have had to be entered in the format of month, day, year (eg. 12/31/02), as this form of date insists on this format.

Personally, I prefer the CVDate approach, but its really horses for courses. I live "down under" where we use the dd/mm/yy format, so I guess this is the reason for my preference. (I never could understand the logic behind representing the date in mm/dd/yy order.)

Hope this makes it clearer,
Cheers,
Steve

 

[Quintios]

Hey Steve, that makes sense. Thanks for your thorough explanation! One more star for you!
Onwards,

Q-