Prev: How to enter multiple RTF lines in memo text box
From: Jesper F on 7 Jun 2010 16:00
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.
From: PieterLinden via AccessMonster.com on 7 Jun 2010 16:26
Jesper,
just wondering, but why not use something like

SELECT myField
FROM myTable
WHERE myField LIKE "*R1-K3*";

the only reason I can see for using Right$ and Mid$ (I would use the string
version, not the variant version) is if you can have the string you're
searching for appear in multiple places in the field.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201006/1

From: Jesper F on 7 Jun 2010 16:56
> just wondering, but why not use something like
> SELECT myField
> FROM myTable
> WHERE myField LIKE "*R1-K3*";

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
 | 
Pages: 1
Prev: How to enter multiple RTF lines in memo text box