Results 1 to 9 of 9
  1. #1
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82

    trying to compare function-created strings in a query and it won't work

    So, I have a query that looks for duplicate song titles in a Songs database. but since punctuation can sometimes obscure these matches (i.e., "Baby, I love you" and "Baby I love you"), I created a function that strips out all punctuation. Then I created a query that gets everything from Songs and adds in the punctuation-less title. But when I query that altered title, StrippedTitle, I get the error "data type mismatch in criteria expression." Can anyone tell me why?




    Query that generates the "data type mismatch" error:
    Code:
    SELECT StrippedTitle
    FROM StrippedSongs
    GROUP BY StrippedTitle, Member
    HAVING (Count(StrippedTitle)>1);
    StrippedSongs query (which calls Function StripPunctuation, which returns a String):
    Code:
    SELECT StripPunctuation([Title]) AS StrippedTitle, Songs.SongID, Songs.Title, Songs.Excerpt, Songs.Notes, Songs.Composer_id, Songs.Member
    FROM Songs;
    Songs table:
    Code:
    SongID    AutoNumber
    Title        Short Text
    Excerpt   Short Text
    Notes      Long Text
    Composer_id Number
    Member    Number
    The query off of Songs that I'm trying to replace with the query to StrippedSongs:
    Code:
    SELECT Title
    FROM Songs
    GROUP BY Title, Member
    HAVING (Count(Title)>1);

  2. #2
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    your criteria is looking for Count(StrippedTitle)>1, but I don't see anywhere you have actually counted them. you have essentially a numeric criteria in a string field, hence datatype mismatch.

    I think you want:

    Code:
    SELECT StrippedTitle, Count(StrippedTitle) as Stitle
    FROM StrippedSongs
    GROUP BY StrippedTitle, Member
    HAVING ((((Count(StrippedTitle))>1));

    good luck with your project,


    Cottonshirt

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Also make sure that there are no nulls in the Title field, or that the function won't barf on one.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82
    But if that's the issue, then why does this work?

    SELECT Title
    FROM Songs
    GROUP BY Title, Member
    HAVING (Count(Title)>1);

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by cherold View Post
    But if that's the issue, then why does this work?

    SELECT Title
    FROM Songs
    GROUP BY Title, Member
    HAVING (Count(Title)>1);
    Not sure who that's addressed to, but if me that would work because you aren't calling the function. I'm wondering if a null is getting passed to a function that can't handle it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82
    I'd actually meant to reply to Pbaldy, who had suggested added the Count() function to the select (which didn't work).

    To check the possible null issue I changed the function so if it received a null it would return an empty string, but that didn't help the data mismatch error either.

    My initial thought was that since the original query is dealing with a "short text" field and the new query is dealing with a string returned from a function that perhaps that was the issue, but I can't find any way to make the function return something as short text so that appears to be a non-starter.

  7. #7
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Can you post up the function?

    Might be worth forcing it to a sensible length eg.

    Code:
    Public Function StripStuff(sInput as Variant) as String
    
        'Code to strip stuff goes here
       
    
       StripStuff =  Left(StripStuff, 254)
    
    End Function
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82
    Here it is. I haven't tried limiting length in the function, but I did try adding left(StrippedSong, 10) in the Count() within the query and that didn't have any effect.


    Code:
    Function StripPunctuation(word As String) As String
        Dim punctStr As String
        Dim i As Integer
        
        punctStr = ".,"" ''- ()[]"
        
        If IsNull(word) Then
            word = ""
        End If
        
        If word <> "" Then
            For i = 1 To Len(punctStr)
                word = Replace(word, Mid(punctStr, i, 1), "")
            Next
        End If
        
        StripPunctuation = word
    End Function

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This function will not accept a Null (your test for it will never be hit). The input data type would have to be Variant, otherwise it will error:

    Function StripPunctuation(word As Variant) As String

    Not saying that's your error, but it should be eliminated as a possibility.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 21
    Last Post: 06-12-2017, 12:37 PM
  2. Replies: 12
    Last Post: 05-02-2017, 07:39 PM
  3. Query to work as a Vlookup function
    By dharmik in forum Queries
    Replies: 21
    Last Post: 01-04-2012, 08:12 AM
  4. Replies: 6
    Last Post: 01-07-2011, 12:50 PM
  5. How to work with .dbf file created by GIS
    By cowboy in forum Programming
    Replies: 8
    Last Post: 09-28-2010, 10:26 AM

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