Help with Form & Report that calls a Query

[bobfin]

Help again,

I have a form that calls a report that calls a query.
On the Form the the user enters the Begin and End Dates and the Account #
The report works fine, except when the Dates or the Account # is omitted it will not run.

I would like it run if either the date or the account number or both are empty.

Is this possible?

Bob

[NTC]

well - depends on really what you seek. The query runs but returns no records (correctly) because there are no record(s) with a blank in the Account# field (or date field, etc). (assumption on my part)

by "not run" - do you mean that the report is not opening blank? or that it is opening blank? and what is it that you want it to do?

as an experiment enter a record that has data in some fields but leaves the Acct # field blank. when you run the report it should show that record.

[bobfin]

Sorry bad wording on my part. Must have been tired...
All fields have to be filled in order for the report to work.

I want to make it, where if some or none of the fields have data that the reports works based on the data in the fields whether there is data or not
entered into the fields..

Thanks,

Bob

[stileguru]

I presume you're using a parameter query for the input. If so, the criterion for filtering can have wild card characters before and after the square brackets e.g. Try entering it as: Like "*" & [Enter Act No] & "*"
If the user doesn't know the account number he/she just tabs past this parameter and the search will find all the accounts relative to any of the other parameters that are entered. You can have the wildcards at the start or at the end to search on entries that either start with, or end with the input.

[bobfin]

No my Queries just have the fields to query. The parameters are called by the data the user enters into the fields on the form?

Bob

[stileguru]

Sorry. Maybe I'm misinterpretting the structure of your form. The form should have an underlying query, which, if you want it to search for records, should have parameters to filter of the sort I descibed in my first reply. The form then shows only the records you want. Then you switch to a report showing only those records. Am I correct here?

[bobfin]

the form has 3 Fields. Start Date End Date & Account#
The user inputs the date fields and the account#
The form then passes it to the Report which calls the query.

The Criteria on the query under Account# is [forms]![1formacc]![Text0]
Text0 being the Account# field on the Form.

It works just fine. But if the user wants to pull all accounts in those date ranges the report comes up blank.

I would like to have them leave the Account# field blank or even enter an*
to pull up all accounts

Hop this makes sense.
Bob

[stileguru]

Personally I don't do it that way. I would have a switchboard/menu set up with a button to directly open the report. In this scenario, the parameter criteria can be set in the underlying report query as described in my first post above. So, have [Enter start date] criterion for the start date field of the report query, [Enter end date] criterion for the end date field, and Like "*" & [Enter Act No] & "*" criterion for the Text0 field. On the last criterion, the user can leave it blank and all accounts will be listed in the resultant report preview.
Incidendetally, if start date and end date are derived from the same field data, a single criterion can be used: 'Between [Enter start date] and [Enter end date]'.
It may not be your preferred way, but I find it works very well. Hope this helps.