Solved

Customize Date Format Access 2010 VBA

Posted on 2014-12-30
5
320 Views
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

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
Comment
Question by:juricta
5 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 40525053
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
 
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.
0
 
LVL 34

Expert Comment

by:PatHartman
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())
 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
 
LVL 26

Expert Comment

by:Nick67
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
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
 

Author Closing Comment

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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now