Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm having problems with a query in Access 2007,running under Windows 7.

    select * from
    tblInventory
    WHERE fncRmvNonAlphaNmrc(UCase(Dscrptn)) = "KB"
    AND ID = 1833
    AND Len(Dscrptn) > 0

    I get a data type mismatch every time I run this query. I've isolated the problem to fncRmvNonAlphaNmrc when used in the where criteria. The fncRmvNonAlphaNmrc function returns a string with all special characters and white space removed. This function has worked in the past and I can run fncRmvNonAlphaNmrc around a field in the select and it works fine. What don't I understand about using this function in the where criteria? Any help would be greatly appreciated.

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Can you post the Function Definition.
    It looks like a user defined function, so without seeing that it is difficult to say.
    Andrew

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry about that.

    Function fncRmvNonAlphaNmrc(strInput As String) As String

    Dim i As Integer
    Dim strOutput As String

    strOutput = vbNullString

    If Len(strInput & "") > 0 Then

    For i = 1 To Len(strInput)
    ' If it's AlphaNumberic...
    If IsCharAlphaNumericA(Asc(Mid(strInput, i, 1))) Then

    strOutput = strOutput & Mid(strInput, i, 1)

    End If
    Next i
    End If
    fncRmvNonAlphaNmrc = strOutput

    End Function

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by jmnees View Post
    Sorry about that.

    Function fncRmvNonAlphaNmrc(strInput As String) As String

    Dim i As Integer
    Dim strOutput As String

    strOutput = vbNullString

    If Len(strInput & "") > 0 Then

    For i = 1 To Len(strInput)
    ' If it's AlphaNumberic...
    If IsCharAlphaNumericA(Asc(Mid(strInput, i, 1))) Then

    strOutput = strOutput & Mid(strInput, i, 1)

    End If
    Next i
    End If
    fncRmvNonAlphaNmrc = strOutput

    End Function
    When you say it's run in the past, is that in an earlier version of Access?

    You may have to change:
    If IsCharAlphaNumericA(Asc(Mid(strInput, i, 1))) Then
    to
    If IsCharAlphaNumericA(Asc(Mid(strInput, i, 1))) = True Then

    I didn't think you could put Null into a string, it should be defined as a variant..

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Did you get a Data Type Mismatch error or the message below?

    [attachment=88365:ByRefError.jpg]

    If the error above, then try changing the first line of the function definition to (I added ByVal to the definition)

    Code:
    Function fncRmvNonAlphaNmrc(ByVal strInput As String) As String
    If that does not fix it (It did for me) it may be something to do with the

    Windows API Function Call to IsCharAlphaNumeric
    Attached Images Attached Images
    Andrew

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Good point Andrew - why not use the VBA function IsNumeric()?
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •