Australian dates in access

Hey all,

I have a control box in my form that asks the user to enter a date.  It then 
takes the date and turns it into the default date so that it will show up on 
the next record.  It accepts the date and saves it to the table in the 
correct format (dd/mm/yyyy) but then when it copies it over to the next form 
it converts the date into american format (mm/dd/yyyy).  I have worked out it 
must be something to do with the code.  This is what I currently have in the 
relevant section of coding.

Private Sub DateFirstSession_AfterUpdate()
DateFirstSession.DefaultValue = "#" & DateFirstSession.Value & "#"
End Sub

Any assistance as to how I should code it so that it accepts the correct 
date format would be greatly appreciated.  I have my regional settings in 
Windows correctly set to Australia and am using Access 2003.

-- 
cheers
0
Utf
6/6/2010 12:25:30 PM
access.formscoding 7494 articles. 0 followers. Follow

3 Replies
1285 Views

Similar Articles

[PageSpeed] 6

"There once was a tall man from Perth..."

Sorry! Don't remember the rest of the limerick! But there is, in fact, a tall
gentleman from Perth named Allen Browne, considered by many to be the biggest
Access Guru in the galaxy! He has a small white papar that discusses the use
of non-USA date formats, and might be of interest to you:

http://www.allenbrowne.com/ser-36.html 

For this particular problem, you might try formatting the date in the
DefaultValue assignment itself:

Private Sub DateFirstSession_AfterUpdate()
  DateFirstSession.DefaultValue = "#" & Format(DateFirstSession.Value,
"dd/mm/yyyy") & "#"
End Sub

or maybe

Private Sub DateFirstSession_AfterUpdate()
  DateFirstSession.DefaultValue = Format("#" & DateFirstSession.Value & "#",
"dd/mm/yyyy")
End Sub

Format outputs as string or text, but Access is ususally pretty tolerant in
this regard, i.e. if it looks like a Date, even if it's s string, Access will
accept it as a Date. Strings that look like dates, for example, can be used
accurately in the DateAdd() and DateDiff() functions

-- 
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201006/1

0
Linq
6/6/2010 12:59:34 PM
The DefaultValue property is always a string expression regardless of the
date type, so should be wrapped in quotes characters:

DateFirstSession.DefaultValue = """" & DateFirstSession.Value & """"

The pairs of contiguous quotes within the delimiting quotes characters are
each interpreted as a single literal quotes character.  When you use the #
date delimiter in this context it assumes a US or otherwise internationally
unambiguous format, so it takes the date as a string expression in the format
dd/mm/yyyy and, because the US short date format is mm/dd/yyyy, transposes
the months and days provided that the result is a legitimate date.

Ken Sheridan
Stafford, England

MR EDDD wrote:
>Hey all,
>
>I have a control box in my form that asks the user to enter a date.  It then 
>takes the date and turns it into the default date so that it will show up on 
>the next record.  It accepts the date and saves it to the table in the 
>correct format (dd/mm/yyyy) but then when it copies it over to the next form 
>it converts the date into american format (mm/dd/yyyy).  I have worked out it 
>must be something to do with the code.  This is what I currently have in the 
>relevant section of coding.
>
>Private Sub DateFirstSession_AfterUpdate()
>DateFirstSession.DefaultValue = "#" & DateFirstSession.Value & "#"
>End Sub
>
>Any assistance as to how I should code it so that it accepts the correct 
>date format would be greatly appreciated.  I have my regional settings in 
>Windows correctly set to Australia and am using Access 2003.
>

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201006/1

0
KenSheridan
6/6/2010 1:36:47 PM
MR EDDD wrote:
>I have a control box in my form that asks the user to enter a date.  It then 
>takes the date and turns it into the default date so that it will show up on 
>the next record.  It accepts the date and saves it to the table in the 
>correct format (dd/mm/yyyy) but then when it copies it over to the next form 
>it converts the date into american format (mm/dd/yyyy).  I have worked out it 
>must be something to do with the code.  This is what I currently have in the 
>relevant section of coding.
>
>Private Sub DateFirstSession_AfterUpdate()
>DateFirstSession.DefaultValue = "#" & DateFirstSession.Value & "#"
>End Sub
>
>Any assistance as to how I should code it so that it accepts the correct 
>date format would be greatly appreciated.  I have my regional settings in 
>Windows correctly set to Australia and am using Access 2003.


