Access importing dates from various formats
Posted on 2014-07-16
I’m working on a project to import data from an XML file generated from a PDF form
The field on the PDF form is unrestricted text entry
The users enter a Part Number & Date, if they have more than one part they enter them separated by a semi colon
ASC123 5/4/15; BCE234 MARCH 2015; XYZ987 JAN 21, 2016; PQR456 4/1/2017; STU432 8/2018; WXY876 9/9/19
When I import the data I need to split each part number & date into separate fields.
Each part number & date segment separated by the semi colon are sent to the function below to be saved to the table.
My problem is with the dates – because it’s open text I don’t have any control over how the dates are entered.
With my current code:
If the date is valid Access will save the date in the short date format defined on the tables date field.
If the date is invalid the part number is saved and the dates are left blank – which is what I want to happen.
My problem is with the variation where I only get month & year - MARCH 2015 or 8/2018 – which access treats as a valid date and defaults the day to the first day of the month.
But if only a month & year are provided I need them to default to the LAST day of the month, but a user could also enter a date of 7/1/2015 in which case I would not want to alter the day.
Is the default to the 1st of the month a setting that can be changed in Access?
Is there any way to tell when access is providing the day vs actually imported?
I expect to have a large percentage of the dates coming in as month / year so I don’t want to make the users reviewing the imports to have to correct these manually.
Private Function fSavePart(strTemp, iRecID As Integer) As Boolean
Dim iSpace As Integer
Dim iLen As Integer
Dim bValid As Boolean
Dim strPart As String
Dim strDate As String
iSpace = InStr(1, strTemp, " ")
If iSpace > 0 Then
strPart = Trim(Left(strTemp, iSpace))
iLen = Len(strTemp) - iSpace + 1
strDate = Trim(Mid(strTemp, iSpace + 1, iLen))
bValid = IsDate(strDate)
Else 'no date?
strPart = Trim(strTemp)
strDate = ""
bValid = False
If DCount("RecID", "XML_PartNum_Exp", "[RecID] = " & iRecID & " AND [PartNumber] = '" & strPart & "'") = 0 Then
DoCmd.RunSQL "INSERT INTO XML_PartNum_Exp ( RecID, PartNumber, ExpDate, DateValid ) SELECT " & iRecID & ", '" & strPart & "', '" & strDate & "', " & bValid & ";"
fSavePart = True
fSavePart = False