How to take Quotes out of Criteria in a Query

[Joye]

Ok, I have created a list box on my form where people can choose all the towns they are interested in seeing data from. Then I take all the towns and create a string expression to send to my query - storing it all in a text box. For example, say they choose... Chelsea, Monkton, Addison... in the list box, they click on a command button and in the text box this is expression that shows up... "Chelsea" or "Monkton" or "Addison" - Then they click to view the report for with the informatin for these towns. So, in the query for the report in the TOWN field I would put the criteria as ... forms!frmTowns!Text1 - The problem is that Access inserts it's own Quotes and it is therefore throwing off my string. I just want to STOP access from inputting it's own quotes. And just read my string exactly as it is.

Can anyone suggest anything???

Thanks

 

[Elana]

Have you placed an equal sign = in front of the criteria?:

=forms!frmTowns!Text1

 

[Joye]

Yes, I have. It still puts the quotes in in addition to my quotes.

 

[Elana]

it should be placing brackets around it. Try placing brackets:

=[forms]![frmTowns]![Text1]

E

 

[Joye]

Yes, I have put the brackets around it, but it still must be defaulting to putting quotes around it. The query works perfectly when I only enter one town in Text1... such as Addison... and I leave the quotes out. It runs as it should and shows me all the data I am looking for.

 

[Elana]

Sorry, I spaced out that you were attempting to use multiple criteria here. I don't know off the top of my head how to solve the problem you are experiencing.

Hope someone else can help -

 

[Mile-O]

Rather than making your expression builder put the quotation marks around the names of these places, try putting an apostrophe and see how that goes.

 

[Joye]

No, I have tried everything. Nothing is working. It always defaults to putting the quotes in no matter what I do. I want it to take the text as is and then treat it like I just entered a parameter value in.... so... it will take Addison, Bristol, Barre - and treat it like I just entered those three parameter values and it will convert it to 'Addison', 'Bristol', 'Barre'... instead of "Addison, Bristol, Barre"

Is this possible? Or is there some other way I should do this with a list box.

 

[Rich]

Dim MyDB As DATABASE
Dim qdf As QueryDef
Dim I As Integer, strSQL As String
Dim strWhere As String, strIN As String
Dim flgAll As Boolean

Set MyDB = CurrentDb()
Select Case Me.optVal
Case Is = 1
strSQL = "INSERT INTO tblMail ( fldLtrPK, AddressID )SELECT tblLetters.fldLtrPK, Addresses.AddressID FROM Addresses, tblLetters "




'create the IN string by looping thru the listbox
For I = 0 To List0.ListCount - 1

If List0.Selected(I) Then
If List0.Column(0, I) = "All" Then
flgAll = True
End If
strIN = strIN & Chr(34) & List0.Column(2, I) & Chr(34) & ","
End If
Next I

'create the WHERE string, stripping off the last comma of the IN string
strWhere = "WHERE [LastName] In (" & left((strIN), Len(strIN) - 1) & " ) And ([tblLetters].[fldLtrPK] In(" & Me![Text7] & "))"

'if "All" was selected, don't add the WHERE condition
If Not flgAll Then
strSQL = strSQL & strWhere
End If

DBEngine(0)(0).Execute strSQL, dbFailOnError