Customize Date Format Access 2010 VBA
Posted on 2014-12-30
I have a STRING that is 30 characters long. It consists of a: Product Code,New Price, Old Price, Start Date, and End Date each which is made up by 6 characters each. Product Code is the 1st 6 characters, New Price is the next 6 and so on. I parse the STRING and then ADD the 5 items to a table (tblNewPrice). In the case of the SAMPLE string shown below, I need the date to be added as 15/01/01 (YY/MM/DD) and I need it to display for view and useage as 15/01/01. When I view the table the date is displayed as: 12/30/1899. The DATE field is formatted as SHORT DATE Only because that is the closest I can find to what I need BUT it is NOT the format. When I click on the field in the table is displays as: 1:10:35 AM. I need to make sure I have the proper format so I can UPDATE other tables later. BELOW is a copy of the code I use to split the STRING into each new area.
Here are my 2 Questions:
1. Is there a simpler way to code this so it is not so cludgy?
2. How do I get the dates to display and store properly as stated above?
How it SHOULD break down:
HERE IS THE CODE:
Dim rsImportNew As DAO.Recordset
Set rsImportNew = CurrentDb.OpenRecordset("SELECT * FROM tblImportNewPrice")
x = rsImportNew.RecordCount 'Find total number of records
'Check to see if the recordset actually contains rows
If Not (rsImportNew.EOF And rsImportNew.BOF) Then
rsImportNew.MoveFirst 'Unnecessary in this case, but still a good habit
Do Until rsImportNew.EOF = True
vRecordNumber = rsImportNew.AbsolutePosition
If vRecordNumber = 0 Then
CreationDate = Left((rsImportNew("NewPrice")), 6) 'Left function extracts a substring from a string, starting from the left-most character
RecordCountNumber = Right((rsImportNew("NewPrice")), 3)
txtNewPrice = rsImportNew("NewPrice") 'ORIGINAL Text
txtNewPrice2 = Right(txtNewPrice, 24)
txtNewPrice3 = Right(txtNewPrice, 18)
txtNewPrice4 = Right(txtNewPrice, 12)
txtNewPrice5 = Right(txtNewPrice, 6)
OLCCItemCode = Left(txtNewPrice, 6) '1st 6 Characters
OLCCNewPrice = Left(txtNewPrice2, 6)
OLCCOldPrice = Left(txtNewPrice3, 6)
OLCCStartDate = Left(txtNewPrice4, 6)
OLCCStartDate = Format(OLCCStartDate, "yy/mm/dd")
OLCCEndDate = Right(txtNewPrice, 6)
'Move to the next record. Don't ever forget to do this.
Set mydb = CurrentDb
mydb.Execute "INSERT INTO tblNewPrice ([OLCCItemCode],[OLCCNewPrice],[OLCCOldPrice],[OLCCStartDate],[OLCCEndDate]) SELECT '" & OLCCItemCode & _
"','" & OLCCNewPrice & "'," & OLCCOldPrice & "," & OLCCStartDate & "," & OLCCEndDate
MsgBox "There are no records in the recordset."
MsgBox "Finished looping through records."
rsImportNew.Close 'Close the recordset
Set rs = Nothing 'Clean up