When Access evaluates the expression in the DefaultValue
string, the date in the # signs must be either in USA style
or an unambiguous style (eg. yyyy-mm-dd)  I prefer the
latter just because it is unambiguous.  Your code should be
more like:
DateFirstSession.DefaultValue=Format(DateFirstSession,"\#yyyy-m-d\#")

Note:  If you use / as the separator, then Access will
translate that to the separator specified in your Windows
local settings which may not be a legal separator.  In this
case the Format would need to be:
Format(dt,"\#yyyy\/m\/d\#")

Note:  The Windows local settings will be used to display a
date whenever you do not specify the Format property in
whatever displays the date.  That means that you can not
look at a date in a table/querys/form/report and tell
anything about whether it is "correct" or not.

Note:  Access will also use the Windows local settings
whenever it it needs to convert a string that looks like a
date to an actual date value.  You can use the CDate
function to do that explicitly or more implicitly when you
specify a date type Format in a text box's Format property,
even if the Format property is different style than the
Windows setting (this is very confusing to me).

The bottom line is your code should always specify a date
string in the unambiguous date style enclosed in # signs.

-- 
Marsh
MVP [MS Access]
0
Marshall
6/6/2010 3:41:41 PM
Reply:

Similar Artilces:

Date Conversion #3
Hello Ladies & Gentlemen, I use comma delimited files which I have parsed the data from. The problem is the date format is not your normal format... For instance Column A has dates in this format ---> 20031027 or ---> yyyymmdd (Notice there are no separators) Is there a way to have Excel convert these dates automatically for me to a mm/dd/yyyy format? (With Separators) Any help would be very appreciated... Thanks! J.D. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.E...

Tieing Lot Numbers to Manufacture Date
Hello, I work for a feed company that has just gotten through our first six months of using GP 9.0. We purchase GP through a company that has added some features on top of GP for feed orders. However, as far as I can tell, lot numbers and inventory are still a GP function, so I am going to post a question here because the company we purchased GP from was not must help to me. Now that we have been producing for a long enough, I would like to be able to generate a list of products that there manufacturing date is over 6 months old and have there lot number connected to the product. I ha...

Australian GST/BAS
How do I record the GST Implication on transactions involved on Bank Transactions Entry, Bank Reconile Adjustments screens, or when I perform a transaction using Miscellaneous cheques in GP 9. My understanding is that GST does not work in those areas. If it does not work, then how do I record the GST implication? Note: that in most instances items such as Fees and bank charges are GST free; there are instances such as credit card merchant fees which attract GST. Hi Andrew You need to enter transactions that will end up in either GL Transaction Entry with a Tax Detail associated or i...

Date on which a Case is resolved ?
Hello people, I am pretty sure this is a stupid question, but I don't seem to be able to find the solution: I am trying to make a list of Cases that have been resolved since a certain date, using advanced find. I don't seem to find a date field that contains the date on which the Case was resolved. Does this not exist or am I missing something else ? Many thanks in advance ! Regards, Mav. On Sep 3, 12:30=A0am, EuroMaverick <EuroMaver...@discussions.microsoft.com> wrote: > Hello people, > > I am pretty sure this is a stupid question, but I don't seem to be ab...

Word inserts incorrect date
Here's my problem: When I begin to type the date in a word document, Word completes the date, but it is two days off of the correct date. For instance, today is, November 5, 2005. Yet Word completes the date as November 3, 2005. It only happens in Microsoft Word. I have checked my Mac preferences and the date is correct there. Any ideas? Have you relaunched Word recently? All the Office programs take the date from the OS at startup. So try quitting and restarting. On 11/5/05 11:28 AM, "keelime" wrote: > Here's my problem: When I begin to type the date in a wo...

A Program is trying to access e-mail addresses you have stored in Outlook.
I am getting constant repeats of this warning after upgrading from Outlook that came with Office XP to Outlook 2003. I am using Outlook 2003 with Exchange 5.5. I was getting a few of these messages mainly associated with the use of Microsoft Team Folders before, but now it seems like Outlook itself is producing this messages every time a new email is received. I have scanned for viruses, and I have created a clean Windows XP Pro System with no other programs installed, and I still get the popup many times per day. How can I determine what program is triggering this message? Is this informati...

when costumizing Access 2003 toolbars how to use access2007 icones
Hi. Um my access 2003 databases I have some toolbars created by me. In those I would like to use the access2007 icones. Is it possible?? Regards, Marco ...

