Mid Function (1 Viewer)

[Privateer]

Windows 7, Office 2010 VBA 7.0, Access left and right functions work fine. I enter a mid(TBN,4,99) where TBN is a string variable, in this case, the table name. I want to get rid of the "dbo_" that precedes linked tables for a project. When I compile it, I get an error that VBA "expected array". If I comment out the line with Mid function, the compile is successful. Most searches point to references so my references are listed below:

VBA: C:\PROGRA~2\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL
Office 14.0 Object Library: C:\Program Files (x86)\Microsoft Office\Office14\MSACC.OLB
OLE Automation: C:\Windows\SysWOW64\stdole2.tlb
Office 14.0 DAO: C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\ACEDAO.DLL

Version 1628, VBA Retail 7.0.1628, Forms3: 14.0.6009.100

When I tried to re-register the ACEDAO I got an error that the file "was loaded but the entry-point DIIUnregisterServer was not found." So I tried to re-register the VBA file (VBE7.DLL) and that worked fine. So maybe there is something wrong with the ACEDAO.DLL file? The date of the ACEDAO.DLL file is 2/18/2013 15:45 and 533KB in size. Anyone have any thoughts or experienced this before? Any help would be appreciated. Thanks, Privateer

 

[spikepl]

What does Intellisense show if you slowly type

myString=Mid(.... ?

I suspect you somewhere have another function Mid defined - either in your code or in your references.

 

[Privateer]

I kind of solved the issue by using Right(TBN, Len(TBN) - 4), but I really like mid

So typing mid( give me no hints about what to type next, like string, start, stop etc. But it is a good thought so I did a search throughout the entire project for "mid(" and "mid" and I found the problem. I recently created a global variable called MID to hold the mapping primary key value and when I commented out the variable the function worked.

Thanks, spikepl I don't think I would have made the connection.

 

[MarkK]

Just an observation, but, because the Mid() function is provided by VBA, you can keep your same-named application-global member if you want, and just prefix your Mid() function calls with VBA, so if you slowly type "VBA." in a code window, intellisense pops up with public members of VBA, of which Mid() is one.