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 7493 articles. 0 followers. Follow

3 Replies
1875 Views

Similar Articles

[PageSpeed] 23

"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:

count between a date range
Hi, I want to be able to count between a date range and return the value yes. I will show you the formula I currently have: =IF(M2>=DateCell-365,"Yes","No") This gives a yes if the date is greater than the date in M2 minus 365. This however cincludes all the dates beyond M2 which isnt what I want. I want the dates upto M2. If that makes sense? Any help would be greatly appreciated Thanks Try.. =IF(M2>=datecell-DATE(1,0,0),"Yes","No") -- Jacob (MVP - Excel) "Dave" wrote: > Hi, I want to be able to count be...

Help with Dates
Hi all, I have a table with field name "ConnectionDate". I want to create a form with ComboBox that will show the "ConnectionDate" as month and year only. The value the user will choose will be a criteria for a query. Please help. TIA, Tom ?Format(Date,"mmyyyy") would return 112007 so, SELECT Format(ConnectionDate,"mmyyyy") AS ConnectDate FROM SomeTable -- Dave Hargis, Microsoft Access MVP "Tom" wrote: > Hi all, > > I have a table with field name "ConnectionDate". > I want to create a form with ComboBox tha...

Converting Dates from YYMMDD to MMDDYYYY
Hi All; I'm looking for a format function that will convert a date in the text format of YYMMDD to text format of MMDDYYYY. Any help would be GREATLY appreciated! Thanks in advance! You can't do it using a Format function, but try: Function SwitchDateFormat(YYMMDD As String) As String Dim strDay As String Dim strMonth As String Dim strYear As String If Len(YYMMDD) = 6 Then strYear = Left$(YYMMDD, 2) strMonth = Mid$(YYMMDD, 3, 2) strDay = Right$(YYMMDD, 2) If strYear < "30" Then strYear = "19" & strYear Else strYear =...

Access Code Pushing Values
I have a customer database with [BillAddress] and [ShipAddress]. I am using a "yes/no" box titled[SameShipAddress?]. To automatically fill [ShipAddress] after checking the "yes/no" box I used this code in the after update event for the check box. If Me![SameShipAddress?] Then If IsNull(Me![BillAddress]) Then Else [ShipAddress] = [BillAddress] End If End If This works for the selected customer, but then pushes the entry [BillAddress] of the previous customer to [ShipAddress] of all of the following customers. Any thoughts? Sounds as...

Data source for PivotTable-Form in ACCESS 2000
Hi, this drives me crazy, 4 years ago I defined in an ACCESS 2000 application a "PivotTable-Form". The resulting EXCEL table inclusive the "Data refresh" works perfect. I now want to update/change the query for the "Data Source" but cannot find which query is behind the "PivotTable-Form" or behind the resulting EXCEL spreadsheet. When editing the properties for the "PivotTable-Form" or the EXCEL spreadsheet the "Data Source" is always blank. Question: Where does Access 2000 or EXCEL 2000 hide the respective data source (Query)...

access 2007 and custom toolbars in earlier version
Is there a way in Access 2007 (Enterprise) to modify a custom toolbar created in say Access 2002? In the help there are only topics about the quick access toolbar. I want to modify the action of a button, but right clicking and choosing customize, like used to be so easy (once you get used to it), is no longer possible. Does that mean no more custom toolbars and no backward compatibilty here? Help would be very welcome as the user friendliness of my user's experience is at stake here. ...

access 2003
I have 8 pages i need to link together to save data what is the best way to link them? tlenney, We can't see your *pages* or your tables so asking what is the best way to link will not yield you answers without a wee bit more information. By *pages* do you mean tabs or forms? What are your tables and how are they related to each other? Why would you need to link the data in order to save it? Or are you linking to an outside data source and you are trying to save that data within the database? -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been ...

Denied file access
When I attempt to open my money program, I get a message that says, There is a problem with your Windows security settings. I use Windows 98 and Money 2000. ...

