Where clause 'right 7 charecters are numeric'

[bugsy]

Hey All

Any suggestions how to do a query with where clause ' wher right 7 charecters are numeric' ?

 

[DALeffler]

?IsNumeric(Right("abc1234567",7))

In the immediate window returns "True".

 

[raskew]

Hi -

Don't think you've provided enough information to see exactly what you're attempting to do and what the problem is.

The following function will take an alpha-numeric / numeric-alpha string and split into either its alpha or numeric portion, depending on user input. Don't know if this will be helpful but you might play around with it:

Function SplitString2(ByVal pstr As String, _
                     pAlpha As Boolean, _
                     Optional delim As String = "") _
                     As Variant
'************************************************
'Purpose:   Split a alpha-numeric string
'           e.g., "abc123", "123abc", "cd_01")
'           with or without a delimiter
'           ("-", "_", etc.)into either
'           the alpha or numeric portion,
'           dependent on the value of pAlpha.
'Re:        http://www.utteraccess.com/forums/ _
'           showflat.php?Cat=&Board=59&Number= _
'           667165&page=0&view=collapsed&sb= _
'           5&o=&fpart=1#Post667801
'Coded by:  raskew
'Inputs:    from the debug (immediate) window:
'           1) ? splitstring2("123abc", True)
'           2) ? splitstring2("123abc", False)
'           3) ? splitstring2("abc123", True)
'           4) ? splitstring2("abc123", False)
'           5) ? splitstring2("cd_23", True, "_")
'           6) ? splitstring2("cd_23", False, "_")
'
'Outputs:   1) abc
'           2) 123
'           3) abc
'           4) 123
'           5) cd
'           6) 23
'************************************************

Dim strHold  As String
Dim intDelim As Integer
Dim inthold  As Integer
Dim intLen   As Integer
Dim n        As Integer
Dim varKeep  As Variant

    strHold = Trim(pstr)
    intLen = Len(strHold)
    inthold = val(strHold)
    
    'position of delim in string
    intDelim = InStr(strHold, delim)
    
    'Is Delim present in string?
    If delim <> "" And intDelim > 0 Then
       If val(Left(strHold, intDelim - 1)) > 0 Then
          If Not pAlpha Then
             varKeep = val(Left(strHold, intDelim - 1))
          Else
             varKeep = Mid(strHold, intDelim + Len(delim))
          End If
       Else
          If Not pAlpha Then
             varKeep = Mid(strHold, intDelim + Len(delim))
          Else
             varKeep = Left(strHold, intDelim - 1)
          End If
       End If
       
    'Delim not present in string.
    ElseIf inthold = 0 Then
       n = intLen
       Do While IsNumeric(Mid(strHold, n, 1))
          n = n - 1
       Loop
       If pAlpha Then
          varKeep = Mid(strHold, 1, n)
       Else
          n = n + 1
          varKeep = Mid(strHold, n)
       End If
    Else  'intHold > 0
       If Not pAlpha Then
          varKeep = inthold
       Else
          varKeep = Mid(strHold, Len(inthold) + 2)
       End If
    End If
    
    SplitString2 = varKeep
            
End Function

HTH - Bob

 

[bugsy]

?IsNumeric(Right("abc1234567",7))

In the immediate window returns "True".

that wasn't working, but
WHERE IsNumeric(Right("abc1234567",7)) = 1
worked fine, tx