troubleshooting Question

Customize Date Format Access 2010 VBA

Avatar of juricta
jurictaFlag for United States of America asked on
Microsoft AccessVisual Basic Classic
5 Comments2 Solutions422 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros