VBA LIKE SYNTAX ISSUE

creativefusion
creativefusion used Ask the Experts™
on
All,

I am having an issue trying to attach the following SQL to a function using vba.

What I am having a problem with is the Like Function. (Not Like "*CLOSED*"). I have tried doing this in steps, step a and executing in step b, but access still errors on me.

Can someone please help me sort this out?

INSERT INTO MT_LOCATION_SUPPLIED ( LOC_NO, DESCRIPTION, STATE_CODE, LAST_UPDATE_DTS, LastUpdateUserID )
SELECT WW_LOCATION.LOC_NO, WW_LOCATION.LOC_NAME, WW_LOCATION.STATE_CODE, Date() AS UpdateDTS, "Admin" AS UserID
FROM WW_LOCATION
GROUP BY WW_LOCATION.LOC_NO, WW_LOCATION.LOC_NAME, WW_LOCATION.STATE_CODE, WW_LOCATION.LOC_TYPE, WW_LOCATION.RANGED_IND
HAVING (((WW_LOCATION.LOC_NAME) Not Like "*CLOSED*") AND ((WW_LOCATION.LOC_TYPE)="WH") AND ((WW_LOCATION.RANGED_IND)='Y'));

CF
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2013

Commented:
Your SELECT statement does not use any aggregate functions like SUM, AVG,etc... so you should be using WHERE and ORDER BY instead of HAVING and GROUP BY.

Try this:

INSERT INTO MT_LOCATION_SUPPLIED ( LOC_NO, DESCRIPTION, STATE_CODE, LAST_UPDATE_DTS, LastUpdateUserID )
SELECT WW_LOCATION.LOC_NO, WW_LOCATION.LOC_NAME, WW_LOCATION.STATE_CODE, Date() AS UpdateDTS, "Admin" AS UserID
FROM WW_LOCATION
WHERE (WW_LOCATION.LOC_NAME Not Like "*CLOSED*") AND WW_LOCATION.LOC_TYPE="WH" AND WW_LOCATION.RANGED_IND='Y'
ORDER BY WW_LOCATION.LOC_NO, WW_LOCATION.LOC_NAME, WW_LOCATION.STATE_CODE, WW_LOCATION.LOC_TYPE, WW_LOCATION.RANGED_IND

Open in new window

Author

Commented:
Thanks. I changed it but the IDE keeps changing the format of the Like Clause. I think it has something to do witht the quotes, maybe it thinks it is a string. Correct? As follows:

strSQL = "INSERT INTO MT_LOCATION_SUPPLIED ( LOC_NO, DESCRIPTION, STATE_CODE, LAST_UPDATE_DTS, LastUpdateUserID ) " & _
    "SELECT WW_LOCATION.LOC_NO, WW_LOCATION.LOC_NAME, WW_LOCATION.STATE_CODE, Date() AS UpdateDTS, 'Admin' AS UserID " & _
    "FROM WW_LOCATION WHERE (WW_LOCATION.LOC_NAME Not Like " * CLOSED * ") AND WW_LOCATION.LOC_TYPE='WH' AND WW_LOCATION.RANGED_IND='Y' " & _
    "ORDER BY WW_LOCATION.LOC_NO, WW_LOCATION.LOC_NAME, WW_LOCATION.STATE_CODE, WW_LOCATION.LOC_TYPE, WW_LOCATION.RANGED_IND"
Most Valuable Expert 2012
Top Expert 2013

Commented:
Oh!  If you are embedding it in VBA you have to write it a little differently.

You need to delimit * Closed *  with single quotes, since Double quotes would be seen as a premature end to your SQL string:

strSQL = "INSERT INTO MT_LOCATION_SUPPLIED ( LOC_NO, DESCRIPTION, STATE_CODE, LAST_UPDATE_DTS, LastUpdateUserID ) " & _
    "SELECT WW_LOCATION.LOC_NO, WW_LOCATION.LOC_NAME, WW_LOCATION.STATE_CODE, Date() AS UpdateDTS, 'Admin' AS UserID " & _
    "FROM WW_LOCATION WHERE (WW_LOCATION.LOC_NAME Not Like  '* CLOSED *' ) AND WW_LOCATION.LOC_TYPE='WH' AND WW_LOCATION.RANGED_IND='Y' " & _
    "ORDER BY WW_LOCATION.LOC_NO, WW_LOCATION.LOC_NAME, WW_LOCATION.STATE_CODE, WW_LOCATION.LOC_TYPE, WW_LOCATION.RANGED_IND"

