Double quotes in query

VPK_Access

Registered User.
Local time
Tomorrow, 02:05
Joined
May 15, 2011
Messages
36
Hi,

I have been struggling with the below code for many hours. Could anyone help me on this!

In my form I have a dropdown, which shows values - "A and B and C"
-"B"
-"C"

Depending on the value selected from the drop down I set a value in a text box and pass that value to my query. Now the problem is when I select the first value "A and B and C", I need to pass value to the query to retrieve value where the values can be "A or B or C".

In my query if I directly paste the value "A" or "B or "C"............it is working fine. However if I send the same value through a text box it doesnt give any output. I searched the net and in some sites they were telling to input a single code to enclose the string, stilll the problem is not solved. I have posted the code below. Any help would be really appreciated.

*********************************************
Private Sub StatsRDD_AfterUpdate()

Dim Str1 As String
Dim Str2 As String
Dim Str3 As String
Dim Str4 As String

Str1 = """"
Str2 = " Or "
Str4 = "'"

Str3 = Str4 + Str1 + "A" + Str1 + Str2 + Str1 + "B" + Str1 + Str2 + Str1 + "C" + Str1 + Str4


Forms!OpeningForm![StatsRDD] = ""
If Forms!OpeningForm![StatsRDD] = "A and B and C" Then
Forms!OpeningForm![StatsRDD-Name] = Str3

ElseIf Forms!OpeningForm![StatsRDD] = "A" Then
Forms!OpeningForm![StatsRDD-Name] = "A"
ElseIf Forms!OpeningForm![StatsRDD] = "B" Then
Forms!OpeningForm![StatsRDD-Name] = "B"
End If
End Sub



And in my query I am selecting the value based on what is apssed in the text box 'Forms!OpeningForm![StatsRDD-BankName]'.
 
You cannot pass code to a query as a parameter, only values. "A" is a value. "'A' OR 'B'" is code.

1. Construct entire query on the fly in code, or
2. Modify the query SQL as required on the fly, look up querydef on this forum
3. Construct an appropriate WHERE clause in your query, using AND and OR, where the VALUE of a parameter determines what happens, eg (MyPAram=1 AND (MyField="A" OR MyField="B" OR MyField="C") OR (MyPAram=2 AND MyField="B") OR (MyPAram=3 AND... All this could be abridged to MyParam=1 AND MyField IN ("A","B","C) OR MyParam=2 AND MyField IN ("B") OR MyParam=3 AND ....
 
... reason being your "expression" is being passed as String and will be interpreted as String. The OR and AND operators will be read as String.
 
Hi,

Thanks a lot for the udpates. I feel that it is the way I pass the string that is giving the issue. I have posted 2 query for explaining my issue.


In Query1 when I input the value "A" Or "B" Or "C", it gives me the output. However if in my code if I put

[Forms]![OpeningForm]![AuditsStatsName]='"A" Or "B" Or "C"'

and then run Query 2, it doesnt gives me the output. Is there anyway I can pass the value as a string and get it executed.


Query1
SELECT DISTINCT [Findings Database].[Report Name], [Findings Database].AuditPlan, [Findings Database].AuditRating
FROM [Findings Database]
WHERE ((([Findings Database].[Report Date])>=[Forms]![OpeningForm]![DateReport-From] And ([Findings Database].[Report Date])<=[Forms]![OpeningForm]![DateReport-To]) AND (([Findings Database].[BU Audited])<>"Branch" And ([Findings Database].[BU Audited])<>"AQR Regrade") AND (([Findings Database].[EN])="A" Or "B" Or "C"));


Query2:

SELECT DISTINCT [Findings Database].[Report Name], [Findings Database].AuditPlan, [Findings Database].AuditRating
FROM [Findings Database]
WHERE ((([Findings Database].[Report Date])>=[Forms]![OpeningForm]![DateReport-From] And ([Findings Database].[Report Date])<=[Forms]![OpeningForm]![DateReport-To]) AND (([Findings Database].[BU Audited])<>"Branch" And ([Findings Database].[BU Audited])<>"AQR Regrade") AND (([Findings Database].[EN])=[Forms]![OpeningForm]![AuditsStatsName]));
 
Is there anyway I can pass the value as a string and get it executed.
NO, there is not.

Threee posts above explain it , but apparently not well enough.

1. You CANNOT pass SQL-language elements in a parameter.
2. You CANNOT pass SQL-language elements in a parameter.
3. And one more time, for clarity: You CANNOT pass SQL-language elements in a parameter.

#2 suggests alternatives, #3 explains again why you cannot do it the way you want, #4 gives you a link that shows how to construct a query in code
 
Code:
Forms!OpeningForm![StatsRDD-Name] = Forms!OpeningForm![StatsRDD]

If Forms!OpeningForm![StatsRDD-Name] = "A and B and C" Then
  Forms!OpeningForm![StatsRDD-Name] = "A' , 'B', 'C"
End If

Forms!OpeningForm![StatsRDD-Name] = "'" & Forms!OpeningForm![StatsRDD-Name] &"'"

Then use the following clause in your query

Code:
AND ([Findings Database].[EN] in "(" & Forms!OpeningForm![StatsRDD-Name] & ")")


ETA:
Changed

Code:
Forms!OpeningForm![StatsRDD-Name] = "'" & Forms!OpeningForm![StatsRDD-Name] &"'1
to

Code:
Forms!OpeningForm![StatsRDD-Name] = "'" & Forms!OpeningForm![StatsRDD-Name] &"'"

Reason: Typo. An iPhone is not best suited to writing VBA. :eek:
 
Last edited:
Then use the following clause in your query

Code:
AND ([Findings Database].[EN] in "(" & Forms!OpeningForm![StatsRDD-Name] & ")")
Are you sure this works nanscombe? IN() is an array and that's what it expects as input. Plus you can't concatenate the parentheses to an array.
 
It's actually building a SQL 'IN' clause "VariableName IN('A', 'B', 'C')" ....

Still can't get it to work in the query though. :mad:


I've used this method before for building dynamic SQL but I just can't get it to work in the query. :mad:
 
That's why we mentioned in the first couple of posts the reasons why it can't be passed in the IN() clause in a query. It just won't work. It has to be built in code, even Eval() won't help either.

I reckon you partially built it in code and passed the SQL to the SQL method of a querydef.
 
The IN() clause does work for a single value (ie A, B, C etc) but doesn't want to play ball for multiple vales.



The closest I've got is

Code:
 AND ((tblProduct.EN=[Forms]![OpeningForm]![StatsRDD-Name])
  OR ("A and B and C"=[Forms]![OpeningForm]![StatsRDD-Name]))
 
Last edited:
Yes I know that. If you write ID IN("1,2,3") it will be interpreted as ID = 123
 

Users who are viewing this thread

Back
Top Bottom