VBA will create the SQL but it returns no results when Query run (1 Viewer)

[zoiboi]

Hi Everyone

I have a text box on a form that rolls up selections to form a criteria statement for a query.

I tried calling it directly but got a "to complex to evaluate" error so I built the following VBA code to generate the query with a SQL statement.

If no checkboxes are selected the Query runs fine (else portion of the If statement), but when the Then portion of the if statement is run the query builds fine but returns no data. I highlighted the staement referencign the text box in red.

Here is the VBA code:

Private Sub Command42_Click()
Dim db As DAO.Database
Dim qdf As QueryDef
Dim strQuery As String
Dim VarTxt As String

Set db = CurrentDb
Set qdf = db.QueryDefs("Customer_Data_Requested")

If ([Forms]![Data_Selections]![Chk43] = True) Then
Set db = CurrentDb
Set qdf = db.QueryDefs("Customer_Data_Requested")

If Text32.Value > "" Then
VarTxt = Text32.Value
End If


strQuery = "SELECT [Customer Level DATA].[CUSTOMER COID], [Customer Level DATA].[CUSTOMER NUMBER], [Customer Level DATA].[CUSTOMER NAME], " & _
"[Customer Level DATA].[XAA TOTAL FEES], [Customer Level DATA].[WF TOTAL FEES], [Customer Level DATA].[TOTAL FEES DIFFERENCE]" & _
"FROM [Customer Level DATA]" & _
"WHERE ((([Customer Level DATA].[CUSTOMER COID]) Like IIf([Forms]![Data_Selections]![Chk2] = True, [Forms]![Data_Selections]![Combo0], '*'))" & _
"And (([Customer Level DATA].[CUSTOMER NUMBER]) Like IIf([Forms]![Data_Selections]![Chk6] = True, [Forms]![Data_Selections]![Combo4], '*'))" & _
"AND (([Customer Level DATA].[TOTAL FEES DIFFERENCE]) Like IIf([Forms]![Data_Selections]![Chk43] = True, [Forms]![Data_Selections]![text32], '*')))"


Else
strQuery = "SELECT [Customer Level DATA].[CUSTOMER COID], [Customer Level DATA].[CUSTOMER NUMBER], [Customer Level DATA].[CUSTOMER NAME], " & _
"[Customer Level DATA].[XAA TOTAL FEES], [Customer Level DATA].[WF TOTAL FEES], [Customer Level DATA].[TOTAL FEES DIFFERENCE]" & _
"FROM [Customer Level DATA]" & _
"WHERE ((([Customer Level DATA].[CUSTOMER COID]) Like IIf([Forms]![Data_Selections]![Chk2] = True, [Forms]![Data_Selections]![Combo0], '*'))" & _
"And (([Customer Level DATA].[CUSTOMER NUMBER]) Like IIf([Forms]![Data_Selections]![Chk6] = True, [Forms]![Data_Selections]![Combo4], '*')))"


End If


Debug.Print strQuery
qdf.SQL = strQuery




DoCmd.OpenQuery "Customer_Data_Requested"



Set qdf = Nothing
Set db = Nothing

End Sub

I thought maybe the text box was not being read right so I added a variable (VarTxt) so I could see what VBA was saying the value was. When I debugged and it came back correctly.

The textbox builds a Query criteria statement that looks like "<=-1000 Or Between -500 and -999.99 Or Between -250 and -499.99" and has 12 options.

Here is the Test Box control source in case that helps:

