Access 2010 v Access 2013 - does FORMAT work differently? (1 Viewer)

I have a text box in a report in Access 2013. The database was in Access 2010 and worked fine, and now I have upgraded to Access 2013. The text box has in it

="Report from "&(Format([startdate]),"dd mmmm yyyy")) & " to " & (Format([enddate]),"dd mmmm yyyy"))

the data source is based on a query which asks for the [startdate] and the [enddate]. But when I run the report I get a input question for FORMAT, and if I leave it blank I then get a #TYPE! error. The only thing I can think of is that the Format field for the properties for the text box is blank ... but this didn't seem to be a problem with Access 2010. Does anyone know what is wrong, I need help please?

William86 said:

..

="Report from "&(Format([startdate]),"dd mmmm yyyy")) & " to " & (Format([enddate]),"dd mmmm yyyy"))
...
Does anyone know what is wrong, I need help please?

Click to expand...

To many ")" + in wrong place!

strange how it worked in 2010 tho'

CJ_London said:

strange how it worked in 2010 tho'

Click to expand...

Yes, I had decided not to mention it, but ... !

sorry, but the extra )'s were my mistake in copying to the post. In fact they are not there, plus I have taken out the ( before FORMAT and double checked that I have the correct number of brackets (open and closing). I don't need to put the whole FORMAT expression in brackets and have taken those out as they seem to be superfluous, but I still get the #TYPE! error ????

Does the [startdate] get included in the record source for the report?
What do you get if you only set the control source for the text control to =[startdate] ?
Sometimes MS-Access get confused if you both have the same name for a field in the control source and for a control on a report/form!

That's the odd thing. If I delete the FORMAT function so the text box is simply ="Report from "& [startdate] & " to " & [enddate]

then it is fine, and the start date is in the format in which it was input. But if I try to define the format using the FORMAT function, I get the #TYPE! error ???

That's why I'm wondering if having the format properties in the text box property sheet has something to do with the problem?

sorry, I meant ... having the format property blank, not set as anything ... has that got something to do with the problem!

I can't see from where in the world you're coming, but from where I'm I've to use ; instead of , in some places - so try:

sorry, but the extra )'s were my mistake in copying to the post

Click to expand...

I can never understand why people don't just copy and paste their code - trying to debug something that has typo's in it is a complete waste of time

First ... the actual text box is in Dutch, running on a machine with a Dutch version of Access 2013, so the words would probably mean little to anyone else and the date notation of dd mmmm jjjj (for the year) would appear to be incorrect when actually it is fine if the regional settings are for Dutch. Plus in the past I have found that posting anything in Dutch does not get a good response (in fact often they get no replies at all)! Hence, so I get a response, I try to post in English!

Secondly ... I can assure you that in the past, by changing the regional settings from English to Dutch, the report concerned would run on an EN version machine, so I am 100% sure that the language settings is not the problem as long as the year notation set in the computers regional settings matches the code (ie yyyy for EN and jjjj for NL).

The replacement of the comma by a semi-colon as you suggest doesn't fix the problem, in fact I just get another error message indicating a missing operator or operand, or an invalid character!

The bottom line is that in moving from Access 2010 to Access 2013, and changing nothing at all, the text box gives an error ... which I don't understand. Thus whatever help I can get is appreciated! Sorry if you feel that you're wasting your time!

Then I would do it step by step; then you wrote without the format function it works.
So what do you get if you've?

I created a new text box and put in it
=Format([kwartaal begindatum],"dd mmm")
kwartaal begindatum is Dutch for quarter begin date.
when I open the report, I am asked to input the parameter value (kwartaal begindatum), that is okay. I input the date in the form 1/1/2015 and I get #Error

if I put the following in the text box
="Report from "& Format([kwartaal begindatum],"dd mmm")
then I get #Type!

???? The whole thing mysterfies me!

Sorry if you feel that you're wasting your time!

Click to expand...

I'm sure you can understand the frustration.

Access 2010 is stricter than 2007 for some things and in turn 2007 is stricter than 2003. So I would not be surprised if 2013 is stricter than 2010.

I don't use 2013 as yet, but on the basis 2013 is stricter than 2010 some thoughts -

what happens if you format a date field, rather than a parameter? does that work OK?

have you tried formatting as "long date" (or its Dutch equivalent, if relevant)

have you tried formatting "dd\ mmmm\ yyyy"

have your tried Format(CDate([Enter Date]),"dd mmmm yyyy") or

Format("#" & [Enter Date] & "#"),"dd mmmm yyyy")

is your parameter specified in the query as a date type? If not have you tried specifying it?

