[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

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
0
CountryGirlMD
Asked:
CountryGirlMD
3 Solutions
 
Ken FayalCommented:
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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