=IIf([Chk8]=True,"<=-1000") & IIf([Chk8]=True And [Chk10]=True," Or ") & IIf([Chk10]=True,"Between -500 and -999.99") & IIf([Chk12]=True And ([Chk8]=True Or [Chk10]=True)," Or ") & IIf([Chk12]=True,"Between -250 and -499.99","") & IIf([Chk30]=True And ([Chk8]=True Or [Chk10]=True Or [Chk12]=True)," Or ","") & IIf([Chk30]=True,"Between -100 and -249.99","") & IIf([Chk28]=True And ([Chk8]=True Or [Chk10]=True Or [Chk12]=True Or [Chk30]=True)," Or ","") & IIf([Chk28]=True,"Between -50 and -99.99","") & IIf([Chk26]=True And ([Chk8]=True Or [Chk10]=True Or [Chk12]=True Or [Chk30]=True Or [Chk28]=True)," Or ","") & IIf([Chk26]=True,"Between -25 and -49.99","") & IIf([Chk22]=True And ([Chk8]=True Or [Chk10]=True Or [Chk12]=True Or [Chk30]=True Or [Chk28]=True Or [Chk26]=True)," Or ","") & IIf([Chk22]=True,">=1000","") & IIf([Chk20]=True And ([Chk8]=True Or [Chk10]=True Or [Chk12]=True Or [Chk30]=True Or [Chk28]=True Or [Chk26]=True Or [Chk22]=True)," Or ","") & IIf([Chk20]=True,"Between 500 and 999.99","") & IIf([Chk18]=True And ([Chk8]=True Or [Chk10]=True Or [Chk12]=True Or [Chk30]=True Or [Chk28]=True Or [Chk26]=True Or [Chk22]=True Or [Chk20]=True)," Or ","") & IIf([Chk18]=True,"Between 250 and 499.99","") & IIf([Chk16]=True And ([Chk8]=True Or [Chk10]=True Or [Chk12]=True Or [Chk30]=True Or [Chk28]=True Or [Chk26]=True Or [Chk22]=True Or [Chk20]=True Or [Chk18]=True)," Or ") & IIf([Chk16]=True,"Between 100 and 249.99") & IIf([Chk14]=True And ([Chk8]=True Or [Chk10]=True Or [Chk12]=True Or [Chk30]=True Or [Chk28]=True Or [Chk26]=True Or [Chk22]=True Or [Chk20]=True Or [Chk18]=True Or [Chk16]=True)," Or ") & IIf([Chk14]=True,"Between 50 and 99.99","") & IIf([Chk24]=True And ([Chk8]=True Or [Chk10]=True Or [Chk12]=True Or [Chk30]=True Or [Chk28]=True Or [Chk26]=True Or [Chk22]=True Or [Chk20]=True Or [Chk18]=True Or [Chk16]=True Or [Chk14]=True)," Or ","") & IIf([Chk24]=True,"Between 25 and 49.99")


Does anyone have any idea why the Query with the text box data called will not return data?

Thanks for the help!

 

[spikepl]

Yeah.

When you have a query containing data known in the module you are creating the query in, then there is no point in dragging alt the stuff over into the sql.

So, for example,

sqlSTR= "SELECT * FROM MyTable Where SomeField= '[Forms]![MyForm]![MyTextBox]'"

should be

 sqlSTR= "SELECT * FROM MyTable Where SomeField= '" & [Forms]![MyForm]![MyTextBox] & "'"

so that not your entire construction gets passed on, but the resulting value.

 

[zoiboi]

Thanks for your quick response.

The SQL portion of the code is what Access created from the base query. I only added the one line in red to get the text box data in.

I'm not sure how to go to the format you suggest for the SQL because I need to keep the IIf statements that are embedded.

I'm pretty good with VBA and Access but I don't work on the SQLs often, so could you dummy it up a little for me?

Again, thanks.

 

[spikepl]

What, specifically, is incomprehensible about my example?

You pass the entire lot into the SQL , i.e. all your stuff between the "". If you look at my second strSQL, then the stuff outside of the " " is 'seen' and interpreted, so the actual value is the one that goes into the sql. If you don't get the difference, put the two sqlSTR-statements somewhere in your code, of course adopted to some table and some existing control on your form, so that the code can run, and output and study the strSQL.

 

[spikepl]

If you are not going to use the query elsewhere, but just as shown, then follow my suggestion. If you need to call the query from elsewhere, then yes, you need to keep the full reference to the controls. But I notice that you are missing spaces, and this is bound to throw off the system.

[I][FONT=Arial][COLOR=Red]"[[/COLOR]Customer Level DATA].[XAA TOTAL FEES], [Customer  Level DATA].[WF TOTAL FEES], [Customer Level DATA].[TOTAL FEES  DIFFERENCE]" & _[/FONT][/I]
[I][FONT=Arial][COLOR=Red]"F[/COLOR]ROM [Customer Level DATA]" & _[/FONT][/I]

should be

[I][FONT=Arial][COLOR=Red]" [[/COLOR]Customer Level DATA].[XAA TOTAL FEES], [Customer  Level DATA].[WF TOTAL FEES], [Customer Level DATA].[TOTAL FEES  DIFFERENCE]" & _[/FONT][/I]
[I][FONT=Arial][COLOR=Red]" F[/COLOR]ROM [Customer Level DATA]" & _[/FONT][/I]

and the same applies to the remainder

 

[zoiboi]

Spike

The only difference between the code sets is that you added a space between " and[. Is that what you meant to do?

I tried it just in case and it made no difference in the execution (or lack of execution).

Zoiboi

 

