Access importing dates from various formats

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
Example:
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
    End If
   
    If DCount("RecID", "XML_PartNum_Exp", "[RecID] = " & iRecID & " AND [PartNumber] = '" & strPart & "'") = 0 Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO XML_PartNum_Exp ( RecID, PartNumber, ExpDate, DateValid ) SELECT " & iRecID & ", '" & strPart & "', '" & strDate & "', " & bValid & ";"
        DoCmd.SetWarnings True
        fSavePart = True
    Else
        fSavePart = False
    End If
End Function
CountryGirlMDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ken FayalCTOCommented:
I can't see a way to do this without some serious parsing of your date string.  There isn't a way to set the default of a date variable which wasn't initialized with a day of the month to the end of the month.

You might be able to use Regular Expressions to get what you are after.  Here is a good article that describes how to use RegExp in VBScript.

http://www.robvanderwoude.com/vbstech_regexp.php
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Terry WoodsIT GuruCommented:
Ken's suggestion might work, and his link is helpful (I'm not a VBScript programmer, usually!). I can help with a regex. The date is obtained here:
        strDate = Trim(Mid(strTemp, iSpace + 1, iLen))

Open in new window

Try adding something like this, after the above line:
Set objRE = New RegExp

With objRE
    .Pattern    = "(\w+[ /-]+){2}\w+"    'Pattern to match a 3-part date
    .IgnoreCase = True
    .Global     = False
End With

' Test method returns TRUE if a match is found
If objRE.Test( strDate ) Then
    'We have a 3-part date (or more, for bad data). Nothing should need to be done here.
Else
    'We seem to have less than 3 parts to our date - do something to default the day to the end of the month
End If

Set objRE = Nothing

Open in new window


You'll still need to add code for setting the day to the last day of the month, but the above will hopefully detect most cases for when it needs doing.
0
clarkscottCommented:
You could create a table and designate the date field as a DATE.
Now, import your data (parse and such) into this new table.
Because the date field is an Access Date type - it should "interpret" the date (various formats) as an Access date field.

Now, use this table for your processes.

Scott C
0
CountryGirlMDAuthor Commented:
Thanks all for the feedback
I need to keep the code inside the application so using a vb script is not an option and there doesn’t seem to be an equivalent to the regular expressions function in vba

So I went with doing a check for the two most common of the 3 place date formats if the day was 1 otherwise assuming only a two part date setting to the last day of the month.  My users will be reviewing the data and will have the ability to change the date if needed.


Revised Code:
    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)
       
        If bValid Then
            If Day(strDate) = 1 Then
                S1 = InStr(1, strDate, "/")                 '4/1/2014 format
                S2 = InStr(S1 + 1, strDate, "/")
                If S1 + 2 = S2 Then
                    dNewDate = strDate
                    GoTo AddRecord
                End If
       
                If S1 = 0 Then                                  'April 1, 2014 format
                    S2 = InStr(1, strDate, "1,")
                    If S2 <> 0 Then
                        dNewDate = strDate
                        GoTo AddRecord
                    End If
                End If

                dNewDate = DateAdd("m", 1, strDate) - 1

            Else                'day not 1
                dNewDate = strDate
            End If
        End If  'date valid
       
    Else   'no date / invalid
        strPart = Trim(strTemp)
        dNewDate = ""
        bValid = False
    End If
   
AddRecord:
    If DCount("RecID", "XML_PartNum_Exp", "[RecID] = " & iRecID & " AND [PartNumber] = '" & strPart & "'") = 0 Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO XML_PartNum_Exp (RecID, PartNumber, DateText, ExpDate, DateValid ) SELECT " & iRecID & ", '" & strPart & "', '" & strDate & "', '" & dNewDate & "', " & bValid & ";"
        DoCmd.SetWarnings True
    End If
End Function
0
CountryGirlMDAuthor Commented:
splitting the points between ken & terry for the effort
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.