using textbox with In () in query

[TheShabz]

Hi everyone.



I am creating a form which will allow the user to enter in certain zipcodes to be searched in the query that is set up. The user can choose multiple zipcodes to query. I assumed I could use the "In(Forms!FormName!TxtBoxName)" and prompt the user to enter commas between zipcodes but it is not working. What is my fix here?

Thanks

[maximus]

Reading your thread I understand that you have the following requirements:
1) You have a table with ZipCodes.
2) You have a form with a text box in it you want to type more than one zip code seperated by a comma.
3) A query will take the criteria from the text box and display results.


this what I have done:

1) Form CreateQuery opens as startup and lets you enter zip codes. please refer to the instructions in the form. Please type zip code already entered to check results.
2) Click Create Query button to create the query and open the same.

Below is the codes used:


On Error GoTo Err_Command0_Click
Dim strSQL As String
Dim strQueryName As String
Dim strCondition As String

If Left$(Me.Text1, 1) = "," Then
MsgBox "You have put a comma at the beginning", vbInformation
ElseIf Right$(Me.Text1, 1) = "," Then
MsgBox "You have put a comma at the end", vbInformation
Else
strCondition = Replace(Me.Text1, ",", "'" & " Or ZipCode= " & "'") 'Generating query condition by replacing the commas.
strCondition = "'" & strCondition & "'"
Set dbs = CurrentDb
strQueryName = "MyQuery"
dbs.QueryDefs.Delete strQueryName 'Delete Existing query
strSQL = "SELECT * From Table1 Where ZIPCode=" & strCondition 'QueryDef used to create query
Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)
DoCmd.OpenQuery strQueryName 'opening the query
End If
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


1) I have used Replace to format the string entered in the text bos to create the condition form my query.
2) I have used querydef to cereate the query.

If this solves your problem mark the thread solved.

[TheShabz]

Thanks Maximus.

EDIT: I am an idiot. Totally ignore the issues that follow. Will mark as solved once i get it working

It worked fine in your db, but the transfer to mine is giving me an error. The changes I made were:
1. changed strQueryName to my own,
2. changed Me.text1 to my txt field.
3. replaced strSQL with the SQL in my query
4. changed your Replace function to make it number instead of a text datatype (my zips are ints)
5. commented out the second strCondition= line (again, i have ints not text)

To check the variables, I debug.print-ed after the Replace function and after strSQL=SELECT....

the first one printed correctly, but the second one didnt print at all. I assume the error is somewhere in between. the error I am getting is "Item not found in this collection". That's a new one to me.

Ideas?

EDIT: oh and I'd love to put up the db, but I cant.

[maximus]

I had anticipated a problem like this and desperately wanted to explain some finer points like what to do in case your zip codes were integer. But I was too lazy and thought you will figure it out.

Check strCondition

Now when the ZipCodes are integers we don't need to enclose them in '' that is '999','888'. So a slight modification is required in the strCondition:

strCondition = Replace(Me.Text0, ",", " Or ZipCode=")

try this is you have already done this the problem lies some where else that atleast

anyways I just loved solving this problem. I never thought of doing something like this before.

[TheShabz]

I fixed it already. Thx for the tip. I never thought of replacing the comma's with "OR Zip = ". I saved that code in my code folder. It'll be useful later too. thread was marked solved yesterday too