Date format question - Regional Setting?

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

0
Utf
6/7/2007 10:35:01 PM
access.formscoding 7493 articles. 0 followers. Follow

7 Replies
1644 Views

Similar Articles

[PageSpeed] 14

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

-- 
KARL DEWEY
Build a little - Test a little


"Gary Dolliver" wrote:

> 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
> 
0
Utf
6/7/2007 11:35:00 PM
On Thu, 7 Jun 2007 16:35:00 -0700, KARL DEWEY
<KARLDEWEY@discussions.microsoft.com> wrote:

>> 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]
0
John
6/8/2007 12:00:40 AM
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

"John W. Vinson" wrote:

> On Thu, 7 Jun 2007 16:35:00 -0700, KARL DEWEY
> <KARLDEWEY@discussions.microsoft.com> wrote:
> 
> >> 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]
> 
0
Utf
6/8/2007 1:13:00 PM
Gary Dolliver wrote:
> 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.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com 


0
Rick
6/8/2007 4:15:29 PM
On Fri, 8 Jun 2007 06:13:00 -0700, Gary Dolliver
<GaryDolliver@discussions.microsoft.com> wrote:

>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]
0
John
6/8/2007 4:38:33 PM
Gary Dolliver wrote:
> 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

Well one reason that they don't do that (even if it was possible) is that you 
can very easily have libraries on your PC that you are properly licensed to 
*use* but not to *distribute*.  That would in fact be the case more often than 
not.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com 


0
Rick
6/8/2007 11:08:33 PM
On Fri, 8 Jun 2007 14:37:00 -0700, Gary Dolliver
<GaryDolliver@discussions.microsoft.com> wrote:

>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]
0
John
6/9/2007 12:52:30 AM
Reply:

Similar Artilces:

Import records w/ date fails
Does anyone know the correct format for date fields during import? I'm attempting to import a file into contacts and leads that have date fields, but I keep getting an error and the records will not import. I've tried variations of date formats like: 01/01/02 01/01/2002 20000101 Any help would be most appreciated... I know I'm missing something small... Thanks!! Thanks in advance!! I just figured it out. Dates have to be imported as YYYY- MM-DD format. >-----Original Message----- >Does anyone know the correct format for date fields during >import? I'm...

Animated GIF as wallpaper question
Is it possible to use an aniamted GIF as wallpaper on an XP system and if so how do set it up? I tryed selecting an animated GIF as wallpaper but XP said it could not find something about an aciv desktop something. TIA One way is to use the Active Desktop. Control Panel > Display Properties > Desktop tab > Customize Desktop button > Web tab and then click the New button to select the gif file. "kraut" <NewsGroupsPlease@NewsGroupsPlease.org> wrote in message news:8ohkj51b48obu0vm06u6dqc5l4dtqg47kg@4ax.com... : : : Is it possible to use an ...

Bar Chart Question Multiple Elements
Greetings excel people, I have the following data below and I want to create a bar chart fo the total # of yes answers, and the total # of no answers for eac category, for the current location. In addition, I need each tota displayed to its corresponding bar. How do I do this? Thanks for your needed help Location Answer Q1 Q2 Q3 Q4 TB YES 12 23 23 2 TB NO 34 33 4 -------- Message sent via www.excelforums.com Put the data in columns in Excel like you posted below. Highlight all the data (should be 6 columns and 3 rows) and either click the Chart icon or click on Insert in the toolbar...

"reply" question
I have a generic account which 3 people have access to that. (I have added that account for them through advance option in Services). however when they reply any email from that account, the sent email will store in their mailbox not on "sent items" in generic mailbox. Is there anyway to keep those replis in generic email "sent Items"? Thanks-Rob Check out "unisent" ?? from www.ivasoft.biz Rob wrote: > I have a generic account which 3 people have access to > that. (I have added that account for them through advance > option in Services). however w...

Day part of date
In B2 I have 27/11/08 In A2 I am trying to extract the day as ddd (Thu in this case) If I use =Day(B2) in A2 with the cell formatted as ddd I get Fri. I tested in another cell with =B2 formatted as ddd - dd mmm yyyy and I correctly get Thu - 27 Nov 2008. Why does the cell A2 result fail, and is there an easy way to do this? Sandy Since you've already formatted A2 to "ddd", use =B2 as the formula in A2. -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================...