[spikepl]

YEs that is the only differene. Did you do it for all the lines? Because if you output your SQL, IN ONE LINE , and those blanks are missing, then there is no separation between SQL keywords, data and what have you, and that beats the system.

 

[zoiboi]

Yes...i did it to every line.

 

[spikepl]

You have not answered: do you only use the query as shown? If so, then follow #2 and #4

 

[boblarson]

Yes, the space is necessary.

And you also have to change all of your double quotes on the values you want for the IIF statements to single quotes.

And third -

Instead of this:

"AND (([Customer Level DATA].[TOTAL FEES DIFFERENCE]) Like IIf([Forms]![Data_Selections]![Chk43] = True, [Forms]![Data_Selections]![text32], '*')))"

Use this instead

[B][COLOR=red]Dim strTotalFeesDiff As String[/COLOR][/B]
[B][COLOR=red] [/COLOR][/B]
[B][COLOR=red]If Forms!Data_Selections!Chk43 = True Then[/COLOR][/B]
[B][COLOR=red]    strTotalFeesDiff = “AND (([Customer Level DATA].[TOTAL FEES DIFFERENCE] Like ‘*’" & Forms!Data_Selections!text32 & "‘*’)))" [/COLOR][/B]
[B][COLOR=red]End If[/COLOR][/B]
[B][COLOR=red] [/COLOR][/B]
[B][COLOR=red]& strTotalFeesDiff & [/COLOR][/B]

Then you only have to account for a value if it exists and if the field isn't being used then it isn't going to be used in the query's where clause.

 

[zoiboi]

Spike

I sent the whole VB & Sql code. I run it 2 ways....if text box 32 is empty it goes to the else SQL and that runs fine.

It is exactly the same as the SQL if text box 32 is selected EXCEPT this SQL also has "AND (([Customer Level DATA].[TOTAL FEES DIFFERENCE]) Like IIf([Forms]![Data_Selections]![Chk43] = True, [Forms]![Data_Selections]![text32], '*')))" added to it.

They replace each other correctly depending on the contents in text box 32.

#4 I understood, but #2 is not detailed enough for my limited knowledge to know how to change to.

I'm a VBA guy, not a SQL guy

 

[spikepl]

Bob has been kind enough to provide an illustrative example of the point I was making - study it. The issue is simple string manipulation and redundancy. There is no point whatsoever in sending a reference to a control to SQL when you can just as well send the value itself.

strSQL = "SELECT * FROM MyTable WHERE SomeField= Forms![MyForm]![MyValue]"
is what you have been sending. That is, the SQL has to look up the value of Forms![MyForm]![MyValue], but that is pintless, becasue you KNOW what the value is.

strSQL = "SELECT * FROM MyTable WHERE SomeField= '" & Forms![MyForm]![MyValue] & "'"

would result in

strSQL = "SELECT * FROM MyTable WHERE SomeField= 'TheContentOfMyControl' " being in the query, so the value is ported directly.

All the above works, as long as you query will not be called from elsewhere. In the resulting sql-string, values of string variables have to be enclosed by '' (single quotes), numbers don't.

 

[zoiboi]

OK Bob, I implimented your solution but I'm getting a runtime error saying there is a missing operator.

When I did that I took the “AND (([Customer Level DATA].[TOTAL FEES DIFFERENCE] Like ‘*’" & Forms!Data_Selections!text32 & "‘*’)))" and just used it as the last line in the SQL and got the same error.

Is there a missing " maybe?

 

[spikepl]

probably a missing space at the beginning. output your sqlstring and check.

If you code VBA, then you know that there have to be spaces for the compiler to recognize what you are writing. SQL is no different in this respect.

 

[zoiboi]

OK....I actually saw that and put the space in when it crashed the first time.

I'm wondering if the issue is the location of
& strTotalFeesDiff &
I have tried it right after the if statement like you sent which gave a syntax error (which I expected because it looked incomplete)
I tried it after the SQL statement, attached as part of the SQL, moved it to the strQuery = strQuery & strTotalFeesDiff statement and tried it like this too
qdf.SQL = strQuery & strTotalFeesDiff
The error is hitting the qdf.SQL line now but with the same missing operator. With the 2nd "&" it asks for another argument in a syntax error.

 

[boblarson]

OK....I actually saw that and put the space in when it crashed the first time.

