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

 

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@schemesoftware.com [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 (2)

---

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

.

__,_._,___