Mail Merge and conditional formatting Word 2007
Need some Help.. My disconnects mainly pertain to the use of e-mail merge in Word 2007. The data file I need to use currently resides in an Excel spreadsheet because of the need for several formulas. I can merge most of the data successfully with the exception of: =B7 Multiple rows of data per e-mail. =B7 Cannot edit individual e-mails prior to completing/sending the merge. (This is possible with a regular letter merge, but cannot do this with e-mail merge) =B7 Merging conditional formatting icons (if this is even possible, I don't know) We're using OnMa...

Excel
Is it possible to convert day of year to date format (assuming all date are 2004) e.g. 001 to 01 January 2002 or 366 to 31 December 200 -- Message posted from http://www.ExcelForum.com Hi try =DATE(2004,1,your_value) Frank > Is it possible to convert day of year to date format (assuming all > dates are 2004) > e.g. 001 to 01 January 2002 > or 366 to 31 December 2004 > > > --- > Message posted from http://www.ExcelForum.com/ Thanks Frank this works perfectl -- Message posted from http://www.ExcelForum.com ...

RE: Question about numbers lined up against dates
This works below for 1 year BUT when I get more than 1 year it still just looking at months. ow can I change toe formula to look at years as well as months. TiA Mag()() mag A= #ID, B= Price, c = DueDAte, D= Qty, E= Numb Due formula in e2: =SUMPRODUCT(--(MONTH($C$2:C2)=MONTH(C2)*(--($A$2:A2=A2)))*($D$2:D2)) Copy the formula down, and multiply the result by the price in column f. Regards Peter "Mag()()" wrote: > Hey all, > Lets see if I can explain this one..... > > In Column "A" I have a series pf part number- IE 123, 442, ER45 and a > whole &g...

Format all comments made now and in future to be size 14
How do you format all comments made, in all workbooks, to be 14 Times New Roman? How do you do it before, not after... Check out the below by Debra Dalgleish http://www.contextures.com/xlcomments02.html#Default If this post helps click Yes --------------- Jacob Skaria "annc5411" wrote: > How do you format all comments made, in all workbooks, to be 14 Times New > Roman? How do you do it before, not after... ...

Chart Question #3
is it possible to create a chart, skipping columns or rows I need to creat a chart using data from every other column and another using data from about every 5th row Any help appreciated David Hopper I'm open to correction on this, but I believe the best way to do this (assuming you don't want to delete the in between data, and resort the columns somehow) would be to create a macro that copies the data you want into the right format (probably on another sheet) and make the chart from there. Something along the lines of: sub MyCopy() dim NewSheet as worksheet dim OldSheet as w...

Pivot Table border formatting and pivot chart formatting
I've haven't been able to eliminate the border formatting in a pivot table. Every time I change the page selection it defaults back to having borders. Does anyone know how to correct this? I've also been having the same problem with pivot charts. When I increase the width of a line in a chart and change the page selection it defaults back to the previous format. ...

Date/time range based calculations
I have a column of values associated with unique dates/times. I need to be able to perform different calculations on the column of values based on whether or not their associated unique dates/times are between a set of date/time ranges (upper and lower limits). What function should I be using to calculate column C based on the date/time ranges? Example: Column A Column B Column C Row 1 Start Time 03/01/2005 00:29:59 Row 2 End Time 03/01/2005 00:59:59 Row 3 Row 4 DATE/TIME VALUE ...

Outlook 2007 question
I installed outlook 2007 and now I try to customise it but I notice that the Tools/Opions and Tools/Customize menus are disabled. Why and how to enable them? thanks I notice now that the problem is more severe. I cannot reply to messages, save is not working. It seems that most of the functionality is disabled. I can receive emails but cannot answer them. Any clue? this is very bothering. Thanks! "YLY" wrote: > I installed outlook 2007 and now I try to customise it but I notice that the > Tools/Opions and Tools/Customize menus are disabled. Why and how to enable > t...

Place Cards using Business Card Format
Do you use the label size or business size category to prepare place cards. After the design is completed, I want to retain the design but be able to print 10 cards to a sheet and then be able to change information in each place card, i.e. name, table number, etc. You can design your cards then do a mail merge for the information if you put the information into a data base or a Publisher list. What version Publisher are you using? Help here Mail and catalog merge http://office.microsoft.com/en-us/publisher/CH062524751033.aspx -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msa...

Email Archiving
Hi, Doing some research on email archiving on Exchange Servers. Anyone has an idea whether Exchange 5.5 had some sort of email archiving functionality? Maybe through a SP? Thanks Nich In news:Osv6yGC7FHA.2264@TK2MSFTNGP11.phx.gbl, Nicholas Aquilina <nickaquilina@hotmail.com> typed: > Hi, > > Doing some research on email archiving on Exchange Servers. > > Anyone has an idea whether Exchange 5.5 had some sort of email > archiving functionality? Maybe through a SP? > > Thanks > > Nich No - E2k/2k3 have "message journaling" that can send a ...

