Link to home
Create AccountLog in
Avatar of lakhi
lakhi

asked on

String function in MS Access Query

I need to pull all of the records from a database table where the 4th character from the right is the letter "S" and I tried "Where Right(Key_Field, 4) = 'S" but it doesn't work
What am I doing wrong?
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

<<I need to pull all of the records from a database table where the 4th character from the right is the letter "S" and I tried "Where Right(Key_Field, 4) = 'S" but it doesn't work>>

  That returns the right 4 characters, not a single character.  Do:

"Where Left(Right(Key_Field, 4),1) = 'S'"

JimD.
SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
slow fingers :)

Note that if you need it to be case-sensitive:

SELECT *
FROM [SomeTable]
WHERE StrComp(Mid(StrReverse([Key_Field]), 4, 1), "S", 0) = 0

Open in new window


Patrick
MX,

Very clever!  If a case-sensitive comparison is needed...

WHERE Instr(1, StrReverse([Key_Field]), "S", 0) = 4

Open in new window


Jim,

Liking your too, but I think it potentially turns up false positives with original strings with 3 or fewer characters.  For example, if the original string is "SSS", then your expression would validate that.

Patrick
Avatar of lakhi
lakhi

ASKER

Thanks so much. Just what I needed. Works great.
<<Jim,

Liking your too, but I think it potentially turns up false positives with original strings with 3 or fewer characters.  For example, if the original string is "SSS", then your expression would validate that.
>>

 Yes, I did make the assumption that there would be at least 4 characters in the field.  Still may want to go with that if that is the case and the recordset is large.  A Right() and Left() would be faster then reversing a string and then doing Mid() or instr.

  Might not make much of a difference though if the string is on the small side.

JimD.