Format and Format$ Functions Not Recognized

[E. L. Wimett]

I have a well-established and stable database just converted and now find
that the format$ function to concatenate a label with a formatted date
(e.g., Meeting of Monday, 14 January, 2004) no longer works.

I can get the concatenation to work with the default format for dates in my
region, but otherwise it will no longer concatenate.

No problem, I thought, as I can do a query and format the dates and times
with no problem in the query and reference the expressions on the reports.

However, I find that in both Access XP and Access 2003 I am getting an error
message that format and format$ are undefined expressions, even when I
simply cut and paste a generic date exemplar from the help screens, for
example = Format(Date(), "dddd, mmm d yyyy").

Help!!! I've got a very time-sensitive project here and left the tweaking on
these reports to the end because they have been so stable. The hard stuff
was easy and things I've been doing since Access was Cirrus are suddenly not
working!!!

E. L. Wimett
Director of Information Systems and Museum Collections
Patriots Point Development Authority

 

[Douglas J. Steele]

Your references are probably messed up.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

 

[E. L. Wimett]

The problem is that none of the (very useful) suggestions from Mr. Steele
really applies.

These functions are suddenly having problems in reports (giving simply the
rather unuseful Error marker) and, the workarounds in queries by example,
with the statement that the function is undefined.

This is happening even if I create a completely new database with completely
new variables and create the form field or query calc using the Access
builder and selecting from the provided built-in functions. NONE of the
items involves defined functions, code behind forms or reports or anything
similar.

It is driving me crazy since I have never seen anything like it previously.
(And, since you may wonder, the only thing in common on the machines in
question is operating system since this is happening on both Access XP and
Access 2003 machines. . .)

E. L. Wimett

 

[Douglas J. Steele]

It doesn't matter whether you've got code-behind-forms, user-defined
functions or any of that sort of thing.

Broken references can occur even in databases that don't have any code, and
it will impact your ability to use the built-in functions.

If you don't have an existing module to open in order to get to the
References dialog under the Tools option, temporarily create one. You don't
even need to save the module once you've corrected the References problem.