VBA will create the SQL but it returns no results when Query run

[boblarson]

Well, for one you didn't study what I did too closely did you? I'll post in a bit my version of your code but I need to know what datatypes COID and CUSTOMER NUMBER are and do you plan on typing in the <= part in the total fees difference? It is fine but it requires different handling.

Also, what is the reasoning behind this:

    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

And I really suggest that you rename your controls to something more meaningful than Chk43, chk6, chk2 so it will help you later and anyone that follows on so they don't have to go look to see what it is.

 

[boblarson]

Okay, if the answers to my above questions are:

1. Yes, we'll be typing in the operator and value (example: <=1000)
2. The two fields are Numeric.

Then the code here should work:

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] " & Forms!Data_Selections!Text32 & ")))"
    End If
 
[B][COLOR=teal]' when I said you needed to deal with the other two, you needed to deal with them this way[/COLOR][/B]
[B][COLOR=red]    If Forms!Data_Selections!Chk2 = True Then
        strCUSTOMERCOID = " AND (([Customer Level DATA].[CUSTOMER COID] = " & [Forms]![Data_Selections]![Combo0] & ")))"
    End If[/COLOR][/B]
[B][COLOR=#ff0000][/COLOR][/B] 
[B][COLOR=red]    If Forms!Data_Selections!Chk6 = True Then
        strCUSTOMERNUMBER = " AND (([Customer Level DATA].[CUSTOMER NUMBER] = " & Forms!Data_Selections!Text32 & ")))"
    End If[/COLOR][/B]
 
    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], " & _
                   [B][COLOR=red]" [[/COLOR][/B]Customer Level DATA].[XAA TOTAL FEES], [Customer Level DATA].[WF TOTAL FEES], [Customer Level DATA].[TOTAL FEES DIFFERENCE]" & _
                   [B][COLOR=red]" F[/COLOR][/B]ROM [Customer Level DATA]" & _
                   [COLOR=red][B]" W[/B][/COLOR]HERE " & 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]" [B][COLOR=red]& strCUSTOMERCOID & strCUSTOMERNUMBER[/COLOR][/B]

    End If

    qdf.sql = strQuery

    qdf.Close
    DoCmd.OpenQuery "Customer_Data_Requested"
 
    Set qdf = Nothing
    Set db = Nothing
End Sub

As shown by the " [ (where I added spaces) you had forgotten to fix the space issue like we had already said as well.

 

[zoiboi]

I thought I had studied it but my brain is getting pretty toasted so my focus may not be tip top.

Cust number & COID are both numbers, but Accounts could possibly have leading zeros.

There are 2 criteria, <=-1000 & >=1000, that have leading mathmatical comparisons and they are necessary. They are produced in the textbox by the the equation in the Control Source for the textbox.

If all the options are checked this is the total statement for the criteria:

<=-1000 Or Between -500 and -999.99 Or Between -250 and -499.99 Or Between -100 and -249.99 Or Between -50 and -99.99 Or Between -25 and -49.99 Or >=1000 Or Between 500 and 999.99 Or Between 250 and 499.99 Or Between 100 and 249.99 Or Between 50 and 99.99 Or Between 25 and 49.99

Notice there is a gap between -25 and 25. Those are considered in spec and not reviewed.

FYI: I added if statements for the COID & Cust Number so they are like the Total Fees Difference if stmt.

 

[boblarson]

Does my last code not work (copy it and paste as is over your current code)? Also just a note - if you have a combo box, don't use LIKE as the operator unless it only pulls part of something. If it has the entire value, there is no need for like, it can be used with equals.

Also, you can't use like when manually entering the operators. There is no reason for it. You can't have something like <1000 it is either less than 1000, equal to 1000, or greater than 1000 but it can't be LIKE less than 1000.

 

[zoiboi]

Bob

I posted before your last email got here so we crossed in the mail.

The if statements did not fixed the run time errors if any COID, Acct Number or Variance range is selected.

I did copy your code (the Acct number was referencing the text box, not the combo but I fixed that).

The 2 combo boxes are sourced from 2 queriees. The account number auto updates based on the selection in the COID field.

 

[zoiboi]

Bob & Spike

I have continued to try to get this to work and I think I am getting close.

I went to this statement " AND (([Customer Level DATA].[TOTAL FEES DIFFERENCE]) = '" & VarTxt & "'))".

It is sending the correct criteria text over but it is sending it in this form '<=-1000 Or Between -500 and -999.99'. The problem are those leading and following 's.

If those are removed the query runs correctly.

Do you know of a way to change the statement to remove those?

Thanks

Zoiboi

 

[boblarson]

Bob & Spike

I have continued to try to get this to work and I think I am getting close.

I went to this statement " AND (([Customer Level DATA].[TOTAL FEES DIFFERENCE]) = '" & VarTxt & "'))".

It is sending the correct criteria text over but it is sending it in this form '<=-1000 Or Between -500 and -999.99'. The problem are those leading and following 's.

If those are removed the query runs correctly.

