Date format question - Regional Setting?

[Gary Dolliver]

Hi all,
I am running a mde file on multiple XP workstations with Access 2002. What
is really weird is on some machines, everything works well, but on some I get
some formatting errors in certain queries. For example, I have a query that
I am formating a long date to a short date, using the following line in the
query:
Date: format([Shipping],"Short Date")
I am getting a Microsoft Access error that states:

The expression On Click you entered as the event property setting produced
the following error: Function is not available in expressions query
expression 'Format([Shipping],"Short Date")'
* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure]
* There may have been an error evaluating the function, event, or macro

I am confused because the same file works on some machines, and not on
others - could this be a regional setting issue?
Any help would be greatly appreciated, thanks!
-gary

 

[KARL DEWEY]

Can you not use Date: format([Shipping],"mm/dd/yy")

 

[John W. Vinson]

The expression On Click you entered as the event property setting produced
the following error: Function is not available in expressions query
expression 'Format([Shipping],"Short Date")'
* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure]
* There may have been an error evaluating the function, event, or macro

I am confused because the same file works on some machines, and not on
others - could this be a regional setting issue?

This appears to be the very common References bug. Open any
module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.

John W. Vinson [MVP]

 

[Gary Dolliver]

Hi Karl,
Thanks for the reply. Unfortunately, I get the same error message when
running it on certain machines, except now instead of "Short Date", it now
says "mm/dd/yy".
-gary

Can you not use Date: format([Shipping],"mm/dd/yy")

--
KARL DEWEY
Build a little - Test a little

Hi all,
I am running a mde file on multiple XP workstations with Access 2002. What
is really weird is on some machines, everything works well, but on some I get
some formatting errors in certain queries. For example, I have a query that
I am formating a long date to a short date, using the following line in the
query:
Date: format([Shipping],"Short Date")
I am getting a Microsoft Access error that states:

The expression On Click you entered as the event property setting produced
the following error: Function is not available in expressions query
expression 'Format([Shipping],"Short Date")'
* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure]
* There may have been an error evaluating the function, event, or macro

I am confused because the same file works on some machines, and not on
others - could this be a regional setting issue?
Any help would be greatly appreciated, thanks!
-gary

 

[Gary Dolliver]

Hi John,
Thank you, that seemed to work. I am confused though, I thought by creating
an MDE file that all references would somehow work and not be missing - but
apparently not!
I opened the master file on one computer and all appeared well, and then
opening it on another, there were 2 missing references - strange! After
unchecking the missing references, it is now working. Also, it appears that
by converting it to either 2002 or 2003 it usually breaks a reference... does
this commonly occur?
Thanks again
-gary

The expression On Click you entered as the event property setting produced
the following error: Function is not available in expressions query
expression 'Format([Shipping],"Short Date")'
* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure]
* There may have been an error evaluating the function, event, or macro

I am confused because the same file works on some machines, and not on
others - could this be a regional setting issue?

This appears to be the very common References bug. Open any
module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.

John W. Vinson [MVP]

 

[Rick Brandt]

Hi John,
Thank you, that seemed to work. I am confused though, I thought by
creating an MDE file that all references would somehow work and not
be missing - but apparently not!
I opened the master file on one computer and all appeared well, and
then opening it on another, there were 2 missing references -
strange! After unchecking the missing references, it is now working.
Also, it appears that by converting it to either 2002 or 2003 it
usually breaks a reference... does this commonly occur?
Thanks again
-gary

Think of what a reference is. It is saying "This project needs to use code
in this external library on the user's PC". Well, if that library does not
exist on the user's PC there is nothing about being an MDE that magically
solves that problem.

That is why when developing applications that need to run on multiple PCs
every effort should be made to NOT add any references beyond the default
ones. When you absolutely must use an external library besides the defaults
you should use late binding. That does not require that you set a
reference.

 

[John W. Vinson]

Hi John,
Thank you, that seemed to work. I am confused though, I thought by creating
an MDE file that all references would somehow work and not be missing - but
apparently not!

Nope. If your database has a reference to Strange.DLL in K:\wierdpath\, and
you convert the database to a MDE and install it on another computer, it's
going to look in that computer in the path K:\wierdpath\ for Strange.DLL. It's
probably not going to find it! An annoying side effect is that if ANY
reference is missing, all *other* references seem to get messed up; builtin
functions like Date() and Format() suddenly don't work.

I opened the master file on one computer and all appeared well, and then
opening it on another, there were 2 missing references - strange! After
unchecking the missing references, it is now working. Also, it appears that
by converting it to either 2002 or 2003 it usually breaks a reference... does
this commonly occur?

Just be sure that you don't have any references set unless you know you need
them. There's a basic set:

Visual Basic for Applications
Microsoft Access x.xx Object Library
OLE Automation

and either Microsoft DAO x.xx Object Library or Microsoft ActiveX Data Objects
x.xx, usually the former.

John W. Vinson [MVP]

 

[Gary Dolliver]

Hi Rick,
thank you for the reply. that makes sense. I was thinking (there I go
again...) if the program was compiled, that all references and code would
work across any machine as they would now be a part of the MDE file - thanks
again!
-gary

 

[Gary Dolliver]

Hi John,
Thank you very much for the explanation, it makes perfect sense. By chance,
is there a specific .dll for each reference, and is there a way to bundle
these and then just copy them to every machine where we will be installing
the database to? Sounds like a long shot, but thought I would ask.
Also, it seemed to add a reference that I did not have checked or even
remember adding (I believe it was a components for windows XP?) and it did
not appear on one computer, but it did on the other.
thanks again!
-gary

 

[John W. Vinson]

Hi John,
Thank you very much for the explanation, it makes perfect sense. By chance,
is there a specific .dll for each reference, and is there a way to bundle
these and then just copy them to every machine where we will be installing
the database to? Sounds like a long shot, but thought I would ask.
Also, it seemed to add a reference that I did not have checked or even
remember adding (I believe it was a components for windows XP?) and it did
not appear on one computer, but it did on the other.

You would need legal permission to distribute the add-ins. As a rule, you will
find distributing an Access app MUCH easier if you don't have to worry about
addins; distributing the addins, installing them, making sure they don't
conflict with other software on the user's machine, etc. can be a monstrous
hassle.

What nonstandard references *are* you using, and why?

John W. Vinson [MVP]