Create Default Query Filter

[wdrspens]

Hi,
I am no good at programming access code, but reasonable at designing an access database to produce what I require using its inherent design criteria in the Design View of a query. However I have hit a stumbling block which I would like to overcome, if at all possible.
I have a date field called "EventDate" and I have only occasional use for dates that occur prior to today, and therefore can impose a filter in Design View criteria for that field by using the >Now() expression.
In order to interrogate earlier dates, at the moment, I have to open the relevant query and remove that filter.
To make it easier for myself I put in a question >[Enter earliest start Date] and sure enough I can back or post date the data, but if, when the message box opens, I just hit <Enter> I get nothing. Is there any way I can make it default to >Now() if I just hit <Enter>. i.e. can I put in the relevant line of the design view of the query criteria something like ">[Enter earliest start Date] Or Now()"? I have tried all combinations I can think of, using And, Or and Xor, so far without success.
Many thanks
David

 

[HiTechCoach]

This is another reason I do not like using parameter prompts in queries, especially with dates.

I like to do this by placing a text box, like in the form's header to get the date.

Then:
1) In the query use the criteria of > forms!frmYourFormName.txtDateTextBoxName
2) In the textbox control's After update event use: Me.Requery
3) In the form's On load event you can set the Text to be the cirrent Date using: Me.txtDateTextBoxName = Date()

This will also allow you to use a Calendar to pick the date.

Note: date() returns just the date, and Now() Returns the date with the time. Unless you really need the time to be part of the criteria, I would use Date()


For an example of how to do this with a report that uses a from to get the date, see the attached.

 

[wdrspens]

Thank you very much for that hint on Date() rather than Now(). That is appreciated greatly.
I have never tried the remainder of your suggestion in the past, but will give it a go. The problem I can see arising, however, is that how do I get it to work for a Report rather than a Form?
Thanks

 

[wdrspens]

I tried it, but came up with a message box that said "Microsoft Office Access can't find the object 'Me.' If 'Me' is a new macro or macro group, make sure you have saved it and that you have typed its name correctly."

That shows how limited my experience is on using code. I haven't a clue what to do although I did try very hard to follow your suggestion.

Again many thanks for your help

 

[HiTechCoach]

Thank you very much for that hint on Date() rather than Now(). That is appreciated greatly.
I have never tried the remainder of your suggestion in the past, but will give it a go. The problem I can see arising, however, is that how do I get it to work for a Report rather than a Form?
Thanks

The example I post is for doing this with reports. Did you get a chance to look at my attachment?

 

[wdrspens]

Thank you very much. I did look at your attachment but it is far too far advanced for me to understand.
I tried looking at the various Forms in Design View to see if that would advance my understanding, but as I did not know for what I was looking I did not find anything to advance it.
I am not at that stage yet in my development.
Thank you very much all the same

 

[wdrspens]

Thank you all for your help, which has been greatly appreciated, but I have now resolved my problem by the simple insertion of the following in the criteria for the [EventDate] field under the appropriate query:

>[Enter Start Date] Or >Date()-1

It works!

I had previously omitted the > sign before Date()-1, and as there were no entries for yesterday, I did not pick up on the fact that was missing. Silly of me.