union query help

[barit]

How do I change my query so that it recognizes <ALL> when <ALL> is the choice selected from an unbound combo box on a form?

On Form1 I have 3 unbound combo boxes that are used to set the parameters for a report. Using the following union query, I added all to the combo box called Occassion


SELECT OccassionType.Occassion, OccassionType.OccassionID FROM OccassionType UNION SELECT '<ALL>', '0' FROM OccassionType
ORDER BY OccassionType.Occassion;

In my query for the report I have been using
[Forms]![Form1]![Occassion]
to obtain the selected criteria from Form1. This does not work now that I have added <ALL> to the choices.

How would I change the query to recognize <ALL> as an option and to in turn create the report with all records.

Any help would be greatly appreciated.

Thanks

 

[frank610]

Here's is one solution:

Remove the parameter from your query. Instead, add code to the button that previews the report. Use something similar to this:

Dim stDocName as String, stLinkCriteria as String
stDocName = <Name of the report>
If Me.Occassion <> "<All>" then
stLinkCriteria = "Occassion = '" & me.Occassion & "'"
End If
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Hope this helps.

 

[PHV]

Something like this ?
WHERE ...
AND ([name of field Occasion]=[Forms]![Form1]![Occassion] OR [Forms]![Form1]![Occassion]='<ALL>')
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244