Thanks for your reply and continued help in trying to figure this out. I think what you say about 2007 being stricter than 2010, and 2013 stricter than 2010 probably is quite correct. It's a pity that MS don't tell us these things! The version of 2013 that I am using is via Access 365 which means that it can have automatic updates which could also be a possible problem in the future. For stability I don't like automatic updates as once the dbase is running okay I think it is best to leave it alone and just let it do its work. Hence, I turn the auto update function for Access 2013 (via Windows update) off and try to "manage" those particular product updates. It gets a bit more complicated with Win 8.1 as it likes to download and install updates automatically. I am still trying to understand that, but for now I have this version of Access 2013 EN running on Win 7.

if I create a new text box, and as the control source put =[input-date] and then on the Properties Sheet for format, I format it as a "long date", when asked to Input Parameter Value I input "1/1/2015" (without the quotes of course) I get #Error ....
if I format it as a short date, and still input 1/1/2015 (a short date), I still get #Error ....
if I put =([input-date]) ... add the brackets, it makes no difference, I still get #Error ....

If I leave the format property blank, I still get #Error. In fact if I put anything in the format property, including percent, currency or euro, I get #Error.

In the query the field that selects the records based on the start and end dates has a criteria Between [kwartaal begindatum] And [kwartaal einddatum]. How can you specify the criteria as a date? Certainly the original field [DateServed] that is being used is specified as a Date/Time data type.

Format(CDate([Enter Date]),"dd mmmm yyyy") ... I had to add = before Format, in the control source property, and got #Type! error.
Format("#" & [Enter Date] & "#"),"dd mmmm yyyy") ... similarly, I pasted this into the control source property of the text box, added = before Format, and got #Type! as the error.

Just to add another perspective to the problem, I have another database for accounts. In a report I have a text box with ="Printed: " & Date() ... which on the same computer, same Access 2013 version, works just fine giving a result of "Printed: " and then today's date. If I copy this text box and paste it into the problem database, into the report where I am having difficulty, I first get a input parameter value for "Date" and then #Name? error irrespective of what date I input. Same text box, same text box property settings, but different result.

Is the fundamental problem either that I have the control source as =[input something] or I have something in the problem database not set correctly.... ???

I don't have 2013 to verify anything but I thought you were suggesting the problem was with formatting a criteria - and it wasn't recognising the format string - e.g.'dd mmmm yyyy' - but elsewhere the format function worked OK.

It now sounds like you are saying the format function is not being recognised anywhere - in which case I would check references. Not sure which library it is in, I think Visual Basic for Applications.

in the VBA window, immediate window, what happens if you type

?Format(Date,"dd mmmm yyyy")

William86 said:

...
Just to add another perspective to the problem, I have another database for accounts. In a report I have a text box with ="Printed: " & Date() ... which on the same computer, same Access 2013 version, works just fine giving a result of "Printed: " and then today's date. If I copy this text box and paste it into the problem database, into the report where I am having difficulty, I first get a input parameter value for "Date" and then #Name? error irrespective of what date I input. Same text box, same text box property settings, but different result.
..

Click to expand...

Could sound like some references problem or some corruption, did you try a Compact and Repair?
Only to get clear what it could be - create a new form, place a button on it and in the click event for the button, place the below code:

yes, what seemed to be a format problem now seems to be something more fundamental. I suppose the fact that all was fine with Access 2010 and the problem occurred with the move to Access 2013 should have flagged the problem, but I find that sometimes problems in Access are not that easy to find and fix. That is why, as a final act of desperation I look to the forum for help!

I typed in ?Format(Date,"dd mmmm yyyy") in the VBA window, immediate window, and get a message "can't find project or library". I have checked the references and VBA for applications is there, also Microsoft VBA for Applications ext 5.3 ... the only one which is marked as "Missing" is Microsoft Office XP Web components!

I have tried a compact and repair but it did nothing?

Indeed, the problem is in the references. I have compared the reference list in the "problem" database with another good database and the first thing I notice is that Microsoft Office XP Web components is marked as "MISSING" in the problem dbase, and omitted from the list in the good dbase. So I have unticked that and removing it seems to make no difference.

Second, Microsoft Office 15.0 Access database engine Object library is not ticked in the problem dbase, but is in the good dbase. So I have ticked that. when I restarted the problem dbase that seems to have fixed it all.

To be honest, I don't have much idea how the references work other than they are basically "add-ins", what is in which I don't know. But now I will check the problem dbase thoroughly, but that seems to have been the problem.

thanks for all of your help and guidance!

I was just about to post that suggestion, actually. A2013 screws up a bunch of references when it opens older databases, and usually does NOT enable the reference you found, likely because it replaces the DAO 3.6 library, and Access can't find a match based on similar name. Unfortunately, as you discovered, that one is needed to run many basic functions.

Also, if you try to re-open that file in an earlier version, you're going to need to update the references again. At the very least, you'll need to re-enable the DAO library.