Do you know of a way to change the statement to remove those?

Thanks

Zoiboi

Remove the quotes in red:

" AND (([Customer Level DATA].[TOTAL FEES DIFFERENCE]) = '" & VarTxt & "'))".

 

[boblarson]

But also remove the equals sign so it looks like this:

" AND (([Customer Level DATA].[TOTAL FEES DIFFERENCE]) " & VarTxt & "))"

 

[zoiboi]

Thanks for trying.

This thing is so frustrating....now it is giving a syntax error in the sql identified as "Missing Operator".

Oh...as an FYI I think I figured out what was happening Friday. According to the watch window it wasn't passing the result to the variables but the full statement.

 

[boblarson]

Thanks for trying.

This thing is so frustrating....now it is giving a syntax error in the sql identified as "Missing Operator".

Oh...as an FYI I think I figured out what was happening Friday. According to the watch window it wasn't passing the result to the variables but the full statement.

If you modified the code like I had said then you would have needed to ALWAYS include the operator ( = , > , < , <= , >=, or <>) with the numbers. It will not work without them. The field [Total Fees Difference] NEEDS to be numeric or this all will not work as it will be looking for a string for each value.

 

[zoiboi]

Unfortunately most of the comparisons are "Between". Only the 2 endpoints <=-1000 & >=1000 use the mathmatical operators....the other 10 are "Between X and Y"

 

[boblarson]

Unfortunately most of the comparisons are "Between". Only the 2 endpoints <=-1000 & >=1000 use the mathmatical operators....the other 10 are "Between X and Y"

What would X and Y represent? are they numbers? If so, it should work still because we are concatenating in the text as part of the criteria.

 

[zoiboi]

yes, X & Y are numbers.

I'm rewritting the control source to get rid of the "betweens" and use one numeric operators. My Control Source equation is so long that if it increases in lenght I won't be able to get it in the field so I'm a little worried doing this.

 

[zoiboi]

I have changed all the "betweens" to mathmatical comparisons.

I went back to your suggestion Friday and I still get a syntax error Missing Operator and it flags " AND (([Customer Level DATA].[TOTAL FEES DIFFERENCE] " & Forms!Data_Selections!Text32 & ")))" as the area for the missing operator.

If all boxes are selected this is the contents of the text box: <=-1000 Or <= -500 and >=-999.99 Or <= -250 and >=-499.99 Or <= -100 and >=-249.99 Or Between <=-50 and >=-99.99 Or Between <=-25 and >=-49.99 Or >=1000 Or >= 500 and <=999.99 Or >= 250 and <=499.99 Or >=100 and <=249.99 Or >= 50 and <=99.99 Or Between >=25 and <=49.99

 

[boblarson]

Well, I believe this is going to be a problem for you. You can't use that type of syntax. I do not know how you are going to accomplish this using the form that you have and the checkboxes.

 

[zoiboi]

Thanks Bob

I guess I need to tryto figure another method to make it user friendly.

 

[Beetle]

IMHO you should seriously reconsider your design here. You've got a form with 25-30 check boxes (from what I can tell) and a text box with one of the most convoluted nested IIf statements I've ever seen as it's Control Source.

What happens if more conditions are required in the future? You'll have to redesign your form with even more check boxes and rewrite that IIf statement. Looking at your actual criteria as returned by the text box, how is this;

<=-1000 Or <= -500 and >=-999.99 Or <= -250 and >=-499.99 Or <= -100 and >=-249.99 Or Between <=-50 and >=-99.99 Or Between <=-25 and >=-49.99 Or >=1000 Or >= 500 and <=999.99 Or >= 250 and <=499.99 Or >=100 and <=249.99 Or >= 50 and <=99.99 Or Between >=25 and <=49.99

any different than;

<=-25 OR >=25

unless you have values with three or more decimal places, like 49.99356 for example, that could actually fall between 49.99 and 50 and would therefore be outside the range. If not, then it seems you are making this way more complicated than it needs to be.

 

[zoiboi]

There are 15 checkboxes. 3 activate criteria selectors.

The text you refered to represent all possible selections. The user can pick any 1 of 12 or combination of the 12. Ie "<=-1000 or >= 1000" or "<=-250 or >=-499.99 or >=100 or <=250".

All the numbers are dollar values so they are to 2 decimal places max.

 

[zoiboi]

Bob

The issue with the script has been found. The issue was how the textbox was read by the SQL.

It was reading the text as labels like this:

[Account Level DATA].[ELEMENT DIFFERENCE TOTAL CHARGE] >=50 And <=99.99

instead of like this (the correct way):

[Account Level DATA].[ELEMENT DIFFERENCE TOTAL CHARGE] >=50 And [Account Level DATA].[ELEMENT DIFFERENCE TOTAL CHARGE] <=99.99

That is also why the "<=-1000" & ">=1000" worked...the SQL statements they produced were correct when the rest were still wrong due to the extra criteria.

One of the SQL programmers was in the dept for a meeting and I cornered him. He spotted it right off the bat.