General ODBC error

kiwidancer · January 12th, 2010, 04:12 PM

the debugger is coming up at the .Refresh BackgroundQuery:=False line which generally means that something is wong with my sql syntax. However, I know this is not the case.
The macro works fine for almost all cases except the most simple.
I have the user select salespeople, categories, and a date range. If they select all users and all categories, the only thing that goes into the sql statement is the date range so the sql statement ends up being:

Code:

SELECT * FROM MonthInvoices WHERE (`Invoice Date`>=20091201 And `Invoice Date`<=20100112)

this statement generates the general ODBC error, while one like

Code:

SELECT * FROM MonthInvoices WHERE (`Invoice Date`>=20091201 And `Invoice Date`<=20100112) AND (Category='BANNER')

works just fine or even something much more complicated like

Code:

SELECT * FROM MonthInvoices WHERE (`Invoice Date`>=20091201 And `Invoice Date`<=20100112) AND (Trim(Salesperson)='CC      ') AND (Trim(Category)='ADJP  ') OR (`Invoice Date`>=20091201 And `Invoice Date`<=20100112) AND (Trim(Salesperson)='CC      ') AND (Trim(Category)='BANNER') OR (`Invoice Date`>=20091201 And `Invoice Date`<=20100112) AND (Trim(Salesperson)='CC      ') AND (Trim(Category)='BOND  ') OR (`Invoice Date`>=20091201 And `Invoice Date`<=20100112) AND (Trim(Salesperson)='DP      ') AND (Trim(Category)='ADJP  ') OR (`Invoice Date`>=20091201 And `Invoice Date`<=20100112) AND (Trim(Salesperson)='DP      ') AND (Trim(Category)='BANNER') OR (`Invoice Date`>=20091201 And `Invoice Date`<=20100112) AND (Trim(Salesperson)='DP      ') AND (Trim(Category)='BOND  ') OR (`Invoice Date`>=20091201 And `Invoice Date`<=20100112) AND (Trim(Salesperson)='FRU     ') AND (Trim(Category)='ADJP  ') OR (`Invoice Date`>=20091201 And `Invoice Date`<=20100112) AND (Trim(Salesperson)='FRU     ') AND (Trim(Category)='BANNER') OR (`Invoice Date`>=20091201 And `Invoice Date`<=20100112) AND (Trim(Salesperson)='FRU     ') AND (Trim(Category)='BOND  ')

works just as well

any idea why i might be getting this error? thank you in advance for your help

kiwidancer · January 13th, 2010, 01:18 PM

Well I figured out the problem.... now I just need to know how to fix it.

The problem: The program is erroring when it's asked to return lines where the value of a certain column is 0. I can make it work by editing my sql statement to omit those lines, but then my report is wrong. The same query works fine in access and ms query; it is only when it tries to write the lines to exel that the problem comes up. Is there some way I can work around this?

thank you!