Open in new window

Since *CLOSED* is part of the criteria of your SQL string it should be within single quotes.

'*CLOSED*'
Most Valuable Expert 2012
Top Expert 2013

Commented:
In the SQL I posted, I copy pasted the spaces that were around Closed in your last post.  If you do not want those spaces included, remove them so that the asterisks are immediately adjacent to the word *closed*.

Author

Commented:
Sorry guys but even after following the above, I still have the same issue.

The records are including locations that have CLOSED in the Name.

CLOSED - MOOREBANK DC X

Here is the SQL as at now:

 strSQL = "INSERT INTO MT_LOCATION_SUPPLIED ( LOC_NO, DESCRIPTION, STATE_CODE, LAST_UPDATE_DTS, LastUpdateUserID ) " & _
    "SELECT WW_LOCATION.LOC_NO, WW_LOCATION.LOC_NAME, WW_LOCATION.STATE_CODE, Date() AS UpdateDTS, 'Admin' AS UserID " & _
    "FROM WW_LOCATION WHERE (WW_LOCATION.LOC_NAME Not Like  '*CLOSED*' ) AND WW_LOCATION.LOC_TYPE='WH' AND WW_LOCATION.RANGED_IND='Y' " & _
    "ORDER BY WW_LOCATION.LOC_NO, WW_LOCATION.LOC_NAME, WW_LOCATION.STATE_CODE, WW_LOCATION.LOC_TYPE, WW_LOCATION.RANGED_IND"
    Conn.Execute strSQL, dbFailOnError

CF
Most Valuable Expert 2012
Top Expert 2013

Commented:
Hazarding a guess,  those records don't meet your criteria.

As a test try removing the other two criteria and see whether you get any records containing closed.
Most Valuable Expert 2012
Top Expert 2013

Commented:
In other words run a test using only the location criteria.

Author

Commented:
Champ, when I run the SQL in the access query window, its runs perfectly fine but as soon as we throw it into VB, it craps out.

Author

Commented:
Is there anything else we can do as a simple work around?
Most Valuable Expert 2012
Top Expert 2013

Commented:
Hmm.  I'm not seeing anything obviously wrong with it, but let's try simplifying it.   This is my last stab at it before calling it a night:


 strSQL = "INSERT INTO MT_LOCATION_SUPPLIED ( LOC_NO, DESCRIPTION, STATE_CODE, LAST_UPDATE_DTS, LastUpdateUserID ) " & _
    "SELECT LOC_NO, LOC_NAME, STATE_CODE, Date(), 'Admin' " & _
    "FROM WW_LOCATION WHERE (LOC_NAME NOT  LIKE '*CLOSED*' ) AND LOC_TYPE='WH' AND RANGED_IND='Y' " & _
    "ORDER BY LOC_NO, LOC_NAME, STATE_CODE, LOC_TYPE, RANGED_IND"
debug.print strSQL
    Conn.Execute strSQL, dbFailOnError

Open in new window


I added a debug.print statement.  If the SQL doesnt work, copy/paste the SQL string that gets shown in the immediate window.
Most Valuable Expert 2012
Top Expert 2013

Commented:
<<when I run the SQL in the access query window, its runs perfectly fine>>

If as you say, the SQL I've suggested works from the Query Designer, then it is just a matter of getting it into VBA correctly - we are almost there, I think if we are not there already.
Most Valuable Expert 2012
Top Expert 2013

Commented:
Here's one more variation, breaking things down a little:
dim strSQL as string
 Dim strSelect as string
 Dim strWhere as string
 dim strOrder as string

 strSQL = "INSERT INTO MT_LOCATION_SUPPLIED ( LOC_NO, DESCRIPTION, STATE_CODE, LAST_UPDATE_DTS, LastUpdateUserID )  " 
 strSelect  =  "SELECT LOC_NO, LOC_NAME, STATE_CODE, Date(), 'Admin' FROM WW_LOCATION "
 strWhere = "WHERE (LOC_NAME NOT  LIKE '*CLOSED*' ) AND LOC_TYPE='WH' AND RANGED_IND='Y' "
strOrder = "ORDER BY LOC_NO, LOC_NAME, STATE_CODE, LOC_TYPE, RANGED_IND"

