Re: [MS_AccessPros] the expression on click you entered as the event property setting produced the following error: function is not available in expressions in query expression 'Format([end date],"mmmm yyyy")

 

The problem is the library is missing or can't be located on the target machine.  It may be because you have multiple versions of runtime installed on the VM?  I used to be able to have multiple versions of Access and parts of Office installed on one machine, but since about 2002, I've quit doing that.  Instead, I set up a different VM for each version that I want to run.  Saves a lot of headaches.

Try uninstalling ALL the runtimes on the VM, then just reinstall the 2003 version and then try your app.  If you can remember the sequence in which you installed them on the VM, uninstall in reverse order.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Sep 14, 2017, at 5:38 PM, sarahk@schemesoftware.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I did compile, save etc and it does not make a difference. Still having the same issue.

On my computer there are no missing VBA libraries.

Sarah

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

If it's not finding Date(), then it's definitely missing a VBA library.  It has nothing to do with the fact that it's the Date function.  Did you do a compile, save, and compact on the app in full 2003 before you sent it to the VM?

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

http://www.viescas.com/

On Sep 14, 2017, at 00:54, sarahk@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

It will be difficult/impossible to open it with a full version in the VM.

I tried running it in 2003 which is the  version it was compiled in. I got the same results.

I think it might have something to do with dates. Text Boxes  on forms that have default value 'Date()' , are not being populated in the virtual machine. they show error '#name?'.

Sarah

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Sarah-

When Access can't find a common VBA function like Format, it means you're missing one of the required libraries on the target machine.  That's kinda hard to fix if all you have is runtime on the server.  Basically, you need to be able to open the database on the target machine in a full version, then open the VB Editor and look at Tools / References.  If you could do that, you would probably see one of the libraries marked as "missing", and then you could take steps to fix it by finding an appropriate replacement library in the list and doing a compile and save.

In general, you should never try to run a database built and compiled with one version of Access on another version.  Access does try to find and "upgrade" the library references, but it doesn't always succeed.  And in the process, it marks the project as not compiled, which will slow things down.

Do you have a way to open the database on the server with a full version of Access?

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Sep 13, 2017, at 11:42 PM, sarahk@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have a MS Access 2003 db / Windows 7 , it is split: FE&BE.

It works fine on all my computers , also on computers running just MSAccess Run time with various operating systems, Citrix etc.

I set up a Virtual Machine in Google Cloud. using Server 2008 R2. I connect to it with RDP. I installed the DB and the MDE.

Most of the functions work fine, but on some I am getting the following message :

the expression on click you entered as the event property setting produced the following error: function is not available in expressions in query expression 'Format([end date],"mmmm yyyy")

the expression may not result in the name of a macro etc.

I tried run time versions 2003, 2007 and 2013. They all generate the same error on this VM. 

The code that generated the above error, is an event in VBA:

DoCmd.OutputTo acOutputQuery, "AR YEARLY1 Bx", acFormatXLS, , True

 

The Query "AR YEARLY1 Bx" is as follows:

SELECT [ar yearly1 b].[MONTH] AS [Service Date], iif(private1=true,"Private","NonPRV") AS [Pay Class], insrcd AS [Insurance Plan], iif([ar yearly1 b].[transp]=0,Days) AS Daycare, RT AS [Transportation RT], OW AS [Transportation OW], I AS [Invoiced Amount], P AS [Paid Amount], W AS [Write Off Amount]

FROM [ar yearly1 b]

ORDER BY [emonth], [month], insrcd, days DESC;

"ar yearly1 b" is as follows:

SELECT [ar yearly1].*, [ar yearly1 a].rt, [ar yearly1 a].ow

FROM [ar yearly1] LEFT JOIN [ar yearly1 a] ON ([ar yearly1].private1 = [ar yearly1 a].private1) AND ([ar yearly1].month = [ar yearly1 a].month) AND ([ar yearly1].insrcd = [ar yearly1 a].insrcd) AND ([ar yearly1].transp = [ar yearly1 a].transp)

ORDER BY [ar yearly1].emonth, [ar yearly1].private1, [ar yearly1].insrcd, [ar yearly1].transp;

"ar yearly1" is another query, based on another query etc.

Dont know if it makes sense to post all of the queries. 

I googled and found suggestions: missing libraries, unicode language, etc.

I checked it all and found no problem. 

Found some references to security issues, so I ran the following:

reg delete hkcr\typelib\{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}\2.0 /f

if exist %systemroot%\SysWOW64\cscript.exe goto 64 

%systemroot%\system32\regsvr32 /u mscomctl.ocx

%systemroot%\system32\regsvr32 mscomctl.ocx

exit

:64

%systemroot%\sysWOW64\regsvr32 /u mscomctl.ocx

%systemroot%\sysWOW64\regsvr32 mscomctl.ocx

Exit

It did not make a difference. I am having the same issue will all 3 version of MS Access I tried: 2003,2007,2013.

Most of the forms, reports, queries work fine.

Any suggestions for me?

Thanks 

Sarah

 

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (6)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___