Anyone decent with Access VBA?

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:

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.

Find more posts tagged with

Free for TechExams community: Cybersecurity salary guide

Compare cert salaries and plan your next career move

Button

Comments

Megadeth4168

Ok... 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.

Megadeth4168

For 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.