470,861 Members | 1,790 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,861 developers. It's quick & easy.

RunSQL no longer recognizes RTRIM()

Expand|Select|Wrap|Line Numbers
  1. 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?
May 15 '21 #1
13 2703
32,311 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. X: LTrim('FFF')
It complains with a compilation error if I try to move off that field in the query.
May 15 '21 #2
358 Expert Mod 256MB
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

Expand|Select|Wrap|Line Numbers
  1. CurrentDB.Execute "UPDATE tmpVendorInventoryImport SET PartNumber = TRIM(PartNumber);", dbFailOnError
May 15 '21 #3
32,311 Expert Mod 16PB
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.
May 16 '21 #4
358 Expert Mod 256MB
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
Expand|Select|Wrap|Line Numbers
  1. 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
Attached Images
File Type: png Screenshot 2021-05-16 173958.png (6.4 KB, 83 views)
May 16 '21 #5
32,311 Expert Mod 16PB
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.
May 16 '21 #6
358 Expert Mod 256MB
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
May 16 '21 #7
270 256MB
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.
May 17 '21 #8
32,311 Expert Mod 16PB
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.
May 17 '21 #9
1,263 Expert 1GB
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.
May 17 '21 #10
358 Expert Mod 256MB
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
May 17 '21 #11
32,311 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. With Me
When I tried to run the following SQL :
Expand|Select|Wrap|Line Numbers
  1. SELECT LTrim(RTrim('     X     '))
in a simple query - not even saved - it resulted in :

The following worked fine of course.
Expand|Select|Wrap|Line Numbers
  1. 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.
Attached Images
File Type: jpg CompileError.Jpg (12.9 KB, 72 views)
May 17 '21 #12
358 Expert Mod 256MB
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
May 17 '21 #13
32,311 Expert Mod 16PB
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.
May 18 '21 #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by jose luis fernandez diaz | last post: by
reply views Thread by Rashmi Dixit | last post: by
19 posts views Thread by DCM Fan | last post: by
3 posts views Thread by Pathfinder | last post: by
2 posts views Thread by David Teran | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.