"Filter by on load" problem with dates

[BDCarrillo]

Trying to do the following:

Filter records to show only those whose "End_Date" is equal to or greater than today. This will be set on the form properties.

I've tried the following:
End_Date>=Today()
End_Date>=Now()
End_Date>=Date()

All of the above with #End_Date#, and all of the above with quotation marks, and with [End_Date]

I do have filter on load set to "yes".

 

[dhookom]

So, what is the problem? We can assume this doesn't work or else you wouldn't ask but what are your results?

This expression should work:
End_Date>=Date()

Are you getting an error message?
Are the displayed records not what you expect?

Duane
Hook'D on Access
MS Access MVP

 

[BDCarrillo]

Just looked through my code when I noticed the filter was "disappearing"

I was putting in my own filter from a previous form in VBA. I'll have to incorporate the date filter in with my other coded filter, unless I can apply a "sub" filter.

 

[BDCarrillo]

Need to bring in "End_Date>=Date()"

To this:

DoCmd.OpenForm "Shop_Form", acNormal, , "[Shop_Code] =" & Me.Shop_Code1 & "", , acDialog

and include a couple different "if" scenarios to cover any cases where the date filter returns zero records

 

[boblarson]

If this is ALWAYS a requirement then why not just change the SQL behind the form's record source?

Then the criteria can be

WHERE [End_Date] >= Date()

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[dhookom]

Try this:

Dim strWhere as String
strWhere = "[Shop_Code] =" & Me.Shop_Code1 & " AND End_Date >=Date()"
DoCmd.OpenForm "Shop_Form", acNormal, , strWhere, , acDialog

Duane
Hook'D on Access
MS Access MVP

 

[BDCarrillo]

I swear I had tried it like that... turns out I had it try to write in the value from me.shop_code1 before finding the record that matched the user input...

Works just fine now dhookum, thanks! I put the code snippet in the right spot.

boblarson: I could have done it in the form data source but I already had a precarious web of 6 forms loaded and 6 various joins. Also, it was a bit of a personal preference to code both filters in one location, so that I could easily expand future capability based on customer request (ie: i want to see all of the jobs older than 30 days if i click a checkbox sort of deal)

Thanks guys! It's always the small quotation marks that get me