asked on

Customize Date Format Access 2010 VBA

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?

SAMPLE STRING:001012001795000000150101000000
How it SHOULD break down:
Product Code-001012
New Price-001795
Old Price-000000
Start Date-150101
End Date-000000

HERE IS THE CODE:
Dim rsImportNew As DAO.Recordset
Set rsImportNew = CurrentDb.OpenRecordset("SELECT * FROM tblImportNewPrice")
rsImportNew.MoveLast
x = rsImportNew.RecordCount 'Find total number of records
'MsgBox x

'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)
Else

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")
'MsgBox OLCCStartDate
OLCCEndDate = Right(txtNewPrice, 6)

'MsgBox txtNewPrice
'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
End If
rsImportNew.MoveNext
Loop
Else
MsgBox "There are no records in the recordset."
End If

MsgBox "Finished looping through records."

rsImportNew.Close 'Close the recordset
Set rs = Nothing 'Clean up

End Sub

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PatHartman

Dates in Jet/ACE as well as most other relational databases (even Excel) are stored as double precision numbers. They are NEVER stored as strings. When you set a format, you are saying how you want it to be displayed. You are not changing how the data is stored. In the case of Jet/ACE, the origin date (zero value) is 12/30/1899. So dates after that one are positive numbers, 1 = 12/31/1899, 2 = 1/1/1900, etc. and prior dates are negative numbers. -1 = 12/29/1899, -2 = 12/28/1899, etc. Time is stored as the decimal. So as I post this:

print cdbl(now())
42004.4684837963
print now()
12/31/2014 11:14:43 AM

so it has been 42,004 days since 12/30/1899 and .4684837963 is almost half-way through the day.

Nick67

Supplementary to what is said above
OLCCStartDate = Format(OLCCStartDate, "yy/mm/dd")
OLCCEndDate = Right(txtNewPrice, 6)

I don't see where you've Dim'med these items but they still look to be text.
You HAVE to coerce the String to DateTime before you insert it into the table.
And I don't see where you have done that -- and hence your problem.
RGonzo has suggested using DateSerial to get it done

Syntax
DateSerial(year, month, day)

So then date bits of your code would become
Dim OLCCStartDate as DateTime
Dim OLCCEndDate as DateTime
OLCCStartDate = DateSerial(Left(OLCCStartDate, 2), Mid(OLCCStartDate, 3, 2), Right(OLCCStartDate, 2))
OLCCEndDate = DateSerial(Left(OLCCEndDate, 2), Mid(OLCCEndDate, 3, 2), Right(OLCCEndDate, 2))

juricta

ASKER

Points awarded to the first one(s) whose code works. Thanks a bunch!!