WHERE clause

[JT]

I'm trying to use a sql statement to select the Dept and Branch from a table
for a specific customer number and where the groups does not start with 000.
I have tried writing the statement as follows:

vSQL = "SELECT [DEPT],[BR] from [Data] WHERE [CUST_NO] = '" & CSTMR & "'AND
MID([GROUP],1,3)<>""000"")"

However, it is still pulling in all of the groups even those that start with
000.

Can someone tell me what is wrong with this statement. Thank You very much!

 

[Steve McLeod]

vSQL = "SELECT [DEPT], [BR] from [Data] WHERE [CUST_NO] = " &
Trim(Cstr(Nz(CSTMR, 0))) & " AND Left([GROUP],3) <> " & chr(34) & "000" &
chr(34) & ")"

 

[Dirk Goldgar]

In

I'm trying to use a sql statement to select the Dept and Branch from
a table for a specific customer number and where the groups does not
start with 000. I have tried writing the statement as follows:

vSQL = "SELECT [DEPT],[BR] from [Data] WHERE [CUST_NO] = '" & CSTMR &
"'AND MID([GROUP],1,3)<>""000"")"

However, it is still pulling in all of the groups even those that
start with 000.

Can someone tell me what is wrong with this statement. Thank You
very much!

I see a couple of things wrong there: (1) you need a space before the
keyword "AND", and (2) you have an unmatched closing parenthesis. I'm
not convinced you're ever actually executing this statement, because it
ought to give you a syntax error, not just return all groups. Check to

Try this and see if it works:

vSQL = _
"SELECT [DEPT],[BR] from [Data] " & _
"WHERE [CUST_NO] = '" & CSTMR & _
"' AND [GROUP] Not Like '000*'"

If that doesn't work, check to make sure that's actually the statement
being executed.