Inserting a function in an SQL query from VBA

[NBVC]

Hi guys,

wonder if someone can help me on this...

I am trying to create an SQL query in VBA to extra data based on various factors.

I am having problems inserting the InStr() function in my WHERE clause, that will compare my table field CUST_ADDRESS.STATE with my entries made in sheet called SUMMARY range Z2:BL2 (where I list the actual states to find).... some of these may be blank and I want it to still work by ignoring blanks in that range...

Can't figure out how to break up the InStr portion here so that it evaluates the function and returns the desired array.

If you need more info, let me know.

Please Login or Register  to view this content.

[NBVC]

I just got it to accept the input into the VB editor as:

Please Login or Register  to view this content.

but still getting an error: Runtime Error 424, Object Required.

This too gave be the above error... tried to isolate the CUST_ADDRESS.State:

Please Login or Register  to view this content.

and this one is giving type mismatch error:

Please Login or Register  to view this content.

[NBVC]

I think, maybe, InStr is not what I need here.. it, I guess works like the Search() function.. i.e. one string to one string.

What I want to do, is like I described.. just instead of saying OR(Customer.state="AZ", Customer.state = "AL", etc... where Customer.state is a column of values... I want to know if the column contains any state in my defined range on my sheet....

I think I should be using In (e.g. Where Customer.State In Range("Z2:BL2") ) but still not getting far....

[snb]

- Worksheets("Summary").Range("Z2:BL2") isn't a string, so instr() won't work.
- the result of instr() is an integer, so in your case it doesn't test on being one of the values in the specified range.

to turn the range into a string:

c00=join(application.transpose(application.transpose(Worksheets("Summary").Range("Z2:BL2"))),"|")

now you can use
instr(CUST_ADDRESS.STATE,c00)>0

althought I can't test it, it would turn out to:

Please Login or Register  to view this content.

sSql = sSql & "AND ((CUSTOMER_ORDER.ORDER_DATE>{ts '2009-12-31 00:00:00'}) AND (CUST_ORDER_LINE.LAST_SHIPPED_DATE Is Null) AND instr( CUST_ADDRESS.STATE,'" & c00 & "')>0) ORDER BY CUSTOMER_ORDER.CUSTOMER_ID "

[NBVC]

That's now giving me a General ODBC error... # 1004.

If I do a Debug.print after your c00=join.... nothing shows up in my immediate window.. but I get that error above.

[snb]

Please Login or Register  to view this content.

should at least show a result equal to string(38,"|") ; unless there is no such worksheet as "Summary" at all.

but maybe the introduction of some apostrophs will do the trick

sSql = sSql & "AND ((CUSTOMER_ORDER.ORDER_DATE>{ts '2009-12-31 00:00:00'}) AND (CUST_ORDER_LINE.LAST_SHIPPED_DATE Is Null) AND instr( CUST_ADDRESS.STATE,' " & chr(34) & c00 & chr(34) & " ')>0) ORDER BY CUSTOMER_ORDER.CUSTOMER_ID "

[NBVC]

Okay think I got with this:

Please Login or Register  to view this content.

seemed to be a missing closing parenthesis before the ORDER BY clause.

I also figured it using the IN clause

Please Login or Register  to view this content.

Thanks very much.