RunSQL no longer recognizes RTRIM()

[howard w]

DoCmd.RunSQL "UPDATE tmpVendorInventoryImport SET PartNumber = RTRIM(LTRIM(PartNumber))"

fails -
Error 3085 - Undefined function 'RTRIM' in expression

?? this works in 32 bit Office 365. Did I leave something out?

[NeoPa]

I can confirm this seems to work fine in 32-bit, but in 64 - and mine's 2019 so not even 365 - it allows the expression builder to create references to these two functions (LTrim() & RTrim() - both from the VBA library.) but then complains when you actually try to use them :-(

I went simpler than you and used a simple SELECT query in the design grid and just entered :

X: LTrim('FFF')

It complains with a compilation error if I try to move off that field in the query.

[isladogs]

When standard functions such as Left, Mid RTrim etc aren't recognised, this usually indicates some issues with references or corruption in your project. Try each of these in turn & test after each step
1. Check all standard references are listed
2. Create a backup, then first run a compact & repair.
3. If that achieves nothing, next decompile your project
4. Finally, if all the above fail, try importing all objects into a new blank database

However, your code is unnecessarily complex. Replace both LTrim & RTrim with Trim which does both operations in one step.
I would also suggest using CurrentDb.Execute as it is more efficient than DoCmd.RunSQL

CurrentDB.Execute "UPDATE tmpVendorInventoryImport SET PartNumber = TRIM(PartNumber);", dbFailOnError

[NeoPa]

It looks like we may have cross-posted.

From my earlier reply you'll see this isn't a database corruption issue, nor is it related to how the SQL is triggered (I put it in a new QueryDef in simple design view.) but that there is a real difference between trying these functions (I suspect any that come from the VBA library.) in a query from 64-bit and 32-bit versions of Access.

I tested this - not 100% rigorously but my findings did match those of the OP and further testing would require access to environments I don't have available.

My best guess is that the Expression Service (that resolves function calls within queries) has access to the VBA library in the 32-bit environment but doesn't in the 64-bit one - which is pretty weird to say the least.

PS. I have no arguments with any of your points generally of course. All good advice.

[isladogs]

Hi
Yes I was typing when you posted earlier.
However, I had tested this in A365 64-bit and it worked absolutely fine for me

Here is a simple select query using LTrim, RTrim and Trim

SELECT id, T1, LTrim(RTrim([T1])) AS Expr1, Trim([T1]) AS Expr2 FROM Table1;
 

I also tested the same expressions in an update query in 64-bit.
Finally I tried that update using a sql statement in code.
Again, no issues

[NeoPa]

Thanks for that. I'm very curious to know under which circumstances it works then. I know I only tested in 2019 but these seems like a fundamental issue to me - except not so much if you find it works ok even in 64-bit. Your results certainly seem to indicate that with no ambiguity.

[isladogs]

Absolutely. No difference in behaviour between 32-bit & 64-bit.
Periodically, I've experienced issues whereby Access hasn't recognised e.g. the Left function.
Its always been caused by a missing / broken reference OR corrupted code
Resetting references and/or decompiling has fixed the issue whenever it has occurred

[SioSio]

I think it's the opinion of isladog.

The reason why the library cannot be referenced may be that the library referenced in the environment where the macro was developed does not exist in the environment in which it is executed, or the path is different.
Check if there is a "non-referenceable library" in the [Reference] dialog.
If a reference is marked as MISSING, uncheck.

[NeoPa]

In both of my tests I also checked that it was available to use in the Immediate Pane - which it was. As far as I understand, the VBA library - which all of these functions are members of - is one where the reference is available automatically. That is to say it isn't even possible to run without it.

The Expression Service is harder to quantify of course, but I find it unlikely it could fail to have access to that most fundamental of libraries. Somehow this is happening though. I can't deny that.

I just had a thought about the database I tested. It was in an uncompiled state at the time. Let me test to see if that makes a difference.

It did. It worked fine in 64-bit in a new blank database. Maybe it's as simple as it only works when the current project is compiled.

[jimatqsi]

I have seen odd stuff like this on machines that had the 64-bit version of Access installed and then uninstalled in favor of the 32-bit version. Programs started failing on standard VBA library calls but recompiling the project solved the problem. Took a long time to figure that out.

[isladogs]

@NeoPa
I still can't replicate your results.
Even with an uncompiled project in 64-bit Access, functions such as LTrim work perfectly in both queries & VBA

As for the 4 default references, you can remove all but the VBA & Microsoft Access xx.0 Object Library without Access complaining (not that I recommend anyone does so). The above tests still work

Some time ago, someone sent me an Access database which, when I finally managed to open it, had no references ticked (yes I really meant NONE). Needless to say it was corrupt but I managed to recover some of the contents even so.

I do sometimes wonder why Access doesn't just build the default references into each Access project rather than load them...but that's a different discussion

[NeoPa]

IslaDogs:
Even with an uncompiled project in 64-bit Access, functions such as LTrim work perfectly in both queries & VBA

When you say uncompiled do you simply mean that you haven't requested a compilation explicitly or does it have a problem such that it can't compile?

Access will compile as & when it needs to in my experience - regardless of any settings as to when it should. If you have code in your project, on the other hand, that doesn't compile because it has errors - my first test was done on a 64-bit database with a project that had been designed for 32-bit exclusively - then that's the situation I'm referring to.

I tried again with an otherwise blank database that I ensured couldn't compile as I'd entered the following code in a standard module :

Option Compare Database
Option Explicit
 
With Me

When I tried to run the following SQL :

SELECT LTrim(RTrim('     X     '))

in a simple query - not even saved - it resulted in :

The following worked fine of course.

SELECT '     X     '

I did further testing and some compile errors resulted in it being able to run whereas others didn't. My original allowed it to run in the Immediate Pane as long as the quotes were converted to doubles but not from SQL. Other tests either allowed both or blocked both. I guess the type of problem stopping the compilation matters. Getting to that level of understanding of the matter is not something I have a great interest in though. Some interest - but not for the work that would involve.

[isladogs]

I originally meant that it wasn't compiled though I checked after running the query/code & it was still uncompiled
However, based on your prompts I have:
a) added the same erroneous With Me code to prevent compilation
b) switched off Compile On Demand in VBE Options
Neither made the slightest difference. The functions were still accepted and ran successfully no matter how I tested it.

Anyway, I agree that we've beaten this subject to death even though our results differed

[NeoPa]

IslaDogs:
Anyway, I agree that we've beaten this subject to death even though our results differed

Pretty much ;-)

I would only add that my tests were done on 2019 so there may be a difference because of that. Your descriptions of how you tested exactly matched mine otherwise.