Two option Criteria

[MCCDOM]

Hi There,

I have built myself a form where I can choose an office from a combobox and date range from a textbox and then run a query that generates a report to show all records that meet that criteria (see attachment for a screenshot of my query in design form). What I would like it to also do, but can't fathom it out, is to show all the offices on the report if I don't choose an office in the combobox.

Many Thanks,

Dom

 

[pbaldy]

One way:

http://access.mvps.org/access/queries/qry0001.htm

 

[plog]

form where I can choose an office from a combobox and date range from a textbox and then run a query that generates a report

Define "report". Do you mean it generically as in any output of data? Or do you specifically mean an Access Report Object?

Hopefully you mean a Report Object. Because then when the button is clicked you open that report via DoCmd.OpenReport (https://msdn.microsoft.com/en-us/library/office/ff192676.aspx) and pass it a filter. You would build that filter by looking at the form--if an offfice was chosen you build the filter to include it, if not, you simply don't include an office filter criteria.

In psuedo code, it would look like this:

strReport = "YourReportName"
strFilter="(1=1)"

if Form.Office Is Not Null Then strFilter = strFilter & " AND [OfficeNameField]=" & Form.Office

DoCmd.OpenReport, strReport, strFilter

Just to be clear, the above is not going to work when you paste it into your event--its pseudocode--written quickly to give you an idea of how the code should work.

 

[MCCDOM]

Hi pbaldy and plog,

Thank you both for your replies. You have solved my issue for me.
In answer to your question plog, I am using an Access Report Object.

Many thanks,

Dom