Current month question
My data appears as such: 1/2/2004 It must stay in this format. The information I need is how many referrals were made only for the month of July using this information. Any ideas on how I might accomplish this? Hi Brandy try =SUMPRODUCT((TEXT(A2:A27,"mmm")="Jul")*1) OR =SUMPRODUCT(--(TEXT(A2:A27,"mmm")="Jul")) OR =SUMPRODUCT(--(TEXT(A2:A27,"mmm")=TEXT(NOW(),"mmm"))) where A2:A27 contains your dates Cheers JulieD "Brandy" <anonymous@discussions.microsoft.com> wrote in message news:7e2f01c4771f$8c7e1060$a50128...

Security Settings in P2007 server
which security settings allow a user the ability to create an Issue or risk for a project. I have created a new group, catagory and template and everything works except that they cannot create a new risk or issue on any of the projects they have access to. Any ideas? -- Sharktracker Can you tell us which of the SharePoint Security groups the users are showing up under? My guess is they are showing up under Readers ... - Web Administrators (Microsoft Office Project Server) DESCRIPTION: Users who have Manage Windows SharePoint Services permission in Microsoft Office P...

GP10 Security Task Id Question
When building a task id you must select the Product, Type & Series. Then in the bottom part of the scrollable area you select "operations" to be included into the task. However, what is meaning of the operations that are NOT checked? Are these operations not available to any user in the system? TIA TIA, Users that have only this task in their role will have access only the the checked operations, unchecked operations will be unavailable for those users. But if the user was a member of other roles or tasks that have shared operations checked, then the system will gain user...

Setting the window class and name in a CRichEdit derived class
I'm trying to set the window class and name for automated testing on a CRichEditView derived class. Unfortunately the name appears in the richedit control as text and the name is not set. This technique worked on a CView derived class. Can anybody help? BOOL CLogView::PreCreateWindow(CREATESTRUCT& cs) { // Call base class PreCreateWindow to get the cs.lpszClass filled // in with the MFC default class name if( !CRichEditView::PreCreateWindow(cs) ) return FALSE; // Register the window class if a new one is defined. if (!m_csClass.IsEmpty()) ...

Column Chart Conditional Formatting
I have a column chart that compares 2 y values against a common x value. I want to compare the second y value to the first, if it is greater then make that bar green, if less then red, if same grey. Any ideas on how to go about this? Thanks Adam Bush You'll need to do this by manipulating the actual data. Setup 3 dummy series, first with a static value of 10. On the chart, format these green, red, and grey respectively. Now, go back and change the static values to formulas similar to this: =IF(B2>A2,B2,NA()) =IF(B2<A2,B2,NA()) =IF(B2=A2,B2,NA()) If ...

Set all folders to AutoArchive
All the Outlook folders on my system are set to "Do not archive items in this folder". Since I have hundreds of folders it is a tedious process going to each folder and changing the Properties|AutoArchive setting to "Archive items in this folder using the default settings". Is there a way to set all the folders to "Archive items in this folder using the default settings" without having to do each one separately? Also, is there a way to set up Outlook so all new folders are created with the AutoArchive set to "Archive items in this folder using the d...

Writing to a text file formatted as XML
Hi! I have a text file that looks like this: <configuration> <appSettings> <add key="db_server" value="someserver" /> <add key="db_database" value="somedatabase" /> </appSettings> </configuration> Now, I want to check if value for db_server is empty and if it is, I want to write the value. I am trying to use XmlTextWriter and XmlTextReader but being new to XML processing I don't know exactly how to do it. Can anyone help please? Regards, Kumar The easiest way would be to load the document in Xml...

Broadcase a setting change event?
I'm trying to change the editor type programatically by changing the following registry value. HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Outlook\Options\Mail\<Edito rPreference> Outlook doesnt pick up on the change until I restart outlook. Is there any message I can send so that outlook will refresh it's settings and re-read this setting? Outlook only reads many settings when it starts up. Blocked Level 1 attachments are just another example. -- Ken Slovak [MVP - Outlook] http://www.slovaktech.com Author: Absolute Beginners Guide to Microsoft Office Outlook 2003 Rem...

Event Error Question
Can anyone tell me what the foioloing event eree is and if there is a way to prevent it? Event filter with query "SELECT * FROM __InstanceModificationEvent WITHIN 60 WHERE TargetInstance ISA "Win32_Processor" AND TargetInstance.LoadPercentage > 99" could not be reactivated in namespace "//./root/CIMV2" because of error 0x80041003. Events cannot be delivered through this filter until the problem is corrected. I google the event adn got several different opinions, one being caused by root worms?? James ...

Setting up Message rules
I have read a few other post about setting up message rules, but I still can't get my messages to go into the folders I have set up. Here is what I have done. In message rules I have selected the Where the from line Contains -email-. Move it to -set- folder. I then go to apply now. I change "Storage Folders" to "Inbox" and then select Apply Now. Am I doing something wrong? This is for my work emails. I receive lots of emails from a couple different email address, and they are all POP3. Any help to get my folders set up would be great!! THANKS!! ...