I'm wondering if the issue is the location of
& strTotalFeesDiff &
I have tried it right after the if statement like you sent which gave a syntax error (which I expected because it looked incomplete)
I tried it after the SQL statement, attached as part of the SQL, moved it to the strQuery = strQuery & strTotalFeesDiff statement and tried it like this too
qdf.SQL = strQuery & strTotalFeesDiff
The error is hitting the qdf.SQL line now but with the same missing operator. With the 2nd "&" it asks for another argument in a syntax error.

The best thing is going to be to have you post what you now currently have so we can spot any additional problems or implementation errors with what we've suggested.

 

[zoiboi]

Thank you guys again

Here is the current code.



Private Sub Command42_Click()
Dim db As DAO.Database
Dim qdf As QueryDef
Dim strQuery As String
Dim VarTxt As String
Dim strTotalFeesDiff As String

Set db = CurrentDb
Set qdf = db.QueryDefs("Customer_Data_Requested")


If Forms!Data_Selections!Chk43 = True Then
strTotalFeesDiff = " AND (([Customer Level DATA].[TOTAL FEES DIFFERENCE] Like '*'" & Forms!Data_Selections!Text32 & "'*')))"
End If
'& strTotalFeesDiff &

If ([Forms]![Data_Selections]![Chk43] = True) Then
Set db = CurrentDb
Set qdf = db.QueryDefs("Customer_Data_Requested")

If Text32.Value > "" Then
VarTxt = Text32.Value
End If



strQuery = " SELECT [Customer Level DATA].[CUSTOMER COID], [Customer Level DATA].[CUSTOMER NUMBER], [Customer Level DATA].[CUSTOMER NAME], " & _
" [Customer Level DATA].[XAA TOTAL FEES], [Customer Level DATA].[WF TOTAL FEES], [Customer Level DATA].[TOTAL FEES DIFFERENCE]" & _
" FROM [Customer Level DATA]" & _
" WHERE ((([Customer Level DATA].[CUSTOMER COID]) Like IIf([Forms]![Data_Selections]![Chk2] = True, [Forms]![Data_Selections]![Combo0], '*'))" & _
" And (([Customer Level DATA].[CUSTOMER NUMBER]) Like IIf([Forms]![Data_Selections]![Chk6] = True, [Forms]![Data_Selections]![Combo4], '*'))"


' &strTotalFeesDiff&


'strQuery = strQuery & strTotalFeesDiff


'strQuery = strQuery & " AND (([Customer Level DATA].[TOTAL FEES DIFFERENCE]) Like IIf([Forms]![Data_Selections]![Chk43] = True, [Forms]![Data_Selections]![text32], " * ")"
Else
strQuery = "SELECT [Customer Level DATA].[CUSTOMER COID], [Customer Level DATA].[CUSTOMER NUMBER], [Customer Level DATA].[CUSTOMER NAME], " & _
"[Customer Level DATA].[XAA TOTAL FEES], [Customer Level DATA].[WF TOTAL FEES], [Customer Level DATA].[TOTAL FEES DIFFERENCE]" & _
"FROM [Customer Level DATA]" & _
"WHERE ((([Customer Level DATA].[CUSTOMER COID]) Like IIf([Forms]![Data_Selections]![Chk2] = True, [Forms]![Data_Selections]![Combo0], '*'))" & _
"And (([Customer Level DATA].[CUSTOMER NUMBER]) Like IIf([Forms]![Data_Selections]![Chk6] = True, [Forms]![Data_Selections]![Combo4], '*')))"


End If


'Debug.Print strQuery
qdf.SQL = strQuery & strTotalFeesDiff




DoCmd.OpenQuery "Customer_Data_Requested"



Set qdf = Nothing
Set db = Nothing

End Sub

 

[boblarson]

strQuery = " SELECT [Customer Level DATA].[CUSTOMER COID], [Customer Level DATA].[CUSTOMER NUMBER], [Customer Level DATA].[CUSTOMER NAME], " & _
" [Customer Level DATA].[XAA TOTAL FEES], [Customer Level DATA].[WF TOTAL FEES], [Customer Level DATA].[TOTAL FEES DIFFERENCE]" & _
" FROM [Customer Level DATA]" & _
" WHERE ((([Customer Level DATA].[CUSTOMER COID]) Like IIf([Forms]![Data_Selections]![Chk2] = True, [Forms]![Data_Selections]![Combo0], '*'))" & _
" And (([Customer Level DATA].[CUSTOMER NUMBER]) Like IIf([Forms]![Data_Selections]![Chk6] = True, [Forms]![Data_Selections]![Combo4], '*')[B][COLOR=red]) "[/COLOR][/B] [B][COLOR=red]& strTotalFeesDiff

