LEFT, RIGHT, MID won't work

[jerem]

Hi,
There are plenty of documentation on this on the web but nothing that seems to help. I have a LEFT function in a query that does not work on Access RunTime 2010 while it works perfectly fine on Access 2010.

Basically, I have a memo field called Notes and in the query I create a new field called ShortNote with the following function: IIF(LEN([Note])>45,LEFT([NOTE],45)&"...",[Note])

Again that works perfectly fine in Access 2010, the issue is with Access RunTime (that I installed on another machine that does not have Access 2010).

I have isolated each function and it is definitely LEFT that is the problem. I have also tried MID and the same problem appears.

Thanks for your help!

 

[Mihail]

Have you checked ? The issue appear only when this functions are in the queries or is the same if the functions work in a module ?

 

[jerem]

I have tried it in a query and in a form (unbound field with the expression) I haven't tried it in a module. Do you mean I should create a VBA module and do this operation through coding?

 

[Mihail]

Do you mean I should create a VBA module and do this operation through coding?

You can try. This was a suggestion, not a solution.

Forgive me but I can't know your skills before some conversation.
Now I know that you are the owner of a good skill.

So, try this. Maybe Microsoft forget to implement the feature in Run Time.
If don't help we'll try to found out another solution.

 

[pbaldy]

I'd suspect a reference problem:

http://www.accessmvp.com/djsteele/accessreferenceerrors.html

 

[jerem]

Alright, if this is a reference problem, I guess that what I need to do is:

1. Get a list of all the files that are being used by the Access application
2. Get a copy of those
3. Include them in the package solution (with the Package Wizard)

Am I on the right track or not?

 

[jerem]

Mihail, I have not tried yet but I would expect that the result is the same. I also have an issue with the function date() in the RT environment. Funny enough if you substitute Date() to Now(), it works fine.
Even stranger, I have another Access application that use the function Date() and works perfectly fine on that same RT environment.
I am going to follow the reference issue but I really am a newbie with this reference issue. Hope the points I listed above are the correct approach.

 

[Mihail]

Mihail, I have not tried yet but I would expect that the result is the same. I also have an issue with the function date() in the RT environment. Funny enough if you substitute Date() to Now(), it works fine.
Even stranger, I have another Access application that use the function Date() and works perfectly fine on that same RT environment.
I am going to follow the reference issue but I really am a newbie with this reference issue. Hope the points I listed above are the correct approach.

I encountered this in the full version.
Sometimes, with no reason, my VBA editor don't allow me to use Date(). The editor remove the brackets but the Date work as is expected:
MyDate = Date

 

[jerem]

I encountered this in the full version.
Sometimes, with no reason, my VBA editor don't allow me to use Date(). The editor remove the brackets but the Date work as is expected:
MyDate = Date

I would expect that to be because of the difference between Date in VBA and the function Date(). Actually Date without brackets in a module is the same as VBA.Date

Correct me if needed but that's how I would interpret the issue you just described.

 

[jerem]

Ok... Bump! Some tell me it is a reference issue. Some say it is not... Nobody has been able to provide an answer. I am sure other developers have faced the same kind of issues. The application is working perfectly fine on any computer running full Ms Access, but once running on machines with Runtime only, issues arise with LEFT, MID, RIGHT, DATE...

 

[Mihail]

That make me think that IS a reference issue. The necessary library isn't installed when you install the Runtime but is installed when you install the full version.

So you must find a way to see what library(ies) are missing.

Maybe you can find out on WWW what libraries should have in order to use this functions.
If you can't, design a very short DB where to use only this functions in a procedure then, remove the references one by one until this functions will stop to work. Now, you know what library should have for this functions.

Install the library on the machine that run only the Runtime.

Of course, this is only "theory", but, is more than nothing.

 

[jerem]

That sounds like pretty good theoretical thinking to me... I will give it a try!

 

[jerem]

You were right, it was a reference issue. I used a code to list them all and then I packaged them with the application. There still is an issue with one reference file: EXCEL.EXE

Since some of the machines do not have office I need to package EXCEL.EXE for some pivot table features of the application. The issue is when packaging additional files, the only location I can put them is in the application default folder (C:\) or one of its subfolders. What I would like to do is to put EXCEL.EXE in C:\Program Files\Microsoft Office\Office14 but this doesn't seem to be possible with the Access Packaging Solution

The reason for this is that on some machines, with EXCEL.EXE intsalled in the default folder, it works perfectly fine (the application looks for EXCEL.EXE the first time it opens) and for some it doesn't work until I manually move EXCEL.EXE to the Office14 folder.

So, is there a way to have the packaging solution put EXCEL.EXE in C:\Program Files\Microsoft Office\Office14 or can this be solved with an additional registry key? If the later, how to do?

Thank you.

 

[gemma-the-husky]

you are probably better using late binding to avoid having such problems with the excel library.

basically you declare the variables as general objects, and replace excel constant names with their explicit values. then you will get trappable run time errors when you try to use the excel functions, that you can recover from.

needs a bit of coding change, but not too much.

 

[jerem]

So is late binding the only solution? No way to register EXCEL.EXE in its new location or to install it in a specific folder?

 

[pbaldy]

FWIW, I would also have suggested late binding. Never tried to install Excel as part of a packaging solution, so not sure if what you ask is possible.