Query criteria question?

[WannabePrgmr]

I have a query in access 2003 that currently has the following criteria in the "DateAdded" field (which is a date and time stamp):

Between Now() And Now()-3
 

This obviously brings back only data from the last 3 days.

I would like to keep this the default, but I am trying to add textboxes that the user will be able to look at a certain period of data based on what they enter into boxes "Start Date" and "End Date" that are on the table.

The query is displayed on the same form as the new textboxes that I have created.

When the user enters the form, I have it automatically displaying the query with the default being what I listed above. The "Start Date" and "End Date" boxes will be empty until the user enters dates that they want to see. Then the command button will requery with the following criteria in the "DataAdded" field:

Between [Forms!]![frmChad]![Start Date] And [Forms!]![frmChad]![End Date] Or Is Null
 

I have this code and the default code working independantly, I just need to know how to have the first be the default and the second work only when dates are entered into the search boxes.

Thanks

[beacon]

I could be completely wrong on this, so if you take my advice please have a backup copy of your DB handy in case my suggestion destroys your work.

I think you can leave the initial criteria in the query, but you need to remove the second criteria (the one for Start/End Date) because the query will expect to receive parameter if the criteria isn't blank.

You should be able to leave the initial criteria, write code to see if the Start/End Date boxes are null or not, then, if they aren't null, execute a query def. Actually, it might be easier to do the query def for the whole thing instead of trying to split up the criteria, but that's up to you.

[nico5038]

You could use the NZ() function like:

Between NZ([Forms!]![frmChad]![Start Date],Now()) And NZ([Forms!]![frmChad]![End Date],Now()-3)
 

But I would set the dates on the form to Now() and Now()-3 by default and require both fields to be filled before activating the query.

Nic;o)

[WannabePrgmr]

Thanks Beacon and Nico! I appreciate your help!
I will check these methods out......

[topher23]

I second Nico. I've done something similar to several reports to allow users to shortcut commonly used options. Although setting the default in the form is useful, if you call that report from several different forms, keeping them all up-to-date can be a headache.

[NeoPa]

Unless you want your criteria to start and end in the middle of a day I would stop using Now() for this right away. Date() is more likely what you want and is not likely to throw up any complicated "Why is it selecting those records?" type questions.