Data mismatch when sorting.

[Hillsy7]

Hi all,

Having an issue here - not overly experienced with access and can't work out where things are going wrong....

I Have a table of information about items....I needed to run a query which removed certain Items if they were similar to others (secondary or lesser versions)....I did this in excel simple enough with a macro, so I created a user defined function in Access to mimic the macro, called DeleteISBNInfo()

Public Function DeleteISBNInfo(Pubdate As Date, Title As String, Format As String, Binding As Variant) As Integer


If Not IsNull(Format) And Not IsNull(Title) And Not IsNull(Pubdate) Then
    If Format = "epub" Then
            DeleteISBNInfo = DCount("*", "ISBN info", "[Pub Date] = #" & Pubdate & "# AND [Title] = '" & Replace(Title, "'", "''") & "' AND [Format] = 'B Format'") _
            + DCount("*", "ISBN info", "[Pub Date] = #" & Pubdate & "# AND [Title] = '" & Replace(Title, "'", "''") & "' AND [Format] = 'A Format'") _
            + DCount("*", "ISBN info", "[Pub Date] = #" & Pubdate & "# AND [Title] = '" & Replace(Title, "'", "''") & "' AND [Format] = 'Demy'") _
            + DCount("*", "ISBN info", "[Pub Date] = #" & Pubdate & "# AND [Title] = '" & Replace(Title, "'", "''") & "' AND [Format] = 'Royal'")
    ElseIf Format = "A Format" Or Format = "Royal" Then
        DeleteISBNInfo = DCount("*", "ISBN info", "[Pub Date] = #" & Pubdate & "# AND [Title] = '" & Replace(Title, "'", "''") & "' AND [Format] = 'B Format'")
    ElseIf Format = "Demy" Then
        DeleteISBNInfo = DCount("*", "ISBN info", "[Pub Date] = #" & Pubdate & "# AND [Title] = '" & Replace(Title, "'", "''") & "' AND [Format] = 'Royal'")
    ElseIf Binding = "Trade Paperback" Then
        DeleteISBNInfo = DCount("*", "ISBN info", "[Pub Date] = #" & Pubdate & "# AND [Title] = '" & Replace(Title, "'", "''") & "' AND [Edition Binding] = 'Hardback'")
    Else
        DeleteISBNInfo = 0
    End If
End If


End Function

So when I created a query and added this function into an additional column to the table.....worked perfectly! There were zeros when there wasn't a similar product up the chain, and a non-zero count if there was. Great!

...Not so great....when I attempted to sort on the column containing this formula, I got the "Data Type Mismatch in Criteria Expression" error. I can sort the table fine by any other field...just not this one..

.....I don't understand!!! Why?!?!?!!



Any help would be greatly appreciated!

[CJ_London]

So when I created a query and added this function into an additional column to the table.....worked perfectly!

I bet if you were to scroll down the entire query result you will find one or more #errors

you probably have some nulls because

If Not IsNull(Format) And Not IsNull(Title) And Not IsNull(Pubdate) Then

if any of these is null then you are not assigning a value to DeleteISBNInfo

try modifying the end of you function to

    ...
    ...
    Else
        DeleteISBNInfo = 0
    End If

Else
   DeleteISBNInfo = 0
End If


End Function

[Hillsy7]

AH FFS!!!!!

1! 1 error in 5000+ records!!!!!

Thanks a bunch!! - didn't know having #errors screwed up sorts....I know what to look for now (Also had to switch the string to variant to allow nulls, but did that before.

I have learned....thanks again

[CJ_London]

Also be aware that although Format is not a reserved word, it is a function and also a property for controls, fields etc. Using it might cause a problem in another situation