Access 2010 runtime won't accept =Format (mydate,"mmm") on control source of form field.

I have field on a continuous subform whose control source (form recordsource is a query) is:

=Format([DTDC_Date],"mmm")

So it takes the field DTDC_Date from the query and displays just the month.

This works fine in the full version of Access but I get a Name? error in runtime.

I really need the user just to see the month (even when they click on the field) - that's why I am using Format.

Any ideas how I an get this to work?

By the way I am using the international date format.

Thanks Lou
LouverrilAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
have you checked your references?  First thing I check when one of these standard Access/VBA formulas fails to work.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ArgentiCommented:
I would suggest you to create an additional field holding just the month number of your [date] field, then format it using MonthName function (which gives you the name of your numeric value).
On the form you can make a ComboList having all the 12 months of the year so the user could select one specific month, then write the code which will create the [date] value from 1st of the selected month + current year.
0
LouverrilAuthor Commented:
Argenti

The references from the accdb version should be copied when I rename it accdr (after making it accde)?

Thanks Lou
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jeffrey CoachmanMIS LiasonCommented:
A long time ago, I learned that if you want something to display properly formatted  in a Form or report consistently, always do the formatting in a query.
Then make the Report or form from this query and the formatting will most likely always be there...
(No need to worry about not setting the exact same format for each form/report control manually)

SELECT YourID, Format([DTDC_Date],"mmm") as DTCCDate
FROM YourTable

Or just add in the formatted field if you are paranoid like me..
;-)
SELECT YourID, [DTDC_Date], Format([DTDC_Date],"mmm") as DTCCDate
FROM YourTable

Now, I will still format controls most of the time, ...but any time I have problems with it displaying properly  (as you have here), I format it in the query, and I have never had a problem.


But fyed is correct, if something has gone wonky with your references, then the Formant() function may not work anywhere...

JeffCoachman
0
LouverrilAuthor Commented:
Sorry  the question about references above was for Fyed - apologies :-).

Lou
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Sounds like a missing reference issue to me.  When there are missing reference issues the built in VBA code like Format() will work.

Are you doing any Office (Word/Excel/Outlook/PowerPoint/etc) Automation? Do you have references set to any of the Office Apps?

Have you considered using Late Binding?

What other Office apps are installed on the target PC with  the Access Runtime?

Also see: Access Reference Problems
0
LouverrilAuthor Commented:
Jeff

I was doing that originally like this:
 Format([DTDC_DAte],"mmm") AS Monthme

but the subform would not even load. When I used a button instead to set the recordsource I actually got an error about using the format function.


Here is the full sql:
SELECT tblDowntimeCode.DTDC_ID, tblDowntimeCode.DTDC_Date, Year([DTDC_Date]) AS [Year], Format([DTDC_DAte],"mmm") AS Monthme, Month([DTDC_Date]) AS Expr1, tblDowntimeCode.DTDC_Code, tblDowntime_Codes.Code_Name, tblDowntimeCode.DTDC_JobsNo, tblDowntimeCode.DTDC_Comments, tblConfiguration_General.Config_SiteName, tblDowntimeCode.DTDC_Void
FROM tblDowntime_Codes INNER JOIN (tblConfiguration_General INNER JOIN tblDowntimeCode ON tblConfiguration_General.Config_ID = tblDowntimeCode.DTDC_Site) ON tblDowntime_Codes.Code_ID = tblDowntimeCode.DTDC_Code
ORDER BY Year([DTDC_Date]) DESC , Month([DTDC_Date]) DESC;


The form will not load.

I create another form (a single for this time)  with only three fields from the table on with the sql below and that won't load in runtime either:

SELECT tblDowntimeCode.DTDC_ID, tblDowntimeCode.DTDC_Code, Format([DTDC_Date],"mmm") AS Expr1
FROM tblDowntimeCode;


Thanks Lou
0
Dale FyeCommented:
I've not had the need to work with in a Runtime environment.  I would assume that the accdr file will contain the pointers to the references, which should be installed on the users computers when the runtime version of Access is installed.  However, if the users computers are running a different operating system that may be an issue.
0
LouverrilAuthor Commented:
Argenti

Thanks for your idea the monthname gives me the month in the right form (mmm) but I really also wanted the user to be able to right click and use the default Filtermso - formatting the field like that won't let me. I appreciate the combo box idea but I also wanted them to be able to right click.

many thanks,

Lou
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Lou,

I think you also posted this on another site. There you posted the references you have set. One of them was for the Microsoft Graph 14.0 object library. IIRC, this is only parts of the Office suite and not included with the Access Runtime.    

References
You have to be very careful not use third-party controls  or set reference to libraries that you do not have the license to distribute or that are only included with the paid versions of Office/Access. If the target PC will have a versions of Office installed without Access then you usually are OK. Need to test to be sure.

FWIW: I have over 1000+ users running my apps with the Access Runtime.   I run all my Access front end with Access in Runtime mode.

I have a few articles you may want to check out:

What is the Access Runtime Version?

 Access 2007 runtime deployment - FAQs
0
LouverrilAuthor Commented:
Thanks to you all for your help.

In the end for now I used the monthname function in the query (rather than on the form) and this DID let me right click and filter - so I got what I wanted.

However I need to review the information in detail that HiTechCode has supplied about references.

I have removed the reference to Microsoft graph, I had added it in earlier when I was learning more about charts. The system I am writing does use a lot of charts.

 However I did see a posting by HiTechCoach (I believe) on another site warning someone to use the inbuilt Access chart tool from the design tab in Access to avoid problems with references - so I did! I have removed the reference and everything compiles and works fine without it.

Many thanks!

Lou
0
LouverrilAuthor Commented:
Just for completeness:

Removing the Graph reference still meant the Format command would not work. I haven't got the time today but I will create a completely fresh simple database and see if it works. I think it will - because I MUST have used it before and most a=of my systems run using runtime.


Thanks again,

Lou
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.