"Format" function in query in Access2000 not working in Access2002

[ag90fox]

I am doing a small database for my wife's boss in his two person company. I developed the database using Access 2000 and upon moving the mdb file to his computer which has Access 2002, the "format" function in several of my report queries (ex. <b>EndMonth: Format([EndDate],"mmmm yyyy"</b>) no longer works? Any insight into why, and the best way around it?

I need it as the report form that launches the report provides the month to the query in the "criteria" row of the query in the format "mmmm yyyy".

Thanks for your help in advance!

Joe

 

[THWatson]

Joe
I don't quite understand the last part of your expression.
<b>EndMonth: Format([EndDate],"mmmm yyyy"</b>)

What is the </b> that follows "mmmm yyyy"?

<b>EndMonth: Format([EndDate],"mmmm yyyy") works at my end, but not with the </b> added.

Tom

 

[THWatson]

Joe
I haven't used Access 2002, but have used Access 2003, and there doesn't appear to be any difference between Access 2000 and 2003 in the Format function. So I doubt there would be a difference with 2002.

Tom

 

[ag90fox]

SORRY!!!) I though html might work and help highlight the specific text there. The "<b>&</b>" stuff was supposed to be 'bold-on' and 'bold-off'.

 

[THWatson]

Joe
Oh, now I get it. So the expression in the query is
EndMonth: Format([EndDate],"mmmm yyyy")

There is no reason evident to me that would keep that from working.

Tom

 

[JunglesMcBeef]

Gday,

I get the same problem, but only sometimes! Sometimes it works and sometimes it says it can't find the library for it. There is definitely some sort of conflict with access 2000 and 2002. I have not yet worked out why this happens but it has happened a lot so it is only a matter of time before i can narrow it down to why this happens. Maybe it has something to do with updating Office 2002? I updated 2002 on a machine that used a function with the format method in it with no problems, until after the update to SP3 and one other update. This is just something I thought of while typing this post, but if anyone would like to test this, it could be worth a try.

I look forward to hearing more on this.

 

[THWatson]

I'm not clear on what is happening here...and why.

Is it possible that a reference isn't checked in the VBE References? The "missing library" suggests that.

I have Access 2003 on my laptop. With the exception of having to change a HasData expression, it works fine on Access 2000 files. And without any difficulty at all in Format functions and I have plenty of those.

If I come across anything else I'll post back.

Tom

 

[JunglesMcBeef]

None of the references were missing, but they may change with the different workstations maybe? I had a slight feeling that the libraries may be different although the references don't change. The project I had built worked fine with the Format function (on the Office 2002 workstation) until I added the Flexgrid library while working on it in 2000, and updated the office 2002 to the most current updates. Then when I tried using the project in 2002 again the format function was not supported (also because I didn't have vs6.0 tools installed on machine, it didn't have the flexgrid reference or library, but that's in a different thread altogether and can be viewed here - thread707-844632 ). Still no closer to working out why though.

 

[THWatson]

I'm assuming that even though you are working in Access 2002, you are using the 2000 file format (the default). Correct?

There's a document on the Microsoft Knowledge Base...I don't have my hands on it right now...but it is entitled something like "Things To Do When You Upgrade To Acces 2003" so I'm wondering if there is a similar document for Access 2002.

As I indicated, the only problem I encountered was with the HasData function. It checks for empty subreports. Acces 2003 requires the following...
=IIf([rptAcctSummarySub].[Report].[HasData]=-1,[rptAcctSummarySub].[Report]![Text35],0)

I had originally built the expression without the [Report] in the latter part of it, and that works fine in Access 2000 but not in Access 2003. Took me a little time to find that. Access 2000 accepts the expression either way.

Some of these things take a while to track down.

Tom

 

[JunglesMcBeef]

Ok, this web page helped me to get everything working again with 2002, even though it is explaining from a 97-2000 point of view -

http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

. Just for your information Tom, I originally created the file with 2000, not 2002, I am using the 2000 file format. This is an excellent resource, I encourage everyone to have a look at it.

 

[THWatson]

JunglesMcBeef
Thanks for the reference. I have bookmarked it and will look thoroughly at it.

As for the HasData expression to which I referred, the db I had was also created in Access 2000, and I maintained the 2000 format. The interesting thing was that the expression continuned to work fine on a computer running Access 2000, but required [Report] in the expression when running on the computer that uses Access 2003.

Tom