473,216 Members | 1,254 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

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 3126
32,554 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
451 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,554 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
451 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, 191 views)
May 16 '21 #5
32,554 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
451 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
272 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,554 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,271 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
451 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,554 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, 179 views)
May 17 '21 #12
451 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,554 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

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

Similar topics

by: jose luis fernandez diaz | last post by:
Hi, This is my rtrim version: inline void rtrim(string *ptr_s) { string::reverse_iterator rit= ptr_s->rbegin(); while(rit != ptr_s->rend() && *rit==' ') ptr_s->erase((++rit).base()); }
by: Rashmi Dixit | last post by:
Hi, My application accesses DB2 over DB2 CLI and we use the calls SQLTables, SQLTablePrivileges, SQLColoumns to retrieve the schema information from the system catalog tables. When we call...
by: rajdb2 | last post by:
Hi, I am using the following sql statement SELECT rtrim(rtrim(coalesce(substr(char(v.creationdate),1,4) || '-' || substr(char(v.creationdate),6,2) || '-' || substr(char(v.creationdate),9,2) ||...
by: DCM Fan | last post by:
Access 2000/Windows 2000 Pro with all current updates on the client; SQL Server 7.0/Windows 2000 Server with all current updates on the server. Bottom line: In a certain context dbs.Execute...
by: Pathfinder | last post by:
Hi All I am trying to run the below but I receive the following error "runsql action requires an argument consisting of an SQL statment" Dim MySQL$ MySQL$ = "Select * from mytablename"...
by: Richard Hollenbeck | last post by:
Help! I don't know why this isn't working: Private Sub Combo9_Change() Dim UsersCourseSelection As String UsersCourseSelection = Me("Combo9").Value Combo13.Visible = True 'the following...
by: David Teran | last post by:
Hi, i am using Postgres 7.4.2 with jdbc. Every time i try to select a varchar the SQL generated by the jdbc driver uses RTRIM(t0.columnname) which breaks an existing application. Is this normal,...
by: Mintyman | last post by:
Hi, I have erronous white space at the end of my 'description' field within my 'product' table. I tried using the RTRIM function but it won't let me because it is a TEXTBLOB (text) field. Can...
by: FAQ server | last post by:
----------------------------------------------------------------------- FAQ Topic - How do I trim whitespace - LTRIM/RTRIM/TRIM?...
by: TheSmileyCoder | last post by:
I have been playing around with transactions and one thing got me wondering. First is the code I am using: 'Start transaction 'Get default Workspace. Dim wrkDefault As...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.