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
1982 Views

Similar Articles

[PageSpeed] 52

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

Problem with Date Manipulation
Okay.. Im not a kid, 36, been working with excel for years & last year did up to intermediate courses in Access. However, that being said because you guys are very keen in here, so I dont want to sound stupid, whilst my understanding on alot of excel is generally on the layman terms! Heres my problem. Im making a simple spreadsheet. I have a widget that enters my facility on one date, it then exits another. I know by setting up EXIT - ENTER = DAYS I have my duration in the facility. BUT---> I dont want the weekends to count as days! Material arrives every weekday & exits the...

Export from Access
I built an Access database which to export the daliy (new) figures to Excel to the desktop. One of the queries is for pulling all the information for a Credit Manager (CM). I then need to link this data to a summary spreadsheet. So each day I want to go to my database, pick the CM's data, export and replace the old info with the new, and keep the links I have created when I exported the first one. When I exported the first, I linked it to a number of spreadsheets. Any thoughts? I get an error telling me that It cannot expand the named range. If I rename it then I would have to r...

Calculating totals by month from whole date
I'm trying to figure out the proper calculation to pull totals by dat in Excel. Here's my problem: I have a workbook with 2 worksheets; one is the main data, the other i the statistics from the main data. Within the main data, there is column titled "date paid" and another column titled "total paid" (ther are a bunch more columns, but they don't matter for this problem). Th "date paid" column will contain dates such as 1/1/04, 5/15/04 etc. O the stats page, I have columns titled for each month of the year an would like each to include how much was pa...

Looking up data from Access
I would like to extract data from MS Access into Excel '97 to populate single cell, preferably imitating the 'vlookup' function, i.e. give the value of an Excel cell, the related field name in the externa database query, and the field with the value to return, it wil populate the current cell with this value. Not sure how clear that is. Let me know if it isn't, and thanks fo any help you can give -- Message posted from http://www.ExcelForum.com I've found a solution using the SQL.Request workbook function, whic does what I need. However, I need to populate many cells...

Custom Reports Using MS Access?
Ran across a thread where Glen Adams suggested using MS Access to create custom reports instead of Crystal Reports. But Glenn never said where to point Access to obtain the needed information to create the reports. Glenn, if you are watching can you give us some more info on how to use Access to create a report? I'm not Glen... I think you would use ODBC... to connect to the RMS database. Then use Access to connect via ODBC... Marc Wagner www.gmroii.net "Dan" <anonymous@discussions.microsoft.com> wrote in message news:0df801c46ea7$d79b00b0$a601280a@phx.gbl... &g...

Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns: ColID - PRIMARY KEY Col1 - Number Col2 - Date/Time Next I created a multi-field index using Col1 & Col2. I entered the following row in the 1st row: 1 5/10/2007 Next when I tried to add the above row again, as expected, I wasn't allowed to enter the same row. Next I entered the following 2 rows: 1 5/11/2007 2 5/10/2007 Both the rows were accepted. After this I deleted the 3 records, went back to the design view & changed the data type of Col2 from Date/Time to Number keeping the multi-field index...

Saving data in Access table with VB ?
I have a form with a button and if you click the button, a list of invoices are generated and saved in the table 'Invoice'. Problem is, the data isnt saved :( Here's my code: Private Sub Knop0_Click() Dim Invoicenr As Long Dim Invoicedate As Date stdocname = "Invoice" DoCmd.OpenTable stdocname, acViewNormal, acAd Invoicenr = 111111 Invoicedate = Now DoCmd.Save acTable, stdocname End Sub What am I doing wrong ? Answered in microsoft.public.access "Bauhaus" <niemandhier@pandora.be> wrote in message news:7Exii.2418$yf6.1885@biebel.telenet-ops.be...

converting dates
Hi All I have a couple of excel problems to do with dates (Excel XP/2002). I have a formula that displays one date minus another and displays the answer as the number of days. Firstly, is it possible to convert the data to display as year and days (375 displays as 1 year and 10 days) as it displays as a decimal point when I divide by 365. Secondly does anyone know a formula that sorts out leap years (see above). Any help, suggestions or tutorial links greatly appreciated Rexmann As long as you assume 1 year is always 365 days =INT(A1/365)&" year(s) "&MOD(A1,365)&&q...

Can I display the current date in a text box?
I know how to display the current date in a cell, but can I display it in a text box? And how would I do that? You would have to have some code to load it, such as Textbox1.Text = Format(Date,"dd mmm yyyy") or link the texbox to a cell with the formula =TODAY() -- HTH RP (remove nothere from the email address if mailing direct) "stephiebrady" <stephiebrady@discussions.microsoft.com> wrote in message news:C78C4C78-C12C-4A8F-9121-E377ACAE3B5B@microsoft.com... > I know how to display the current date in a cell, but can I display it in a > text box? And ...

Web access for Exchange 2003
What do I have to do in order to access my Exchange 2003 via the Web? thanks, Raul Rego rrego@njpies.org On Thu, 6 Jan 2005 14:39:53 -0500, "Raul Rego" <rrego@njpies.org> wrote: >What do I have to do in order to access my Exchange 2003 via the Web? > >thanks, > >Raul Rego >rrego@njpies.org > Check that you can access the server from within the network by going to http://server/exchange and then http://server.fqdn.com/exchange If that works then simply opening TCP80 on the firewall and implementing whatever NAT or port forwarding your specific network ...

Database using Access 2.0
Our church purchased Church Windows software. When we asked if we could import our members from our existing database Greentree CDMS they said we couldn't because CW is an Access 2.0 database and we don't have that software. What would be the benefit of still using Access 2.0 for software development? Thanks, Linda Hi Linda I doubt there are many new programs being written in Access 2. It was a great piece of software, and many databases were writing using it. I don't know Church Windows, but I am guessing that it was written back in the Access 2 days (around 1994), ...

Center Access2007 Form in the middle of access Main Window
Thank You Is there any easy way to center access 2007 forms in the middle of the screen (Monitor) or access main window. and give me good result Hello, I think there is no built in feature to achieve this goal. You may need to create a new form and configure it as the main window. You could enumerate forms and create buttons etc to open forms from this main form. For example, you could use hte following code enumerate forms in your database: Dim db As Database Set db = CurrentDb Set cntContainer = db.Containers("Forms") For Each doc In cntContainer.Documents Str...

Use Datedif but for future dates
I have a formula for identifying years, months, days from a past date to now. DATEDIF(C6,NOW(),"y")&" Y, "& DATEDIF(C6,NOW(),"ym")& " M, " & DATEDIF(C6,NOW(),"md") & " D" I'd like a formula that can produce the same format (years, months, days) between now and a future date. Any ideas? Thanks in advance, Bart Hi Bart Try this with the date in A2 =IF(TODAY()<=A2,DATEDIF(TODAY(),A2,"Y") & " y " & DATEDIF(TODAY(),A2,"ym") & " m","-"&...

a program is trying to access e-mail addresses you Stored n Outlook
Hi - After installing Acrobat Standard 6.0, when I try to send an email message using Word or any other program, I get an Outlook message saying "a program is trying to access e-mail addresses you have stored in Outlook". The only choices I get are to allow access for one instance or for a short period of time. What I want to do is to ALWAYS allow Word, PowerPoint, Excel and ACT to access my Outlook e-mail addresses. I went to the Office Service Pack 3 page, which said "To prevent the PDFMaker COM Addin from loading, go to HKEY_LOCAL_MACHINE\Software\Microsoft\Offi...

Advanced Find should let me search records between two dates
When searching the system for records, many times I need to search for records that came in between two dates. For instance, I would like to be able to pull all records input in CRM between March 1 and March 15. In 3.0 you can only query specific dates like "Last X days," "Last X Months," "On," "On or After," etc., but you cannot search the date fields between two dates. The functionality was available in 1.2 but is not available in 3.0. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the...

Sort search results by date
It would be nice if i could sort my search results in this forum by date. I would like to see my most recent questions at the top. -- Sheri Salomone THANKS! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroup...

Denial of report access
I would like to deny access to specific reports thoughout CRM for a group of users. I have already created Security Group-Domain Local at the Active Directory and assigned some users. Afterwards I opened SQL Server Reporting Services, selected the report I didn't the group to access and assigned the new group with a new role (only task:View folders). At the end I deleted all the other groups except BUILTIN\Administrators and tried to enter as the acess denied user. Unfortunatelly it didn't work. What else should I need to do? Thank you in advance! ...

vbscript insert into access 2003 database with two different table
I am trying to insert data collected by WMI. Here is the script On Error Resume Next Const HKEY_CURRENT_USER = &H80000001 Const HKEY_LOCAL_MACHINE = &H80000002 Const ForReading = 1 'Create FSO Set objFSO = CreateObject("Scripting.FileSystemObject") 'Create an environment for the script to work Set wshshell = WScript.CreateObject("WScript.Shell") 'Connection to the database Set cnn = CreateObject("ADODB.Connection") 'Connection to a Recordset Set objRecordSet = CreateObject("ADODB.Recordset") 'Opens the Database ...

Accessing all questions I have asked in the Office forums
Is there a way I can call up all questions and answers I have ever asked via my login to Microsoft Discussion groups? I sure appreciate your help, John In news:FB996750-8ACF-4806-BDF8-7E66057A0DBB@microsoft.com, John <John@discussions.microsoft.com> typed: > Is there a way I can call up all questions and answers I > have ever asked via my login to Microsoft Discussion groups? > > I sure appreciate your help, John Use Google; look up your own name; finesse from there if you get too many hits. HTH, Twayne` ...

Outlook Web Access
I cannot open e-mails forwarded to me from several AOL users. OWA shows "The page cannot be displayed" message. I can open all other e-mails. Looking at the details of the message I find these content types: multipart/mixed, text/plain, message/rfc822, multipart/alternative, text/html, application/msword. text/html is the only one represented in the registry and is in Level2MIMETypes registry entry. Any help on what may be blocking these e- mails is greatly appreciated. ...

Access 2007-Table not displaying the values from a combo box
I have built a database to schedule senior/disabled transportation appointments. I have created two tables and associated forms for data entry. I placed a combo box in the Schedule form so end users choose a client and fields complete on the form with client name, address, city, and home phone number. I did instruct Access to store the data in the SeniorIntakeID field in the Schedule table. I wrote a report that staff execute to see what is scheduled for a given date, for each driver. On the report I want to see the actual client's name, address, city, and home phone n...

Auto insert of date
I'm trying to input some datas using preparedStatement and among those coiumns one column is of type "smalldatetime" i wish to input the current date and time at which i execute this query, how can i achieve this in JDBC or what is the sql query for this. I'm not sure if we're talking JET SQL or T-SQL or something else here? In JET SQL you want the Date() function, something like ... INSERT INTO Table1 ( TestDate ) SELECT Date() AS Expr1; If you're using T-SQL you want the GETDATE() function, something like ... INSERT INTO dbo.Table1 (Test...

how do i subtract one date from another and get an age in years
i am trying to subtract a date of birth from today's date and get an age in years. Can anyone help me? http://www.cpearson.com/excel/datedif.htm -- Kind Regards, Niek Otten Microsoft MVP - Excel "ucastores" <ucastores@discussions.microsoft.com> wrote in message news:FE331F27-C314-42D4-A390-9DA97E0E493D@microsoft.com... >i am trying to subtract a date of birth from today's date and get an age in > years. Can anyone help me? > Hi see: http://www.cpearson.com/excel/datedif.htm#Age -- Regards Frank Kabel Frankfurt, Germany "ucastores" &l...

Mail Merge with Access Report (with grouping levels)
Hi, I have two tables in Access 2003 with a one-to-many relationship, in an access report the grouping works, how do I make this work in a Word 2003 mail merge? The following are not my tables but an example of what I am trying to achieve, where the relationship is 1 to many from Orders to OrderItems: Table 1 - Orders OrderID Date SalesPerson Table 2 - OrderItems ItemID Description QTY Value *OrderID On the 1 Page word document I want to print display as follows This is your <Order ID> on <Date>. Your Salesperson was <Salesperson>. Here is a list of your Items: <...

Queries and reports in access Need help please
Hello everyone , I have a little issue to setup a database. I created a table with approximately 20 columns. The columns have an entry of Yes or no base on a questionnaire from survey. What can I do to get a result of my table I would like to know how many "Yes" and "no" I have. I don't know how to add them or get this information. Thanks in advance GABRIEL Gabriel, The first answer to your question is that your table structure is not really the best for this situation, and it will be a lot easier if you can set it up differently. Is your database design set in ...