• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 806
  • Last Modified:

Microsoft Access Australian dates turning into american date format

Hey Guys,
Really need your help with this one as iv been strugling to find the cause.

I use a script to insert a bunch of asset lets say from serial number 1600 - 1650 assigned to a single invoice and sor number.The script is as attached.

For example if I key the delivery date for a batch of 10 x systems, and the date is 8th January 2010. (08/01/2010 – dd/mm/yyyy)

It appears on the batch entry screen as 8th January.

If you then click OK, and view the data in the database, it mixes the date up, and puts the date as 1st August 2010 (01/08/2010 - dd/mm/yyyy).

If I key a date such as 13th January 2010 (13/01/2010 – dd/mm/yyyy) then it enters it correctly in the database, no worries.

Iv checked all the computer settings its set to Australian, Nothing has reference to american settings.Your help in this matter is much apreaciated.

Private Sub Generate_Click()
Dim i As Long
Dim sql As String
Dim strInvoice As String
Dim strSOR As String
Dim datDate As Date
 
On Error GoTo err

 
strInvoice = Me.txtInvoiceNumber
strSOR = Me.txtSorNumber
datDate = Me.txtDate
 
For i = Me.txtrange1 To Me.txtRange2
 
sql = "Insert Into computer(Serial,[Invoice Number],[SOR Number],[Invoice date]) VALUES ("
sql = sql & i & ","
sql = sql & Chr(34) & strInvoice & Chr(34) & ","
sql = sql & Chr(34) & strSOR & Chr(34) & ","
sql = sql & "#" & datDate & "#" & ")"
 
DoCmd.SetWarnings False
 
DoCmd.RunSQL sql
 
Next i
 
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious
err:
Me.txtInvoiceNumber = Null
Me.txtSorNumber = Null
Me.txtDate = Null
Me.txtrange1 = Null
Me.txtRange2 = Null
MsgBox "Batch Operation Completed.", vbInformation + vbOKOnly, "Batch Addition"




End Sub

Open in new window

PIC1.jpg
Pic2.jpg
0
Krisdeep
Asked:
Krisdeep
1 Solution
 
thenelsonCommented:
Access stores all dates as doubles as the number of days since 12/30/1899 12:00:00 AM  (Format(0,"mm/dd/yyyy hh:nn:ss AM/PM") = 12/30/1899 12:00:00 AM).  For example 09/11/2005 10:56:40 AM is stored as 38606.4560185185 or 38606.4560185185 days since 12/30/1899 12:00:00 AM  (CDbl(#09/11/2005 10:56:40 AM#) = 38606.4560185185).  You can input and display the date/time in any format you choose.  

Time without a date will default to 12/30/1899 since the integer portion of the double is zero.

Date without a time will default to Midnight since the fractional portion of the double is zero.

Also keep in mind that Access is an American product so VBA is sometimes expecting mm/dd/yyyy even when the regional setting is set for something else.  yyyy/mm/dd will always work for VBA input.

ONE DATETIME FIELD VS TWO FIELDS:
Many new Access developers will use two fields, one for the date, the other for the time to store an event. Since the Access DateTime field stores both date and time, it makes sense to use one field to store the date and time of an event. Then arises the question how to have a user input date and time to the field. The easiest way is to have a single text box for date and time. Set the format property to 'General Date' or create your own format. To force users to set both date and time, you can set an input mask (which will force them to use a specific format) or you can use this validation rule setting:
DateValue [Forms]![YourFormName]![YourControlName] > 0 And TimeValue([Forms]![YourFormName]![YourControlName]) > 0
The validation rule with input mask allows users to use any format but requires both date and time (or you can set a specific range for date and/or time). You can set the validation text to something like: "Date AND time required."

If you want separate date and time textboxes, it gets a little more complicated.  You would create three textboxes.  A hidden textbox bound to the DateTime field (let's call it txtDateTime) and two visible textboxes, one formatted for date (txtDate), one formatted for time (txtTime). To display the date and time, use in the form's current event:
Private Sub Form_Current()
txtDate = IIf(DateValue(txtDateTime), DateValue(txtDateTime), Null)
txtTime = IIf(TimeValue(txtDateTime), TimeValue(txtDateTime), Null)
End Sub

To set the date and time use: (you may want to add error handling to these samples)

Private Sub txtDate_AfterUpdate()
txtDateTime = txtDate + txtTime
End Sub

Private Sub txtTime_AfterUpdate()
txtDateTime = txtDate + txtTime
End Sub

If the DateTime field is required, the after update events above will require both date and time and you would add this form error event:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3314 Then
    If Len(txtDate & "") = 0 Or Len(txtTime & "") = 0 Then
        MsgBox "Date AND time are required"
        Response = acDataErrContinue
    End If
End If
End Sub

To allow either date, time or both use:

Private Sub txtDate_AfterUpdate()
txtDateTime = Nz(txtDate) + Nz(txtTime)
End Sub

Private Sub txtTime_AfterUpdate()
txtDateTime = Nz(txtDate) + Nz(txtTime)
End Sub


CONVERTING SECONDS, MINUTES OR HOURS TO DATETIME:
To display number of seconds, number of minutes or number of hours (including decimal) to hh:mm:ss
Remember DateTime is computed in days. A day has 86400 seconds, 1440 minutes or 24 hours so:
Format (NumberOfSeconds / 86400, "hh:nn:ss")
Format (NumberOfMinutes / 1440, "hh:nn:ss")
Format (NumberOfHours / 24, "hh:nn:ss")


DATE TIME FUNCTIONS:
Timer () Returns the number of seconds since midnight
Time ()  Returns the current time
Date ()  Returns the current date
Now ()   Returns the current date and time  

For more information see:
Functions for calculating and for displaying Date/Time values in Access: http://support.microsoft.com/?kbid=210604
0
 
JVWCCommented:
try this
datDate = dateserial(Me.txtDate)

JC
0
 
Gustav BrockCIOCommented:
First, you must read the date as to your local settings. DateValue does this.
Then, in SQL, a date must be converted to a string representation formatted either in the "reverse" US format or - preferred - the ISO format.

Thus:

datDate = DateValue(Me!txtDate)
strDate = Format(datDate, "yyyy\/mm\/dd")
 
For i = Me.txtrange1 To Me.txtRange2
 
sql = "Insert Into computer(Serial,[Invoice Number],[SOR Number],[Invoice date]) VALUES ("
sql = sql & i & ","
sql = sql & Chr(34) & strInvoice & Chr(34) & ","
sql = sql & Chr(34) & strSOR & Chr(34) & ","
sql = sql & "#" & strDate & "#" & ")"

/gustav
0
 
KrisdeepAuthor Commented:
Thks will try some of the solution and get back to you. Much apreaciated for your suggested solution in advance.
0
 
KrisdeepAuthor Commented:
Hey Cactus So sorry for the late reply, but thanks for your suggestion. I ended up reserving the dates within the coding.Thanks for your help
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now