data type mismatch in criteria expression (1 Viewer)

caprice63

Registered User.
Local time
Today, 06:49
Joined
Nov 4, 2018
Messages
25
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

AWF VIP
Local time
Today, 01:49
Joined
Oct 20, 2018
Messages
3,471
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

Super Moderator
Staff member
Local time
Today, 06:49
Joined
Feb 19, 2013
Messages
12,771
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

Registered User.
Local time
Today, 06:49
Joined
Nov 4, 2018
Messages
25
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

Registered User.
Local time
Today, 06:49
Joined
Nov 4, 2018
Messages
25
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

AWF VIP
Local time
Today, 01:49
Joined
Oct 20, 2018
Messages
3,471
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

Super Moderator
Staff member
Local time
Today, 06:49
Joined
Feb 19, 2013
Messages
12,771
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

Registered User.
Local time
Today, 06:49
Joined
Nov 4, 2018
Messages
25
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

Registered User.
Local time
Today, 06:49
Joined
Nov 4, 2018
Messages
25
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

Immoderate Moderator
Staff member
Local time
Today, 00:49
Joined
Feb 28, 2001
Messages
18,735
I tried to decompose this so it would stand out a bit more for analysis purposes.

Code:
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/

Code:
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

Super Moderator
Staff member
Local time
Today, 06:49
Joined
Feb 19, 2013
Messages
12,771
@doc - don't think you can use the split function in a query
 

arnelgp

error reading drive A:
Local time
Today, 14:49
Joined
May 7, 2009
Messages
11,144
create a public function that will do the removal of "(" from your field and include the function on your AV_01_Hist2:
Code:
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:
Code:
HHorse: fncRemoveBracket([HistAll].[HorseName])
 

caprice63

Registered User.
Local time
Today, 06:49
Joined
Nov 4, 2018
Messages
25
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

Registered User.
Local time
Today, 06:49
Joined
Nov 4, 2018
Messages
25
I MUST remember to ALWAYS check for source data errors and also do Compact/Repair before I ask for help.
 

arnelgp

error reading drive A:
Local time
Today, 14:49
Joined
May 7, 2009
Messages
11,144
cheers! get your full refund, since you solved it by yourself.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:49
Joined
Feb 28, 2001
Messages
18,735
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.
 

caprice63

Registered User.
Local time
Today, 06:49
Joined
Nov 4, 2018
Messages
25
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

error reading drive A:
Local time
Today, 14:49
Joined
May 7, 2009
Messages
11,144
well, you can claim it to our boss, Mr.Jon or to our generous Mods.
 

Users who are viewing this thread

Top Bottom