How to take Quotes out of Criteria in a Query (1 Viewer)

Joye

Registered User.
Local time
Today, 21:35
Joined
Aug 3, 2001
Messages
34
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

Registered User.
Local time
Today, 14:35
Joined
Apr 19, 2000
Messages
232
Have you placed an equal sign = in front of the criteria?:

=forms!frmTowns!Text1
 

Joye

Registered User.
Local time
Today, 21:35
Joined
Aug 3, 2001
Messages
34
Yes, I have. It still puts the quotes in in addition to my quotes.
 

Elana

Registered User.
Local time
Today, 14:35
Joined
Apr 19, 2000
Messages
232
it should be placing brackets around it. Try placing brackets:

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

E
 

Joye

Registered User.
Local time
Today, 21:35
Joined
Aug 3, 2001
Messages
34
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

Registered User.
Local time
Today, 14:35
Joined
Apr 19, 2000
Messages
232
:eek: 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

Back once again...
Local time
Today, 21:35
Joined
Dec 10, 2002
Messages
11,316
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:35
Joined
Feb 19, 2002
Messages
42,976
You are attempting to change the actual structure of the query. You cannot do this with a variable. Switch to building the entire SQL string in code instead. Also, use the In(...) syntax rather than "or". Your string will get too long with OR's because you also need to repeat the field name -
Somefield = "a" or somefield = "b" or .... NOT somefield = "a" or "b".
 

Joye

Registered User.
Local time
Today, 21:35
Joined
Aug 3, 2001
Messages
34
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.
 
R

Rich

Guest
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
 

Users who are viewing this thread

Top Bottom