data type mismatch in criteria expression

[caprice63]

Hoping someone can shine a light on this please...
I have two queries which I join to form another list of data. However, I get the "data type mismatch in criteria expression" error.

The joined fields are sourced from two separate tables and are both Short Text in the source tables. I have tested at this level and the join works.

However, I have to manipulate one of the fields as follows: HHorse: CStr(IIf(InStr(1,[HistAll]![HorseName],"(")=0,[HistAll]![HorseName],Left([HistAll]![HorseName],InStr(1,[HistAll]![HorseName],"(")-1)))

The idea here is to find and HorseName that has "(" in it and return just the text before the "(". E.g. Coolagh Moll (IRE) becomes Coolagh Moll).

When I try to join HHorse with either of the original tables I get the "data type mismatch" error.

Any help would be greatly appreciated.
Thanks

 

[Micron]

Expression looks good, but then again I can't try it. I suggest you examine the records for zls or nulls. I suspect it's balking because 1 or more records have no horse name value.

 

[CJ_London]

you say the error is "data type mismatch in criteria expression" - but you haven't shown the criteria for the query, only a calculated value

and the calculated value will generate an error if your field 'horsename' starts with a '(' - but that would generate a 'error in expression' error

 

[caprice63]

Expression looks good, but then again I can't try it. I suggest you examine the records for zls or nulls. I suspect it's balking because 1 or more records have no horse name value.

Thanks for the reply. Fields have valid data - no nulls etc. I think access must change the properties when I perform the iif function.

 

[caprice63]

you say the error is "data type mismatch in criteria expression" - but you haven't shown the criteria for the query, only a calculated value

and the calculated value will generate an error if your field 'horsename' starts with a '(' - but that would generate a 'error in expression' error

Thanks for the reply,

Criteria is: .......FROM AV_01 LEFT JOIN AV_01_Hist2 ON AV_01.HorseTo = AV_01_Hist2.[HHorse1];

AV_01.HorseTo works when joined with the source table.
AV_01_Hist2.[HHorse1] is the field that I have manipulated with iif and this does not work when joined with the source table.

 

[Micron]

AV_01.HorseTo and AV_01_Hist2.[HHorse1] are both strings then?

AV_01_Hist2.[HHorse1] is the field that I have manipulated with iif

are you sure? HHorse is the alias (in case you're joining on it somewhere) and the field in the IIF portion seems to be ([HistAll]![HorseName]
If that's irrelevant or incorrect on my part, then all I can suggest is to copy this query and trim it back until things work, then test each time you add a portion back. Or validate the results of the expressions to ensure they are returning both the values and data types you expect.

 

[CJ_London]

you are not by any chance using lookups in your tables? they will generate errors like these if not handled correctly.

other points:
you say - AV_01_Hist2.[HHorse1] is the field that I have manipulated with iif
according to you your calculated value is called HHorse

 

[caprice63]

AV_01.HorseTo and AV_01_Hist2.[HHorse1] are both strings then?

are you sure? HHorse is the alias (in case you're joining on it somewhere) and the field in the IIF portion seems to be ([HistAll]![HorseName]
If that's irrelevant or incorrect on my part, then all I can suggest is to copy this query and trim it back until things work, then test each time you add a portion back. Or validate the results of the expressions to ensure they are returning both the values and data types you expect.

Thanks, I'll double check. Just off to bed

 

[caprice63]

you are not by any chance using lookups in your tables? they will generate errors like these if not handled correctly.

other points:
you say - AV_01_Hist2.[HHorse1] is the field that I have manipulated with iif
according to you your calculated value is called HHorse

Thanks for your reply. No lookups.

 

[The_Doc_Man]

I tried to decompose this so it would stand out a bit more for analysis purposes.

HHorse: CStr( 
  IIf( [COLOR="SeaGreen"]InStr( 1, [HistAll]![HorseName], "(" )=0[/COLOR],   '<-- condition
      [COLOR="Teal"][HistAll]![HorseName][/COLOR],      '<-- TRUE part of IIF
      Left( 
        [HistAll]![HorseName], 
        [COLOR="Blue"]InStr( 1, [HistAll]![HorseName], "(")-1[/COLOR]   '<-- position of LEFT selection 
      )              '<-- End of the LEFT function and FALSE part of IIF
    )            '<-- End of the IIF 
  )         '<-- End of the CSTR

First, why do you need CSTR? Unless the horse's names are all numbers, that function can only return strings anyway. And if it didn't work without it, then something else is badly wrong anyway because whether or not you DO need it, you shouldn't need it.

Second, it might be easier to use SPLIT, which returns a 0-based array of strings, and you only ever want the first one if I read this right.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/split-function

https://www.techonthenet.com/excel/formulas/split.php

https://www.exceltrick.com/formulas_macros/vba-split-function/

HHorse:  TRIM( SPLIT( [HistAll]![HorseName], "(", -1 )(0) )

This tells split to take a string like "Coolagh Moll (IRE)" and split it into "Coolagh Moll " and "IRE)" (dropping the leading "(" because it was the delimiter) - but the (0) subscript says only take the "Coolagh Moll " part, slot 0 of the 0-based string array. Then the TRIM removes the trailing space that used to be between "Moll" and "(IRE)." AND... if there is no "(" then the whole string ends up in the (0) element of the split.

That should simplify that beast pretty well.

 

[CJ_London]

@doc - don't think you can use the split function in a query

 

[arnelgp]

create a public function that will do the removal of "(" from your field and include the function on your AV_01_Hist2:

Public Function fncRemoveBracket(param As Variant) As Variant
'agp
'returns only string after the "(" (if there is)
'
    Dim pos As Long
    If IsNull(param) Then Exit Function
    param = param & ""
    fncRemoveBracket = param
    pos = InStr(param, "(")
    If pos > 0 Then _
        fncRemoveBracket = Trim(Left(param, pos - 1))
End Function

now the calculated field (HHorse) in AV_01_Hist2:

HHorse: fncRemoveBracket([HistAll].[HorseName])

 

[caprice63]

Thanks for all the suggestions.
I tried the SPLIT fn but was still getting errors. I did a Compact/Repair but still got errors. I trawled thro' my source tables and eventually found two rows that contained some null fields. I deleted these and hey presto!
I can now progress with the next steps of my data manipulation.

Thanks again

 

[caprice63]

I MUST remember to ALWAYS check for source data errors and also do Compact/Repair before I ask for help.

 

[arnelgp]

cheers! get your full refund, since you solved it by yourself.

 

[The_Doc_Man]

don't think you can use the split function in a query

If so, as Arnel pointed out, make a public function that can be used. SPLIT would work in that context. What Arnel proposed would also work.

 

[Micron]

cheers! get your full refund, since you solved it by yourself.

Not so fast. Read post2.

Where do I go to collect my prize?

 

[caprice63]

It was all good info. Helped me to remember some basics (data integrity) and taught me the use of SPLIT in public function.
Thanks again to EVERYONE

 

[arnelgp]

well, you can claim it to our boss, Mr.Jon or to our generous Mods.