RobMike
asked on
Dates in Database
I am trying to take the Date from a computer and place it in a MS Access database. The problem for example is when the date is 04/05/2000 it is saved as 05/04/2000. What can I do?
you can specify a custom format for your date field inside access to ensure it comes out the way you want
e.g. mm/dd/yyyy
e.g. mm/dd/yyyy
Internally the date is stored as a LONG. The display format is independent of the internal format. The Locale settings will change things around. If you want to display it in a specific format try:
text1.text = format(rs!datefield, "yyyy-mmm-dd")
M
text1.text = format(rs!datefield, "yyyy-mmm-dd")
M
You can use the CDate function when appending the data to an Access table via your Append query. Wrapping the Format function with the CDate function may seem like overkill but it's also good insurance:
CDate(Format(TheDate, "mm/dd/yyyy"))
Mike.
CDate(Format(TheDate, "mm/dd/yyyy"))
Mike.
Hi
Set ur Regional Setting as per ur need e.g. dd/mm/yyyy OR mm/dd/yyyy. Then in your Access Database set the fields i.e. ur data field format as per ur need e.g. dd\/mm\/yyyy OR mm\/dd\/yyyy.
Good Luck.
Jay
Set ur Regional Setting as per ur need e.g. dd/mm/yyyy OR mm/dd/yyyy. Then in your Access Database set the fields i.e. ur data field format as per ur need e.g. dd\/mm\/yyyy OR mm\/dd\/yyyy.
Good Luck.
Jay
Hi!
I thought all VB users would know this by now!
Bill Gates decided that all dates posted to Access are in the US format mm/dd/yyyy.
If you query or explicitly post or update 'dates' you must use this format irrespective of the regional settings.
ie
format(date,"mm/dd/yy")
This only works if its a Date you are using - if its a string - manipulate it!
Of course bound controls do this themselves according to regional settings - beware - the datacontrols can mess this up - always post dates explicitly in code.
Good luck
Voodooman!
I thought all VB users would know this by now!
Bill Gates decided that all dates posted to Access are in the US format mm/dd/yyyy.
If you query or explicitly post or update 'dates' you must use this format irrespective of the regional settings.
ie
format(date,"mm/dd/yy")
This only works if its a Date you are using - if its a string - manipulate it!
Of course bound controls do this themselves according to regional settings - beware - the datacontrols can mess this up - always post dates explicitly in code.
Good luck
Voodooman!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Voodooman,
Your answer will not work in a date field.
Again, dates in the database are stored as DOUBLES no matter what the regional settings show for the format.
RS!DateField = Now
will *always* store the current date/time!
The problem comes when you are trying to force a format that could be ambiguous. If the *operator* is generating a date field they can type it as mm-dd-yy or dd-mm-yy and if the day portion is under 12 then the program can't be 100% sure of what is meant.
IsDate() doesn't help either.
Two solutions:
1) Present the operator with the *Calendar* control so they pick a date by clicking on the calendar. Then you can code:
rs!DateField = Calendar.Value
and be *sure* that the date is clean.
Same-same idea with VB6's date time picker control.
2) Change the format of the date to one where the month is entered as *string* instead of numeric. Typically use a mask edit box with the .Mask property: ##-@@@-#### (Double check this - i'm writing off the top of my head)
Then you can use IsDate() on the MaskEditBox.Text value to determine if it's actually a date.
The problem is that if the date isn't valid you have to nag the operator on the Lost_Focus event and it forces them to retry.
Of the two methods, I prefer the first. It is more intuitive, is regionally & language independent and doesn't require the operator to key. Here is code that will fire off a form containing a calendar when entering a date field.
Private Sub AppealDate_Click()
'
' Here when operator clicks on the date field.
'
Me.Hide 'Vanish
DateFinder.Show vbModal 'Show date picker and wait
AppealDate.Text = Format(DateFinder.When.Val ue, "dd-mmm-yyyy") 'Get date from picker and format
Me.Show 'Reappear
'
End Sub
Private Sub AppealDate_KeyPress(KeyAsc ii As Integer)
'
' Fields are locked to prevent editing...
'
Select Case Chr(KeyAscii)
Case vbCr, " " 'Treat <Enter> same as click
Call AppealDate_Click
'
End Select
'
End Sub
The DateFinder form is a simple form with only the calendar control (called WHEN) on it. Here all of the code for that form:
'
' Form to allow selection of dates
' By: Mark M. Lambert on February 28, 2000
'
' V1.0 - 28 Feb 00 - MML - Initial Code
'
Option Explicit
Private Sub Form_Activate()
'
' Default to today when shown
'
When.Value = Date
'
End Sub
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
'
' Ignore clicks in [X]
'
If UnloadMode <> vbFormCode Then Cancel = True
'
End Sub
Private Sub When_Click()
'
' Vanish after selection is made
'
Me.Hide
'
End Sub
This gives a fast, clean and 100% accurate way for the operators to pick dates without any ambiguity in what they meant.
Again, the problem isn't the date is wrongly formatted *in* the database, it's that ambiguous dates are being keyed.
M
Your answer will not work in a date field.
Again, dates in the database are stored as DOUBLES no matter what the regional settings show for the format.
RS!DateField = Now
will *always* store the current date/time!
The problem comes when you are trying to force a format that could be ambiguous. If the *operator* is generating a date field they can type it as mm-dd-yy or dd-mm-yy and if the day portion is under 12 then the program can't be 100% sure of what is meant.
IsDate() doesn't help either.
Two solutions:
1) Present the operator with the *Calendar* control so they pick a date by clicking on the calendar. Then you can code:
rs!DateField = Calendar.Value
and be *sure* that the date is clean.
Same-same idea with VB6's date time picker control.
2) Change the format of the date to one where the month is entered as *string* instead of numeric. Typically use a mask edit box with the .Mask property: ##-@@@-#### (Double check this - i'm writing off the top of my head)
Then you can use IsDate() on the MaskEditBox.Text value to determine if it's actually a date.
The problem is that if the date isn't valid you have to nag the operator on the Lost_Focus event and it forces them to retry.
Of the two methods, I prefer the first. It is more intuitive, is regionally & language independent and doesn't require the operator to key. Here is code that will fire off a form containing a calendar when entering a date field.
Private Sub AppealDate_Click()
'
' Here when operator clicks on the date field.
'
Me.Hide 'Vanish
DateFinder.Show vbModal 'Show date picker and wait
AppealDate.Text = Format(DateFinder.When.Val
Me.Show 'Reappear
'
End Sub
Private Sub AppealDate_KeyPress(KeyAsc
'
' Fields are locked to prevent editing...
'
Select Case Chr(KeyAscii)
Case vbCr, " " 'Treat <Enter> same as click
Call AppealDate_Click
'
End Select
'
End Sub
The DateFinder form is a simple form with only the calendar control (called WHEN) on it. Here all of the code for that form:
'
' Form to allow selection of dates
' By: Mark M. Lambert on February 28, 2000
'
' V1.0 - 28 Feb 00 - MML - Initial Code
'
Option Explicit
Private Sub Form_Activate()
'
' Default to today when shown
'
When.Value = Date
'
End Sub
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
'
' Ignore clicks in [X]
'
If UnloadMode <> vbFormCode Then Cancel = True
'
End Sub
Private Sub When_Click()
'
' Vanish after selection is made
'
Me.Hide
'
End Sub
This gives a fast, clean and 100% accurate way for the operators to pick dates without any ambiguity in what they meant.
Again, the problem isn't the date is wrongly formatted *in* the database, it's that ambiguous dates are being keyed.
M
ASKER
Comment accepted as answer
Post some code.
You could also check your Regional Settings.