Cancel the 2nd input if the 1st input is empty

[Scott]

I have a parameter query, "Between [Enter Start Date (dd/mm/yy):] And
[Enter End Date (dd/mm/yy):]". If I want to show all records, how can I
disable the 2nd input.

Thanks,

Scott

 

[Guest]

well - your parameter query when left blank will then have no filter applied
and will return all records.....

so although from within the Query itself there really is no mechanism to
disable the 2nd parameter input request - -but you can inform the user to
leave blank as part of the parameter prompt : i.e.

Like [Enter End Date or Leave Blank for all dates]

hope this helps

 

[John Spencer]

Do you mean you want to show all records after the start date? You could
use the NZ function to force some date far in the future.

Between [Enter Start Date (dd/mm/yy):] And NZ( [Enter End Date (d/m/yy)
Blank for All:],#1/1/9999#)

 

[Scott]

NTC,

Thanks for your reply. Sometimes, I would like to show all records. If I
just enter return twice, it will give a result of no records. I would like
to enter return once for all records and the 2nd input will be suspended
automatically due to the filter becomes unnecessary. It is more user
friendly behaviour.

Scott

well - your parameter query when left blank will then have no filter
applied
and will return all records.....

so although from within the Query itself there really is no mechanism to
disable the 2nd parameter input request - -but you can inform the user to
leave blank as part of the parameter prompt : i.e.

Like [Enter End Date or Leave Blank for all dates]

hope this helps
--
NTC

I have a parameter query, "Between [Enter Start Date (dd/mm/yy):] And
[Enter End Date (dd/mm/yy):]". If I want to show all records, how can I
disable the 2nd input.

Thanks,

Scott

 

[Scott]

John,

Thanks for your reply. Yes, enter return once to show all records. In
addition, to disable the second input as the 2nd input becomes unnecessary.

Scott

Do you mean you want to show all records after the start date? You could
use the NZ function to force some date far in the future.

Between [Enter Start Date (dd/mm/yy):] And NZ( [Enter End Date (d/m/yy)
Blank for All:],#1/1/9999#)

I have a parameter query, "Between [Enter Start Date (dd/mm/yy):] And
[Enter End Date (dd/mm/yy):]". If I want to show all records, how can I
disable the 2nd input.

Thanks,

Scott

 

[John Spencer]

It can't be done using a parameter query as you have it set up. If you have
two parameters, you are going to get prompted twice.

You could do it with a form and some controls on the form that the query
would reference. Or you could try the following. Warning: Access will
rewrite this when you save/close the query.

Field: YourDateField
Criteria: >= [Enter Start Date] OR [Enter Start Date] is null

<<Add a second reference to the field>>
Field: YourDateField
Criteria: <= [Enter End Date] OR [Enter End Date] is Null

Of course, if your date field ALWAYS has a value then you can use the first
technique I mentioned on both parameters forcing a date in the past that is
earlier than any date in your records and a date in the future that is later
than any date in your records.

Field: YourDateField
Criteria: Between NZ([Enter Start Date (dd/mm/yy):],#1/1/1899#)
And NZ( [Enter End Date (d/m/yy) Blank for
All:],#1/1/9999#)

IF you really want you can even use NZ on your date field and return a
non-null date value. Of course, this means all your queries will run slower
since any index on your date field will not be useable in selecting
records.


Field: ForceDate: Nx(YourDateField,#1/1/9999#)
Criteria: Between NZ([Enter Start Date (dd/mm/yy):],#1/1/1899#)
And NZ( [Enter End Date (d/m/yy) Blank for
All:],#1/1/9999#)

John,

Thanks for your reply. Yes, enter return once to show all records. In
addition, to disable the second input as the 2nd input becomes
unnecessary.

Scott

Do you mean you want to show all records after the start date? You could
use the NZ function to force some date far in the future.

Between [Enter Start Date (dd/mm/yy):] And NZ( [Enter End Date (d/m/yy)
Blank for All:],#1/1/9999#)

I have a parameter query, "Between [Enter Start Date (dd/mm/yy):] And
[Enter End Date (dd/mm/yy):]". If I want to show all records, how can I
disable the 2nd input.

Thanks,

Scott