Parameter query with udefined numer of parameters

[BillM]

Have a table that has a field for Zip Codes.

Have a query that is used to run a report for zip codes the user is
enquiring about.

Am now using "Or" for multiple Zips.

Example: [What Zip Code?] Or [Second Zip] Or [Third Zip] Or ...etc,
etc.

Problem is that the number of Zips that can be queried is fixed by how
many Or's I use.

If I do 5 Or's, then the user must go through all 5 even if only one
or two zips are requested. On the other hand if the user wants to
query more than 5, it can not be done without running a second time.

Zips may be random and non consecutive and likely not be within a
numerical range. For example might want xx403, xx406, xx425, xx578,
xx601 but no others even though others exist between these numbers.

Would like to be able to enter a Zip paramater and then either run it
then, or continue to enter Zips until done and run then. User might
enter as few as one Zip or as many as 20-25.

I have read here about using combo boxes on forms to select but there
are well over 1,000 unique Zip codes and selecting from a list would
be extremely tedious.

Any help would be greatly appreciated.

Bill

 

[Steve Schapel]

Bill,

I don't think it is practical to do this sort of thing with a Parameter
Query. One approach would be to build the SQL of your query in a VBA
procedure, the (untested "air code") skeleton of which would be, for
example...

Dim strSQL As String
Dim MsgAnswer As Integer
Dim ZipCrit As String
strSQL = "SELECT * FROM YourTable WHERE Zip = 'x'"
Do Until MsgAnswer = vbNo
ZipCrit = InputBox("What Zip?")
strSQL = strSQL & " OR Zip ='" & ZipCrit & "'"
MsgAnswer = MsgBox("Another Zip?", vbYesNo)
Loop
DoCmd.OpenForm "ResultsForm"
Forms!ResultsForm.RecordSource = strSQL