- Joined
- Jan 12, 2010
- Messages
- 2
- Reaction score
- 0
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
is there a limit to the # of lines a query can return or something? this macro was working for the most part a few months ago when i first started writing it, but i went in to finish today and all of a sudden had this error.
any idea why i might be getting this error? thank you in advance for your help
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
is there a limit to the # of lines a query can return or something? this macro was working for the most part a few months ago when i first started writing it, but i went in to finish today and all of a sudden had this error.
any idea why i might be getting this error? thank you in advance for your help