Search results

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...

That would be unusual..Access should default to Text format for the field when creating the table if the data is mixed. If there is a data type mismatch Access usually says it can't import the data and either aborts or deletes the "bad" data from the import. It shouldn't give a #num error...

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...

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

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...

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.

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"

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.

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...

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...

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...

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...

OK...I thought I only needed it done to the problem child Thank you Bob 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...

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...

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...

Yes, it is modified every time it is run based on form selections. The form uses data from 2 combo lists and 12 checkboxes to generate the criteria statements for the query each time it runs. It is for drilling down into large amounts of customer data (several hundred thousand indiivdual...

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...

Do you need to push it to Exce or can you pull it in from Excel? If you can try using the MSQuery tool. you can pull in query results to Excel with it and whenever you nneed new data you just need to select "refresh" in Excel

Yes...i did it to every line.