Fordraiders
asked on
Need vba function to fix dates in a field
I have a "date hired" field - text 10
in my "Employee" table.
folks are entering:
9/9/2004
1/02/1989
What I need:
I need the month and day to have a leading zero always:
Thanks
fordraiders
in my "Employee" table.
folks are entering:
9/9/2004
1/02/1989
What I need:
I need the month and day to have a leading zero always:
Thanks
fordraiders
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I do not have a form. So I need to loop through the table and fix any dates that are not in the format I need.
ASKER
this worked... based on kelvin:
Public Sub FixDate()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("S ELECT [Date Hired], [birthdate] FROM [Employees Information2]")
'Check to see if the recordset actually contains rows
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst 'Unnecessary in this case, but still a good habit
Do Until rs.EOF = True
'Perform an edit
rs.Edit
rs![Date Hired] = Format(rs.Fields("Date Hired"), "mm/dd/yyyy")
rs![BirthDate] = Format(rs.Fields("Birthdat e"), "mm/dd/") & "2015"
'rs("VendorYN") = True 'The other way to refer to a field
rs.Update
'Example Save contact name into a variable
'sContactName = rs!FirstName & " " & rs!LastName
'Move to the next record. Don't ever forget to do this.
rs.MoveNext
Loop
Else
MsgBox "There are no records in the recordset."
End If
MsgBox "Finished looping through records."
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
End Sub
Public Sub FixDate()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("S
'Check to see if the recordset actually contains rows
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst 'Unnecessary in this case, but still a good habit
Do Until rs.EOF = True
'Perform an edit
rs.Edit
rs![Date Hired] = Format(rs.Fields("Date Hired"), "mm/dd/yyyy")
rs![BirthDate] = Format(rs.Fields("Birthdat
'rs("VendorYN") = True 'The other way to refer to a field
rs.Update
'Example Save contact name into a variable
'sContactName = rs!FirstName & " " & rs!LastName
'Move to the next record. Don't ever forget to do this.
rs.MoveNext
Loop
Else
MsgBox "There are no records in the recordset."
End If
MsgBox "Finished looping through records."
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
End Sub
ASKER
Thanks !
MS ACCESS 2003: DEFINE A CUSTOM FORMAT FOR A DATE/TIME FIELD ON A FORM
http://www.techonthenet.com/access/forms/format_date.php