puzzling data mismatch error

  • Thread starter Thread starter Jesper F
  • Start date Start date
J

Jesper F

I have a table where one of the fields contain values such as
26-14-2C-R1-K1
15-13-1C-R3-K6 and so on.
this is a text field

I am trying to find records based on the part: R1-K3, R2-K4, R5-K1
etc.
I am getting weird data type mismatch error when searching with my
query.

For example this works:
SELECT myfield as P FROM myTable
WHERE myfield Is Not Null AND right(myfield,len(myfield)-9) Like 'R1-
K1'
ORDER BY myfield

but this one using a nested OR doesnt and fails with a data mismatch
error:
SELECT myfield as P FROM myTable
WHERE myfield Is Not Null AND
(right(myfield,len(myfield)-9) Like 'R1-K1' OR
right(myfield,len(myfield)-9) Like 'R2-K2')
ORDER BY myfield

also where it seems that using LIKE works (as seen in the first
example)
using =sign fails with the data mismatch error as this doesnt work:
SELECT myfield as P FROM myTable
WHERE myfield Is Not Null AND right(myfield,len(myfield)-9) = 'R1-K1'
ORDER BY myfield

can someone explain what is happening?
Thanks.
 
just wondering, but why not use something like
SELECT myField
FROM myTable
WHERE myField LIKE "*R1-K3*";
Click to expand...

wow, don't know why I didn't think of this. I am using criteria for
searching within the first part of the field too,
but the Rx-Ky part is always at the end.
And to add to it - this now works! :

SELECT myField as p,
FROM myTable WHERE myTable.myField Is Not Null AND
(Left(myTable.PlaceringID,2)='26') AND (mid(myTable.PlaceringID,
3,2)='15') AND (mid(myTable.PlaceringID,5,2)='2C') AND (myField LIKE
'*R1-K1*' OR myField LIKE '*R1-K2*')

(extended as this is what I'm actually doing)
For the first section of the WHERE part I do need to search for 26 and
15 in certain places.
But it works now. Awesome - thanks for pointing me in that
direction :-)

Jesper
 
Back
Top