Auditing Mailbox item access
Don't ask me why I am asking, you don't want to know....but Is it possible to monitor and log access not only to a mailbox, but actually to individual items within the mailbox and be able to trace it back to the specific item and user who accessed it? I am aware of the ability to log when a non-primary account logs into a mailbox, but that is not granular enough for the scenario I need to look into. The powers that be want to be able to see not only who accessed a particular mailbox, but actually who accessed a particular message within that mailbox. Just wanted to know if...

Automatic Data Entry Script OnChange Event for Date Field.
Hi, Sub: Automatic Data Entry Script OnChange Event for Date Field. has anyone assigned 'Today date'( new Date()) to a custom date filed on the form - OnChange of a Picklist field, I have the following code. DatefiledName.value = new Date(); This assigns, currect date to the field, but then when I save it does not like it. When I manually assign a date it displays in the format mm/dd/yyyy, and saves correctly, so I changed the script to: d= new Date(); s = d.(getMonth() +1); s += '/' + d.getDate(); s += '/' + d.getFullYear(); DatefiledName.value = s; This also acc...

Access 97 conversion to 2000 and OpenRecordSet
We have a simple application developed in Acees 95 / upgraded to 97 and now we wish to move to 2000. the application 'almost' upgrades itself apart from a problem we are havinng with OpenRecordSet . The code is simple and I know (with hindsight) it can be much neater but when you do not spend a great deal of time and it works then leave alone. Code throws up Run Time eror 13 at the OpenRecordset command when run. Have read lots of docs but confused as to change this seems a major problem. Have checked DAO 3.6 to get round other problems and they seem to work. Help appreciated Pub...

Program trying to access Contacts
I did a clean install of WinXP and OfficeXP. I use Incredimail for Outlook and Spam Daemon for Outlook and have never had a problem with them. I also did an Office Update this morning. Now whenever I click "new" message, I got a window that says "a program is trying to access the contacts folder" and suggests it may be a virus and gives me an option to permit access but only for 1 to 5 minutes, as a select. What is causing this problem and how do I get rid of it? [I have Norton AV and Zonealarm installed, and they have never caused this problem.] Arthur Broadh...

