Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

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
0
juricta
Asked:
juricta
2 Solutions
 
Rgonzo1971Commented:
Hi,

to transform your pseudo ISO date to Access date format

pls try

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

Regards
0
 
Dale FyeCommented:
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.
0
 
PatHartmanCommented:
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.
0
 
Nick67Commented:
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))
0
 
jurictaAuthor Commented:
Points awarded to the first one(s) whose code works.  Thanks a bunch!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now