[/COLOR][/B]
But you need to do the same for each of the IIF's that are in the WHERE clause. You've only got this one but Customer Number and CustomerCOID needs to be done the same way as this one.

 

[zoiboi]

OK...I thought I only needed it done to the problem child

Thank you Bob

Zoiboi

 

[zoiboi]

Bob

Here is the code now that I made the changes.

It is still giving this error:
"Run-time error '3075': Syntax Error (missing operator) in query expression '((([Customer Level data].[Customer Coid] Like'*''*')) and (([Customer Level data].[Customer Number] Like'*''*')) and ))[Customer Level data].[Total Fees Difference] like'*'<=-1000'*')))'.

It come up when the purple hilighted command runs.

The data from the locals window is at the bottom and it looks fropm there like it isn't appending the strTotalFeesDiff, strCUSTOMERCOID & strCUSTOMERNUMBER to the SQL.



Private Sub Command42_Click()
Dim db As DAO.Database
Dim qdf As QueryDef
Dim strQuery As String
Dim VarTxt As String
Dim strTotalFeesDiff As String
Dim strCUSTOMERCOID As String
Dim strCUSTOMERNUMBER As String


Set db = CurrentDb
Set qdf = db.QueryDefs("Customer_Data_Requested")


If Forms!Data_Selections!Chk43 = True Then
strTotalFeesDiff = " AND (([Customer Level DATA].[TOTAL FEES DIFFERENCE] Like '*'" & Forms!Data_Selections!Text32 & "'*')))"
End If
strCUSTOMERCOID = "((([Customer Level DATA].[CUSTOMER COID] Like '*'" & [Forms]![Data_Selections]![Combo0] & "'*'))"
strCUSTOMERNUMBER = " And (([Customer Level DATA].[CUSTOMER NUMBER]) Like '*'" & [Forms]![Data_Selections]![Combo4] & "'*'))"

If ([Forms]![Data_Selections]![Chk43] = True) Then
Set db = CurrentDb
Set qdf = db.QueryDefs("Customer_Data_Requested")

If Text32.Value > "" Then
VarTxt = Text32.Value
End If



strQuery = " SELECT [Customer Level DATA].[CUSTOMER COID], [Customer Level DATA].[CUSTOMER NUMBER], [Customer Level DATA].[CUSTOMER NAME], " & _
" [Customer Level DATA].[XAA TOTAL FEES], [Customer Level DATA].[WF TOTAL FEES], [Customer Level DATA].[TOTAL FEES DIFFERENCE]" & _
" FROM [Customer Level DATA]" & _
" WHERE " & strCUSTOMERCOID & strCUSTOMERNUMBER & strTotalFeesDiff



Else
strQuery = "SELECT [Customer Level DATA].[CUSTOMER COID], [Customer Level DATA].[CUSTOMER NUMBER], [Customer Level DATA].[CUSTOMER NAME], " & _
"[Customer Level DATA].[XAA TOTAL FEES], [Customer Level DATA].[WF TOTAL FEES], [Customer Level DATA].[TOTAL FEES DIFFERENCE]" & _
"FROM [Customer Level DATA]" & _
"WHERE ((([Customer Level DATA].[CUSTOMER COID]) Like IIf([Forms]![Data_Selections]![Chk2] = True, [Forms]![Data_Selections]![Combo0], '*'))" & _
"And (([Customer Level DATA].[CUSTOMER NUMBER]) Like IIf([Forms]![Data_Selections]![Chk6] = True, [Forms]![Data_Selections]![Combo4], '*')))"


End If


qdf.SQL = strQuery




DoCmd.OpenQuery "Customer_Data_Requested"



Set qdf = Nothing
Set db = Nothing

End Sub



Here is the data from the locals window when the code crashed.



: strQuery : " SELECT [Customer Level DATA].[CUSTOMER COID], [Customer Level DATA].[CUSTOMER NUMBER], [Customer Level DATA].[CUSTOMER NAME], [Customer Level DATA].[XAA TOTAL FEES], [Customer Level DATA].[WF TOTAL FEES], [Customer Level DATA].[TOTAL FEES DIFFERENCE] ": String

: strTotalFeesDiff : " AND (([Customer Level DATA].[TOTAL FEES DIFFERENCE] Like '*'<=-1000'*')))" : String

: strCUSTOMERCOID : "((([Customer Level DATA].[CUSTOMER COID] Like '*''*'))" : String

: strCUSTOMERNUMBER : " And (([Customer Level DATA].[CUSTOMER NUMBER]) Like '*''*'))" : String