Options
Anyone decent with Access VBA?
Megadeth4168
Member Posts: 2,157
in Off-Topic
I'm having an issue with what I feel should be an easy to implement code.
I'm filtering a Report based on criteria selected in a Form... Something I'm familiar with and have done many times. Except, in this case I'm working with Dates and it is throwing me off.
Here is the relevant code:
The result is a blank report, however, if I change the sign to > I get the full report minus any records where Warranty Expiration is null.
I've checked the report and made sure that Warranty Expiration is formatted as date.
Clearly I can use a workaround by creating separate Queries and reports based on those queries but I'm trying to avoid that.
Any quick ideas?
Thanks.
I'm filtering a Report based on criteria selected in a Form... Something I'm familiar with and have done many times. Except, in this case I'm working with Dates and it is throwing me off.
Here is the relevant code:
if checkboxExpiration.value= -1 then Criteria = "[Warranty Expiration] < " & Date DoCmd.OpenReport stDocName, acPreview, , Criteria end if
The result is a blank report, however, if I change the sign to > I get the full report minus any records where Warranty Expiration is null.
I've checked the report and made sure that Warranty Expiration is formatted as date.
Clearly I can use a workaround by creating separate Queries and reports based on those queries but I'm trying to avoid that.
Any quick ideas?
Thanks.
Comments
-
OptionsMegadeth4168 Member Posts: 2,157Ok... I see what's going on.
Using DateValue(Warranty field)
and a msgbox
it's showing me the first record's date:
12/01/2008 < 6/07/2011
I think the code in this case is looking at the first Date and applying it as the date for the entire filter instead of checking each Warranty Expiration in each row. -
OptionsMegadeth4168 Member Posts: 2,157For anyone who is curious or runs into this it is a simple straight forward solution that I knew but wasn't thinking clearly about at the time.
Here is *fixed* code.if checkboxExpiration.value= -1 then Criteria = "[Warranty Expiration] < #" & Date & "#" DoCmd.OpenReport stDocName, acPreview, , Criteria end if
Simply encapsulate Date in between the pound signs.