ACCESS 2000 is deleting records
Just yesterday I converted an Access 97 database to Access 2000. Have a large problem I need help with. After converting the DB I "split it" in order to use it as a backend, same way it was in Access97. After converting the database to Access2k we began to have a problem with Access2k deleting records. it deleted a total of 4 out of 62 records. the records are not together ( record #289365 then 289048 then 289128 then 289178 then this morning 289405) these record are auto-numbered incerement of one. They were entered by different people so I know its not just an operator probl...

OE removed access to unsafe attachments #2
How do I tell Outlook Express to consider some attachments safe and let me open them? Anyone know? Tools-> Options-> tab Security-> uncheck Do not allow... Note that this is an Microsoft Office Outlook forum. Outlook Express is family of Internet Explorer and Outlook of the Office family. Here is the link for the right forum http://communities.microsoft.com/newsgroups/default.asp?icp=InternetExplorer Good Luck! -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tip of the month: -Create your o...

Outlook Web Access #34
I have a problem reading mails from Russia usig OWA ? (it is all a lot of ?????? and maybe numbers). If I use a normal Outlook client there are no problems ? I have triet different regional settings but no luck ? Try posting this in an Exchange news group as OWA is a function of Exchange, not Outlook. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Lost asked: | I h...

number changes to date ... how do I change
I am trying to do an "age" range: 1-9 10-19 20-29 Instead I get September 9, 2004 or October 19 I can't figure out how to change it. Please help! Thank you! kk Format->Cells->Number Change the type to Text, and it should keep everything the way you want it. If you don't specify a type, Excel takes a guess based on what you enter. Hope that helps. -Bob --- Message posted from http://www.ExcelForum.com/ ...

Rich Text formatting in Access 2007
In Access 2007, is it possible to add rich text formatting to text in a memo field (or mixed formatting within any text field). I have just upgraded to the new version of Access, and it appears that this feature still has not been added. Is this correct? (If so, why?) If it is not possible to format individual words within a field in Access, , is there a simple, free add-in that will enable Access to do this kind of basic formatting, such as adding italic, bold, and underlining? Thanks very much for any information you may have. Bob Rich Text format for Memos in: Tables - Look in t...

BACKING UP ACCESS DATABASE 10-31-07
I'd like to backup my Access database automatically (no user intervention needed) to an external drive. Does Access allow such a backup while the database is "open" ? If I have to log out of Access, it defeats my purpose of wanting to do the backup without user intervention. There appear to be a number of drives and SW available to do the backup, but the question is whether Access will allow it. Basically I'd like to backup to the external drive every time a record is changed. I'm worried about losing my database if the PC crashes. If your database is that mission...

Automatic date/time aquisition for graphs
Ok, I collect particle count data for a cleanroom which looks like th following: "LASAIR110","04/05/19","15:47:41",2700.0,45.00,"V6.3",0,0,0,1,433,486,236,113,294,196,103,328,4.993,1.0000,0.000,0.000,0.000,0.000,0.000,"22de" "LASAIR110","04/05/19","16:33:15",2700.0,45.00,"V6.3",0,0,0,1,458,432,163,55,107,55,46,139,5.007,1.0000,0.000,0.000,0.000,0.000,0.000,2299 "LASAIR110","04/05/19","17:18:49",2700.0,45.00,"V6.3",0,0,0,1,305,286,76,29,14,1,0,0,5.017,1.0000,0.000...

VBA Help in Access 2007
When looking through the Help engine, either local or online, I get blank pages when I look at many detail pages in the Help file. For example, when looking at most all web pages in ADO (Section I: ActiveX Data Objects (ADO), there's one title line and no detail. Is that planned behavior (no data) or is there some setting or site to find information? I'm specifically looking for handling records in a table (read, process, summarize). Thanks Terryomsn =?Utf-8?B?VGVycnk=?= <Terry@discussions.microsoft.com> wrote in news:04199121-0746-4A15-B201-03F13827D29C@microso...

CRM data access from external website
Hi All, I have a fully working CRM 3.0 solution on a dedicated server on my internal network. This server is also running SQL 2005 I have an externally hosted website on a Windows 2003 Server, located on the internet. I would like to be able to let some of my customers access to certain parts of my CRM solution, so that they can see the status of an order. I have developed a website on the external server that uses ASP.NET linked to my Internal SQL server and allows ASP registered users access to certain pages. On these pages I would like to use a table that shows the information that...

Date Range
When I use the following macro, what reference range is being employed that makes certain decisions for the conversion? For example, when I have a date of 1/12/46, this converts to January 12, 1946, not January 12, 2046; when I have a date of 1/14/20, this converts to January 14, 2020, not January 14, 1920; when I have a date of 2/3//10 this converts to February 3, 2010 not February 3, 1910??? Seems like this was only project so far into the future. I haven't tried all numbers but once you reach that boundary it assigns the 20th century to any number there and above. Su...

Calculating Date Fields
I have a document that is filled with FormFields and users go from field to field filling the fields for a final document. I have been reading up on 'calculated dates' but have not seen a scenario like this. Three of my formfields are "Date1", "Date2" and "Date180." What I am trying to figure out is how to evaluate/compare Date1 and Date2 to see which is the earliest and then provide in Date180 the date 180 days from the earliest date. (Unless the form is blank there will always be a Date1, but there may not be a Date2 and if Date2 exist...

OUTLOOK WEB ACCESS #17
I recently applied the most current patches and fixes to my Exchange 2000 server. Since then, some of the users are getting attachments in only HTML format when accessing the server from off site through OUTLOOK WEB ACCESS. They need to be able to open them in word or at least notepad. What made the change and how do I get it back? ...

Need to convert Julian Day to Gregorian date
I have a table that has a year field and day field. I only need to convert the day field which is a Julian date to Gregorian. If possible in a query. If not then in a module, but, I'm real rusty with access so please give explicit instructions. I have found some codes but have not been able to make them work with the table, I'm sure its me. Thanks for any help you can provide me with. There are loads of examples out there. The simplist one I could find that works is SELECT DatePart("y",[TableName]![FieldName])+(Year(Now())-1900)*1000 AS NewDate FROM TableName; -- ...