Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Customize Date Format Access 2010 VBA

Avatar of juricta
jurictaFlag for United States of America asked on
Microsoft AccessVisual Basic Classic
5 Comments1 Solution422 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
Avatar of Rgonzo1971
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answers