Find text value at position 46,47,48,49 in a 210 character field.

[jlig via AccessMonster.com]

I have an Access table with only 1 field called Field1.

I need to locate all records that have the text 5483 at position 46,47,48,49
..

Field1 is actually 210 characters long but is all text.

I looked at Inst, Mid, etc in my query but don't see how they can locate the
Position?

Sorry for the ignorance on this one,

Thanks in advance.
jlig

 

[Guest]

You were on the right track. The Mid function will do this; its second
argument is the start position and the third the length of the string to be
returned, so a query along the following lines will return the relevant rows:

SELECT Field1
FROM YourTable
WHERE Mid(Field1,46,4) = "5483";

 

[Larry Linson]

I have an Access table with only 1 field called Field1.

I need to locate all records that have the text 5483 at position
46,47,48,49
.

Field1 is actually 210 characters long but is all text.

I looked at Inst, Mid, etc in my query but don't see how they can locate
the
Position?

I think you need to be clear when asking your question. I am inferring that
you want a Query that will retrieve each record with this value in the
specified position. Create a calculated Field in the Query, as follows

TheText: Mid([Field1],46,4)

Then specify criteria for that Calculated Field of "5483".

That should return the records you want.

Larry Linson
Microsoft Access MVP

 

[jlig via AccessMonster.com]

Ken & Larry,
Your simple solution worked perfectly. I had forgotten the optional "Length"
part of Mid.

I used :
SELECT UnionAll3.Field1
FROM UnionAll3
WHERE (((Mid([Field1],46,4))="5483"));
in my query.
Thanks again!
jlig

I have an Access table with only 1 field called Field1.

[quoted text clipped - 7 lines]

the
Position?

I think you need to be clear when asking your question. I am inferring that
you want a Query that will retrieve each record with this value in the
specified position. Create a calculated Field in the Query, as follows

TheText: Mid([Field1],46,4)

Then specify criteria for that Calculated Field of "5483".

That should return the records you want.

Larry Linson
Microsoft Access MVP