LEFT command causes problems in Word VBA

[Bluejay07]

Hello,

I have a query that is run in Word VBA as part of an email mail merge function.
It has been working fine until this most recent change.

.OpenDataSource Name:=strDataSource & "\" & DocNameCut & ".dbf", _
   ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
   AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
   WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
   Format:=wdOpenFormatAuto, Connection:= _
   "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & strDataSource & ";" & _
   "Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";" & _
   "Jet OLEDB:Engine Type=18;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=" _
   , SQLStatement:="SELECT * FROM `" & DocNameCut & "` WHERE EMAIL > '' AND LEFT(DNOTTYPE,2) = 'DE'", SQLStatement1:="", SubType:=wdMergeSubTypeAccess

The left command seems to prevent any records from being returned.
If I removed the left command and have DNOTTYPE equal to the full value of the record instead of the first 2 letters, it works fine.

Any suggestions on why adding the LEFT command causes problems?

Thanks.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[MajP]

This is usually a reference problem. Check all your references, you likely have a broken reference.
Here is what happens.
References are listed alphabetically, and left is inside "Visual basic for applications." So "V" is usually close to the bottom. When you call the vba left function is goes searching through the dll in order, but when it hits the broken reference it cannot go any further. So it never finds the left function.
Sometimes you may have to actually unclick all references and then re add them.

 

[PHV]

Anyway, you may try this:
WHERE EMAIL > '' AND DNOTTYPE LIKE 'DE*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

I would be careful doing what PHV suggests. Although that may band-aid the immediate issue, unless you fix the reference your code is likely to fail throughout the application. Another band-aid is to suggest that you fully qualify the function. So instead of LEFT(DNOTTYPE,2) you use VBA.LEFT(DNOTTYPE,2). This works because instead of searching for the left function in alphabetical order it knows which dll to look in. So it never hits the broken reference. Again that is a band-aid and likely there are other issues in the application.

 

[Bluejay07]

Hi,

Thanks for the responses.
I checked the references and Visual Basic For Applications is the first reference on the list.
I cannot check/uncheck it since it's in use although testing LEFT on a separate line, the intellisense appears immediately.

In this case, wouldn't LEFT be considered as an SQL command and not a VBA command?

I tried both solutions of VBA.LEFT and LIKE 'DE*'.
Neither seem to work.

I have also tried DNOTTYPE IN ('DEB','DEF'). That didn't work either.

It seems like the only way it works is to refer to the fully quantified value.
(DNOTTYPE = 'DEB' OR DNOTTYPE = 'DEF')

If at first you don't succeed, then sky diving wasn't meant for you!

 

[MajP]

To be safe I would create a brand new blank database.
Import the tables
Import the queries
Import the forms, reports, modules, and macros
Reset your references.
Should take about 5-10 minutes to do that.

 

[PHV]

And this ?
DNOTTYPE LIKE 'DE%'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bluejay07]

Thanks for the replies.

Here's what I ended up using and it seems to work better for code management.
I believe using this method also eliminates another issue I was contemplating.

I first queried the source for DNOTTYPE (this will always be the same for all records from the same datasource) and saved the value.
Next, I used a case statement to determine the value of DNOTTYPE and then created several queries based on the value discovered.

Initial tests display good results.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[MajP]

Again this may be a bandaid
What happens in the immediate window if you type
?left("Text",2)
If this works then my initial assumption is wrong, because if it is a reference issue it should fail everywhere.

 

[Bluejay07]

Hi MajP,

It does work in the immediate window.
Te was returned.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Andrzejek]

Are you using Access as a data base? Or Oracle, SQL Server, other....?

If Oracle, Left() would't work. Oracle's syntax would be: [tt]SUBSTR(YourField, 1, 2)[/tt] or PHV's suggestion of [tt]DNOTTYPE LIKE 'DE[highlight #FCE94F]%[/highlight]' [/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Bluejay07]

Hi Andy,

After re-reading PHV's code, it might have worked since I do believe SQL Server uses the percent sign as a wildcard.

The database is a dbf file and using SQL Server syntax to query the database.
After restructuring the code (as partly described above), things seems to run smoother and the code is a lot cleaner as well.

I'm happy with the way the code is now working.

I thanks everyone for their contributions/suggestions.




If at first you don't succeed, then sky diving wasn't meant for you!

 

[dhookom]

Keep in mind some databases are case-sensitive which might be your issue. It is helpful if you state DBF in your initial post.

Duane
Hook'D on Access
MS Access MVP

 

[Bluejay07]

Hi Duane,

I wasn't too concerned about case sensitivity but thank you for mentioning it.
As for indicating the database source, I didn't specifically state the source since that wasn't the issue I was trying to address, although if you look at the first line of code I submitted, you will see .dbf listed.

I'll try to be more specific in the future.

If at first you don't succeed, then sky diving wasn't meant for you!