Results 1 to 6 of 6
  1. #1
    markcrobinson is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    New Hampshire, US
    Posts
    31

    Tables full of Inches" and Feet' - Dealing with Quotes and apostrophes

    Spent several months and wrote this brilliant program to process MRP data, come to find out the Actual data is filled with apostrophes and quotes. Dozens of Queries and tables all related by thousands of ItemID are now crashing and burning. Played with a function that goes through and strips all non-ascii characters on the fly - but now takes days to execute... and passing a string in a function when the string that may have a " or a ' in it is no fun at all.

    Anybody conquered this beast?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    See if this link helps: http://allenbrowne.com/casu-17.html
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I replace all dbl-quotes in entries to single quotes:

    replace([field],chr(34),"'")

  4. #4
    markcrobinson is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    New Hampshire, US
    Posts
    31
    Sounds like you're suggesting that I run a process on all my data files such that
    9" nails
    would become
    9"'" nails
    I see how this would work in theory, and be easy to implement, although a 9' long nail would look a bit cumbersome on first read.
    Also, whereas that would handle quotes, some of my SQL statements use apostrophes in the logic
    DoCmd.RunSQL ("Select * FROM Items WHERE itemid = 'nails';")
    These might get angry if I
    DoCmd.RunSQL ("Select * FROM Items WHERE itemid = '9' nails';")
    I'm thinking of going through all the files and changing " to i and ' to f, then running this process when I import new files.

    If I create a function to convert a string like 9" nails, how do I pass that parameter?
    OldStr=rs1.Itemid
    NewStr=CleanAscii(OldStr) will fail when OldStr has a quote in it.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    ItemID is a text field?
    Code:
    "Select * FROM Items WHERE itemid = '9" & Chr(34) & Chr(34) & " nails'"
    If parameter is selection of combobox or listbox item:
    Code:
    "Select * FROM Items WHERE itemid = '" & Replace(Me.combobox, Chr(34), Chr(34) & Chr(34)) & "'"
    Why are you showing RunSQL on a SELECT query?

    When I first encountered this, I figured out how to deal with apostrophes by using Replace() in code. However, quote marks stumped me and I FORBADE users to use quotes in typing text - had to use "inch" or "inches" or decimal. I since learned about Chr() function.

    Function should not have issue receiving string with quote or apostrophe characters - it's SQL that chokes.
    Last edited by June7; 05-28-2020 at 11:49 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I'd convert the strings to numerics. Maybe something like [Item] = "9' Nails" becomes [ItemTypeId] = {reference to lookup table where id of item type = "Nail"} and [ItemLength] = 9.0

    I wrote a vba regex function a few years ago that will convert different feet and inch notations to decimal, if that would help I'll see if I can dig it up when I get back to my computer. End user input can be ft/in or '/" or whatever and then just process it to a decimal before recording to the db.

    [EDIT] Here you go
    Code:
    '/*---------------------------------------------------------------------
    '|  Method FtIn2Dec
    '|
    '|  Purpose: To convert user input strings in various feet & inch
    '|      notations to decimal inches. Uses a regular expression
    '|      to parse the input into it's numeric components then
    '|      performes appropriate arithmitic on the components to return a
    '|      single decimal value in inches. Optionally return value can be
    '|      feet
    '|
    '|  Pre-condition:
    '|      The regular expression engine must be referenced:
    '|      Tools -> References -> Microsoft VBScript Regular Expressions 5.5
    '|
    '|      1.  If the first non space character in the string is a hyphen or
    '|          a open parentheses then the result is interpretted as negative.
    '|          A closing parentheses at the end of the input is optional and
    '|          no effect on the result. Only leading parentheses will return
    '|          a negative number
    '|          - 12' <= valid
    '|          (12') <= valid
    '|          (12'  <= valid
    '|          12')  <= valid but NOT interpreted as negative
    '|
    '|      2.  Input may have up to two values, one feet value and one inch
    '|          value each.
    '|      3.  Input may ommit a feet value or an inch value
    '|      4.  An input of an empty string will return 0
    '|      5.  Feet unit symbols are ft and '
    '|          Inch unit symbols are in, ", and ''
    '|      6.  Values without a unit symbol following it will be interpreted
    '|          as inches
    '|          6 1/2   <= valid interpreted as inches
    '|          12' 6.5 <= valid, same as 12' 6.5"
    '|
    '|      7.  Feet values must always be followed by either ft or ' symbols
    '|          12ft    <= valid
    '|          12'     <= valid
    '|          12      <= valid but interpreted as inches
    '|          12 6.5" <= invalid
    '|
    '|      8.  A feet value must always be before the inch value
    '|          12 ft 6 1/2 in <= valid
    '|          6 1/2 in 12 ft <= invalid
    '|
    '|      9.  If input has both a feet and inch value then the feet value
    '|          must be a whole number
    '|          12' 6 1/2" <= valid
    '|          12.5' 1/2" <= invalid
    '|
    '|      10.  If input is only a feet value then it may be entered as a whole
    '|          number or a decimal number. Never as a fraction
    '|          12'     <= valid
    '|          12.75'  <= valid
    '|          12 3/4' <= invalid
    '|
    '|      11. Inches may be entered as a whole number, a whole number and a
    '|          fraction, just a fraction, or as decimal number
    '|          6"     <= valid
    '|          6 1/2" <= valid
    '|          6.5"   <= valid
    '|          13/2"  <= valid
    '|
    '|          Note: spaces are the only valid character to seperate whole number
    '|          from a fraction's numerator
    '|          12' 6 1/2" <=valid
    '|          12' 6-1/2" <=invalid
    '|
    '|      12. Components of a fraction must all be whole numbers
    '|          6 1/2"   <= valid
    '|          6 1.5/2" <= invalid
    '|
    '|      13. Optionally may seperate feet and inches with a hyphen
    '|          12'-6 1/2" <= valid
    '|
    '|          Note: If ommiting a feet value and there is still a hyphen
    '|          before the inch value the result will be returned as negative
    '|          12'-6"   <= valid equal to 150 (inches)
    '|          -12'-6"  <= valid equal to -150 (inches)
    '|          -6"      <= valid equal to -6 (inches)
    '|          --6"     <= invalid
    '|
    '|
    '|      14. Spacing between negative symbol, unit symbols, and divisors
    '|          are all optional
    '|          (   12 '   -     6 1  /2   " ) <= valid and equals (12'-6 1/2")
    '|
    '|      15. The following examples are valid and will all return
    '|          150.5 (inches):
    '|          12'-6 1/2"
    '|          12' 6 1/2"
    '|          12 ft 6.5 in
    '|          12 ft 13/2 "
    '|          150 1/2"
    '|          150 1/2
    '|          150.5
    '|          12ft 6.5
    '|          12.541666666'
    '|
    '|      16. An input that doesn't meet the conditions above will return
    '|          an error
    '|
    '|      17. Note there are no range limits on the numerical components
    '|          12' 43 13/4" <= valid equal to 190.25 (inches)
    '|
    '|  Post-condition: returns a double in inches unless specified
    '|      to return feet
    '|
    '|  Parameters:
    '|      str -- String -- The input string to be parsed
    '|      returnInches -- Optional boolean -- If true (default) the function
    '|          will return a decimal value in inches. If false it will return
    '|          a value in feet.
    '|
    '|  Returns:  A double in inch units unless specified to be feet. Returns
    '|      an error code if the input string was invalid.
    '*-------------------------------------------------------------------*/
    Public Function FtIn2Dec(ByVal str As String, Optional returnInches As Boolean = True) As Variant
    On Error GoTo ErrHandler:
        Dim regEx As New RegExp 'Tools -> References -> Microsoft VBScript Regular Expressions 5.5
        Dim matches As MatchCollection
        
        If Trim(str & vbNullString) = vbNullString Then
            FtIn2Dec = 0#
            GoTo ExitHandler
        ElseIf IsNumeric(str) Then
            FtIn2Dec = CDbl(str)
            GoTo ExitHandler
        End If
        
        Dim pattern As String
        pattern = "^\s*(-|\()?\s*(?:(\d+\.\d*|\d*\.\d+)\s*(?:ft|')|(?:(\d+)\s*(?:ft|'))?\s*-?\s*(?:(\d*?)(?:\s*(\d+)\s*\/\s*(\d+))?|(\d*\.\d+|\d+\.\d*))\s*(?:in|""|'')?)\s*\)?\s*$"
        
        Set regEx = New RegExp
        
        With regEx
            .Global = True
            .MultiLine = False
            .IgnoreCase = True
            .pattern = pattern
            Set matches = .Execute(str)
        End With
        
        If matches.Count = 0 Then GoTo ErrHandler
        
        Dim feet_part As Double
        Dim inch_part As Double
        Dim inch_total As Double
        Dim denominator As Integer
        
        With matches(0)
            denominator = CNum(.SubMatches(5))
            If denominator = 0 Then denominator = 1
            feet_part = CNum(.SubMatches(1)) + CNum(.SubMatches(2))
            inch_part = CNum(.SubMatches(3)) + (CNum(.SubMatches(4)) / denominator) + CNum(.SubMatches(6))
        End With
        inch_total = feet_part * 12# + inch_part
        If Not IsEmpty(matches(0).SubMatches(0)) Then inch_total = -1 * inch_total
        
        If returnInches Then FtIn2Dec = inch_total Else FtIn2Dec = inch_total / 12#
        
    ExitHandler:
        Set regEx = Nothing
        Set matches = Nothing
        Exit Function
    
    ErrHandler:
        Debug.Print "Error converting feet & inches string to a decimal value."
        FtIn2Dec = CVErr(2015)
        Resume ExitHandler
        
    End Function
    
    'CDbl( ) on a blank string will throw an error. Needed this little helper
    ' function to quickly convert empty and blank string/variants to zero for
    ' the FtIn2Dec function.
    Private Function CNum(ByVal str As Variant) As Double
        If IsNumeric(str) Then CNum = CDbl(str) Else CNum = 0#
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 01-10-2015, 01:58 AM
  2. Displaying Height as Feet and Inches
    By beaverx37 in forum Forms
    Replies: 6
    Last Post: 11-12-2014, 12:12 PM
  3. Replies: 11
    Last Post: 03-29-2012, 02:32 PM
  4. Replies: 2
    Last Post: 03-23-2012, 05:09 AM
  5. Replies: 0
    Last Post: 12-23-2008, 01:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums