Customize Date Format Access 2010 VBA

Posted on 2014-12-30
Last Modified: 2014-12-31
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

Dim rsImportNew As DAO.Recordset
    Set rsImportNew = CurrentDb.OpenRecordset("SELECT * FROM tblImportNewPrice")
        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)
            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
        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
Question by:juricta
LVL 50

Accepted Solution

Rgonzo1971 earned 250 total points
ID: 40525053

to transform your pseudo ISO date to Access date format

pls try

Var = DateSerial(Left(OLCCStartDate, 2), Mid(OLCCStartDate, 3, 2), Right(OLCCStartDate, 2))

LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 40525192
For the initial parsing of your string, I would use:

OLCCItemCode = Mid(txtNewPrice, 1, 6)                 '1st 6 Characters
OLCCNewPrice = Mid(txtNewPrice, 7, 6)
OLCCOldPrice = Mid(txtNewPrice, 13, 6)
OLCCStartDate = Mid(txtNewPrice, 19, 6)
OLCCEndDate = Mid(txtNewPrice, 25, 6)

Then, to convert your StartDate and EndDates to actual dates use the DateSerial function similar to what Rgonzo posted above.
LVL 36

Expert Comment

ID: 40525525
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())
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.
LVL 26

Expert Comment

ID: 40525641
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
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))

Author Closing Comment

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

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question