Right() function not working

[Rabbit]

Got a new computer at work. Which means a new installation of Access.

So now I can't use the Right() function. I checked the dependencies, they're the same.

I get the message "Object doesn't support this method or property."
Run-Time error 438.

[ADezii]

Got a new computer at work. Which means a new installation of Access.

So now I can't use the Right() function. I checked the dependencies, they're the same.

I get the message "Object doesn't support this method or property."
Run-Time error 438.

What exactly isn't it working on, Rabbit?

[Rabbit]

In VBA code, it works elsewhere. Haven't tried it with a query yet.

Otherwise, none of the following works.

Dim strTest As String

strTest = "Test String"
MsgBox Right(strTest, 2)
MsgBox Right(Year(Date)), 2)
MsgBox Right("Test String", 2)

[ADezii]

In VBA code, it works elsewhere. Haven't tried it with a query yet.

Otherwise, none of the following works.

Dim strTest As String

strTest = "Test String"
MsgBox Right(strTest, 2)
MsgBox Right(Year(Date)), 2)
MsgBox Right("Test String", 2)

I'm not 100% sure, but I think the built-in VBA Functions reside in
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6 \VBE6.DLL. It could be that this .DLL is corrupt. Did you try doing an Uninstall and then a Re-Install of Access?

Are other VBA Functions operational?

[Rabbit]

I'm not 100% sure, but I think the built-in VBA Functions reside in
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6 \VBE6.DLL. It could be that this .DLL is corrupt. Did you try doing an Uninstall and then a Re-Install of Access?

Are other VBA Functions operational?

Well, here's the weird thing. I basically copied, ctlr+c, pasted it into a new folder, and renamed the file. I was changing the code and tables to be used in a new database. Now, the function worked in the old database, I think that might be because i compiled it before I switched to my new computer. But it didn't work in the new one.

I used =Right("Test", 2) as the control source for a textbox in a form and that worked. But doesn't work in VBA code. I didn't realize they were different.

The Date/Time functions work, haven't tried any other string functions.

[ADezii]

Well, here's the weird thing. I basically copied, ctlr+c, pasted it into a new folder, and renamed the file. I was changing the code and tables to be used in a new database. Now, the function worked in the old database, I think that might be because i compiled it before I switched to my new computer. But it didn't work in the new one.

I used =Right("Test", 2) as the control source for a textbox in a form and that worked. But doesn't work in VBA code. I didn't realize they were different.

The Date/Time functions work, haven't tried any other string functions.

Here is a wild Theory. You are not getting the 'Object doesn't support this method or property' Error on the Right Function but on the Msgbox Function. You are using the Variant Form of the Function (Right()) which returns a Variant of Sub-Type Text which the Msgbox can't handle. Replace Right() with Right$() (String Version) and I'd venture to say that it will work. The fact that it works within the ControlSource Property of a Control indicates that there is absolutely nothing wrong with the Function itself. Must be Msgbox. I'm very curious, please let me know how you make out. Also, if Debug.Print Right("Test String", 3) works, my Theory is confirmed.

[Rabbit]

Here is a wild Theory. You are not getting the 'Object doesn't support this method or property' Error on the Right Function but on the Msgbox Function. You are using the Variant Form of the Function (Right()) which returns a Variant of Sub-Type Text which the Msgbox can't handle. Replace Right() with Right$() (String Version) and I'd venture to say that it will work. The fact that it works within the ControlSource Property of a Control indicates that there is absolutely nothing wrong with the Function itself. Must be Msgbox. I'm very curious, please let me know how you make out. Also, if Debug.Print Right("Test String", 3) works, my Theory is confirmed.

Well, the problem is the MsgBox was only for testing and I got the error before that. Originally it was something along the lines of:

NewPath = path & Right(Year(Date), 2) & "-" & month & day

I was just trying to isolate which function was messing up.

Also I tested it on a different computer and it works. So something must be missing from my new installation of Access.

[ADezii]

Well, the problem is the MsgBox was only for testing and I got the error before that. Originally it was something along the lines of:

NewPath = path & Right(Year(Date), 2) & "-" & month & day

I was just trying to isolate which function was messing up.

Also I tested it on a different computer and it works. So something must be missing from my new installation of Access.

Thanks for the reply - a rather unique problem.

[Rabbit]

Yes.. indeed. I won't be able to try a reinstall until I get into the office on monday.

[Denburt]

You might try one thing before a reinstall. I have had in the past and again just recently managed to handle this simply by commenting out the offending right or in my case a left function. Then recompile the modules and you are likely to find some other offending line in the code somewhere. Once you find the actual issue you can correct it and uncomment the right or left function, at which time things went right back to normal.

[Rabbit]

I think my form is corrupted. I copied the code to a new form and it works. Looks like I'll just have to recreate the form.

[Denburt]

Ah the corrupt forms issue... One of my favorites.

[Rabbit]

Okay, I'm just an idiot. I accidentally named one of my controls Right.

Whenever I have a new database for work I carry over a lot of my old forms and code because they are all very similar except they collect different information. Each iteration is better coded and designed as I go along. This was just something that started from the very first database I created and was never an issue till now.

Thanks for the help everyone.

[NeoPa]

Rabbit,
Sorry I didn't get to this before now.
Looks like you found the culprit though - Well done. They can be really nasty ones those :(
BTW That advice from Denburt about commenting out offending lines until you find a fundamental problem (that can be understood ;)) is worth noting down and remembering. You'd be surprised at how many issues you can get past with that approach :)

[Denburt]

Okay, I'm just an idiot. I accidentally named one of my controls Right.

Whenever I have a new database for work I carry over a lot of my old forms and code because they are all very similar except they collect different information. Each iteration is better coded and designed as I go along. This was just something that started from the very first database I created and was never an issue till now.

Thanks for the help everyone.

I have had similar issues in the past I think it happens to all of us. the more we carry with us the better it gets. Glad ya found it.

Yeah Ade I noticed that in one of my first db upgrades or something, took me a while to figure out what was going on and I swore I would never forget it. :)