Last Download Date Wrong
On the download statements screen I have a message that says the last download was 10/03/2004. That date stays there after a currnt download. Just think what you could do if it actually downloaded the 10/3/04 quotes today.! Any suggestion on how to correct the date? What's your system date set to? "Shorty" <hankxxxx@hankmack.com> wrote in message news:402285e1_4@127.0.0.1... > On the download statements screen I have a message that says the last > download was 10/03/2004. That date stays there after a currnt download. > > Just think what you could do if it...

Year to date budgets?
I am trying to use Money 2007's budget summary. Here's my problem: Let's say that I budget $100/month for my electric bill. I pay the Jan bill on Feb 1st, and the Feb bill on Feb 28th. The budget summary for last month (Jan) says that I am under-budget by $100; this month's budget summary says that I am over by $100. The current year summary says that I have spent $200 of my $1200 budget, and that I am under-budget for the year. Why is there no YTD option??? YTD I am ON budget!!! -- Kevin M While I don't recommend using it, the Advanced Budget has an option to the ...

How to share Access Database in local area network
Dear Sir, I created a DB using Access 2003 and still working on it. how can i use my DB by many users in the same time with out locking the Database. when i did before, an error message appears every time from the other comuter showing that can not creat lock file!! could you please show me the appropiate way of sharing the DB file? Each user requires at least Read, Write and eXecute permission on the folder where the MDB (or MDE) file is located. (Delete is usually recommended as well, but isn't 100% necessary: not having it will mean that the location file--the .LDB file--will no...

Can't find Australian Banks Online
On the list of financial institutions I can only find American banks. I also have Australian accounts that I would like to add. Could you please advise if this is possible? I am using Microsoft Money 2004. Thanks, Jason You can add foreign banks to money by selecting the not held at a financial instution option when setting up the accounts. The drawback to this is that you cannot link foreign banks to online services and you probably won't be able to download statements from their website because most foreign banks (with the exception of canada) don't support money. This ...

Evaluate Receipt Date in receipt
Is there a way to evaluate a receipt date inside of a CONDITION tag? I want to do <= and >= I've tried and it seems like I cant evaluate TransactionDate. (Yes, I am using >= and <=) Thanks.... ...

How to carry over date value from one record to the next.
I have searched this site and the internet in search of an answer to this problem but none of the solutions I've found have seemed to help. I have a form that is used for data entry. The first two text boxes are called Supervisor and W/E Date. These two values will remain the same for several dozen entries. I have tried just using CTRL + ' to repeat the data but that is getting quite time consuming as well. How do I automatically carry over the value to the next record once I input the first data in? Any help on this would be much appreciated. -- Message posted via AccessMo...

Find date
Hi All, I have two ranges of dates, both one's width is one column in which dates are created by =DATE() functions. In the first range cells are formatted like "yyyy.mm.dd", in the 2nd one like "mmmm d.". In a macro I have a date variable DateToFind created by a DateSerial() function. I used Range("first range").Find(What:=DateToFind ).Row > 0 to check if DateToFind is included in first range and it worked. For the second range it gave a Type mismatch error, and i had to modify the Find line like this to make it work: Range("first ...

Outlook Today Date format Question?
Hi I'm running outlook xp on xp pro. This is a strange one that has troubled be for some time and I hope anyone can help? The issue is with Outlook Today Main page? If you look at your Outlook Today Main page in Standard view, you can see the date at the top of the screen. At the far right you have "Customise Outlook Today" and the date I mean is over to the left. How can I change the format that the date is displayed? On a duplicate machine the format is "Wednesday, July, 30, 2003" But on my machine its displayed as "30 July 2003" I would really like the d...

Date Format #24
How can I get Excel (2003) to display a date as YYYYMMDD with no hyphens, periods or dashes between the YYYY the MM and the DD? I still need the cell to be recognized as a date. Thanks FORMAT / CELLS / NUMBER / CUSTOM yyyymmdd Hope this helped, Gary Brown "Adam1 Chicago" wrote: > How can I get Excel (2003) to display a date as YYYYMMDD with no hyphens, > periods or dashes between the YYYY the MM and the DD? > > I still need the cell to be recognized as a date. > > Thanks format=>Cells=>Number Tab, select custom and enter yyyymmdd -- Regards, Tom...

rouge exchange admin??? logging unauthorized mailbox access
We are trying to determine if a rouge admin is logging on to mailboxes and actually reading inbox messages. We are getting the 1016's but we know that these could be caused by calendar access but we are also seeing 1009 events from this admin accessing other mailboxes. Does the 1009 event confirm that the mailbox was completely opened or does it mean the same thing as the 1016. Thanks... See if this helps: http://support.microsoft.com/kb/260835 AM "marr-ta" <mesthay@hampton.gov> wrote in message news:1168446968.391752.213410@i56g2000hsf.googlegroups.com... > We are...

Opening another Access Database file with VBA
I am in one of my Access database files, and wish to use a command button to open another database file. How is this done? On Sat, 9 Jan 2010 21:00:01 -0800, VWP1 <VWP1@discussions.microsoft.com> wrote: There are several ways. Perhaps the simplest is using the Shell function. Check the help file. -Tom. Microsoft Access MVP >I am in one of my Access database files, and wish to use a command button to >open another database file. How is this done? On Sat, 9 Jan 2010 21:00:01 -0800, VWP1 wrote: > I am in one of my Access database files, and wish to use ...

Inventory Dates #2
If I am to add all the shipments for all different equipment types by receipient and other criteria, eg =SUMIFS(RECORDS!TOTALS,RECORDS!MONTH,$A$2,RECORDS!EQUIPMENTTYPE,C$2,RECORDS!RECIPIENT,$A4) This formula is validating the receipient, the equipment type and even the month it was shipped. Adding totals of all shipment by item description. Our inventory starts on the 22nd of each month and ends on the 21st of the following month. Up to this point, I had to add a column to specify what month's inventory the shipment must be applicable to, otherwise is considering the month to start ...

access to contacts in email
My boss (of all people) cannot see his contacts when he clicks on the 'TO:' at the new email message screen. It's a brand new machine with Windows XP Pro & Office Pro. When you go into 'Contacts' all the contacts and their email addresses are there, but, he can't access them at the TO: button, or, at the Address Book Icon. I checked the filter, it's off, don't know what else to do. Thanks ...

Australian GST
I am trying to find out if there is any 'fix' or upgrade available to Money that includes the Australian GST tax. I find Money easy to use, but it's no good for my accountant without the GST shown. Any feedback would help. Thanks Suzs Sorry no. No sign of any new non-US or non-Japanese versions. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically asked for. "Suzs" <Suzs@discussions.microsoft.com> wrote in message news...

enter days and dates
I've used Fill handle to add all the dates of 2009 in cloumn A. 1) How do I enter all the days (Mon, Tue, etc) in Column B based on column A? 2) What to do if I want only the weekdays in column A? Thanks for the help, 1) =A1, format as ddd 2) format as ddd -- Kind regards, Niek Otten Microsoft MVP - Excel "cpliu" <spamfreeliu@yahoo.com> wrote in message news:edcc7e34-edd1-4718-b2a5-1d758546d8ee@a26g2000prf.googlegroups.com... > I've used Fill handle to add all the dates of 2009 in cloumn A. > 1) How do I enter all the days (Mon, Tue, etc) in Column B based...