strSQL = strSQL & strSelect  & strWhere & strOrder 
debug.print strSQL
    Conn.Execute strSQL, dbFailOnError
                                            

Open in new window

Most Valuable Expert 2012
Top Expert 2013

Commented:
Also, you are including a couple of fields in the Order By that are not included in the SELECT.  You should drop those fields:

dim strSQL as string
 Dim strSelect as string
 Dim strWhere as string
 dim strOrder as string

 strSQL = "INSERT INTO MT_LOCATION_SUPPLIED ( LOC_NO, DESCRIPTION, STATE_CODE, LAST_UPDATE_DTS, LastUpdateUserID )  " 
 strSelect  =  "SELECT LOC_NO, LOC_NAME, STATE_CODE, Date(), 'Admin' FROM WW_LOCATION "
 strWhere = "WHERE (LOC_NAME NOT  LIKE '*CLOSED*' ) AND LOC_TYPE='WH' AND RANGED_IND='Y' "
strOrder = "ORDER BY LOC_NO, LOC_NAME, STATE_CODE"

strSQL = strSQL & strSelect  & strWhere & strOrder 
debug.print strSQL
    Conn.Execute strSQL, dbFailOnError

Open in new window

Author

Commented:
Right. It is still not sorted. It has to be something to do with the Like statement in VBA.

Here is the debug output as requested.

INSERT INTO MT_LOCATION_SUPPLIED ( LOC_NO, DESCRIPTION, STATE_CODE, LAST_UPDATE_DTS, LastUpdateUserID )  SELECT LOC_NO, LOC_NAME, STATE_CODE, Date(), 'Admin' FROM WW_LOCATION WHERE (LOC_NAME NOT  LIKE '*CLOSED*' ) AND LOC_TYPE='WH' AND RANGED_IND='Y' ORDER BY LOC_NO, LOC_NAME, STATE_CODE

As per my previous, if I execute this statement in SQL query window, it works perfectly fine.

Here is the SQL:

strSQL = "INSERT INTO MT_LOCATION_SUPPLIED ( LOC_NO, DESCRIPTION, STATE_CODE, LAST_UPDATE_DTS, LastUpdateUserID )  "
 strSelect = "SELECT LOC_NO, LOC_NAME, STATE_CODE, Date(), 'Admin' FROM WW_LOCATION "
 strWhere = "WHERE (LOC_NAME NOT  LIKE '*CLOSED*' ) AND LOC_TYPE='WH' AND RANGED_IND='Y' "
strOrder = "ORDER BY LOC_NO, LOC_NAME, STATE_CODE"
Most Valuable Expert 2012
Top Expert 2013
Commented:
Hey - are you using an Access or a SQL back end?

I just noticed that you are using Conn.Execute instead of CurrentDB.Execute.

If you are using SQL Server or an ADO connection (even to an Access back-end), you need to use % for your wildcard characters, not *.  The * wildcard WILL work in Access's query builder, but will not work in VBA with an ADO connection.

Try this:


dim strSQL as string
 Dim strSelect as string
 Dim strWhere as string
 dim strOrder as string

 strSQL = "INSERT INTO MT_LOCATION_SUPPLIED ( LOC_NO, DESCRIPTION, STATE_CODE, LAST_UPDATE_DTS, LastUpdateUserID )  " 
 strSelect  =  "SELECT LOC_NO, LOC_NAME, STATE_CODE, Date(), 'Admin' FROM WW_LOCATION "
 strWhere = "WHERE (LOC_NAME NOT  LIKE '%CLOSED%' ) AND LOC_TYPE='WH' AND RANGED_IND='Y' "
strOrder = "ORDER BY LOC_NO, LOC_NAME, STATE_CODE"

strSQL = strSQL & strSelect  & strWhere & strOrder 
debug.print strSQL
    Conn.Execute strSQL, dbFailOnError

Open in new window

Most Valuable Expert 2012
Top Expert 2013

Commented:
Btw, I tested the code I posted earlier using DAO (currentDB.Execute) and it definitely works, so I think that the wildcard change will fix this for you.

Author

Commented:
Hey champ. Sorry about the delay in responding, just got off a sev 1. This solution works a treat. Thanks heaps. FYI I am using ADO with Access Back End but will migrate to SQL server when switching to production.

Thanks again champ.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial