Concatenation of search criteria with db.FindFirst 1

[polyonoma]

I need to search within a table, defined as rec1, for records that match both criteria:

field [Zip] = input zip code
field [Zip4] = input zip+4 code.

Current code as typed into the form is:

rec1.FindFirst "Zip =" & [Forms]![form1]![ZipName]
rec1.FindFirst "Zip4 =" & [Forms]![form1]![Zip4Name]

Which returns records in which the Zip4 equals Zip4Name, but the Zip has nothing at all to do with the entered ZipName.

I tried to use an ampersand (&):

rec1.FindFirst "Zip =" & [Forms]![form1]![ZipName]& "Zip4 =" & [Forms]![form1]![Zip4Name]

Which returns a syntax error. The help files are not helpful in figuring out the correct syntax. Just for argument's sake, I tried using "AND:

rec1.FindFirst "Zip =" & [Forms]![form1]![ZipName] AND "Zip4 =" & [Forms]![form1]![Zip4Name]

But that also yields a syntax error.

My questions:
1. Is it possible to use multiple arguments in a FindFirst command? If so, how?

2. If not, the only other solution I see is to create a temporary table, rec2, to pass all records that fit the first criterion to, then use rec2.FindFirst to search within that. But that seems rather unnecessarily code-intensive. Are there any other options here?

Thanks!
-Shawn

 

[BillPower]

Private Sub Command5_Click()
Dim strSQL As String
Dim strZip As String
Dim strZip4 As String
strZip = Forms!Form1!ZipName
strZip4 = Forms!Form1!Zip4Name
strSQL = " WHERE Zip = '" & strZip & "' AND " _
& " Zip4 = '" & strZip4 & "'"
Me!ControlToBeSearched.SetFocus 'change this control name to your control name.
DoCmd.FindRecord strSQL
End Sub

 

[polyonoma]

I'm still getting "Missing operator in expression" error. It's the same error I was getting before.

 

[BillPower]

Hi polyonoma,

If you want, email your db to me and I'll see if I can spot what's going wrong. billpower@cwcom.net

Happy New Year

 

[polyonoma]

Bill -

I appreciate your help. The database you sent back did a great job of looking up the requisite Zip & Zip4. However, I need to return the closest 36 records (2-pages of report) to the first instance of the selected zip/zip4 combo when the entire database is sorted by: Zip, Carrier_route,Zip4. This is due to the geographic grouping of the records.

I'm a bit confused as to where you're storing the data that fits the queries - it'll take me awhile to deconstruct how you set up the database.

It might help if I explain the purpose of the database - I have a list of people who've requested information or we've had contact with them in the past - up to 150,000 names. The agent, "Bob", has zip and zip4 on an appointment that he has.

Bob should be able to put in that zip/Zip4 combo and pull up the (geographically) nearest 36 records so that if he's not doing anything else for awhile before or after that appointment, he knows which of their neighbors are interested and he can knock on their doors.

That's what all the recordset.count stuff and the x = 1 to 18 statements in the code I sent you were for - to narrow the recordset returned by the queries to a manageable 2-page report.

Does that make sense?

-Shawn