Automatically send email based on date value
Is there way to send an email automatically based on a date field in a table? Some more information about what I am trying to do. I have a form where users enter in upcoming bids. I would like to send a reminder email out some specified time (ie, 14 days, 7 days, and again 3 days) prior to the bid being due based on the Bid due date field in the table. I don't want to have to go into the form and hit a button, I would just want it to review the table perhaps every time the database is open or even when the form is opened and send the email. The email only needs to go to one person, so ...

Dates in cells #2
When I start a spreadsheet my first step is to format the columns and the date format is selected. Occasionally when entering dates they do not follow the format for that column or other dates in that column. Am I doing something wrong? Anyone else have this problem. Ralphael, the OLD one Hi Ralphael1, You may want to create an excel template for your date format. Just type this on the cell "=NOW( )" format the cell you prefer. "Ralphael1" wrote: > When I start a spreadsheet my first step is to format the columns and > the date format is selected. > Occasiona...

odbc access driver
Hi everyone, I'm going to develop an mfc based application that uses ODBC and mdb MS Access files. The target platform is WinXP or better. My question considers the redistributable application. Do I need to include in my setup 'mdac_typ.exe' (to install MS access drivers for odbc)? Or any part of MS Access driver? As far as I know this driver is 'buildin' in WinXP and Win2003. I developing an application in VS.Net 2003 prof. I hope you give me an idea. Regards, KrystianZ ...