[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")

 

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: sarahk@schemesoftware.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

---

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

.

__,_._,___