Parsing A Field

[carl]

I have a field that contains values like this

1234be567
2222cd111
3333ab333

I need to filter on character 5 and 6 ("be" for example).

Can I parse the field ? Is there a way to filter on just the 5th and 6th
characters ?

BTW. The database has 4 milllion entries so moving it to excel is not
possible.

Thank you in advance.

 

[Chaim]

You can extract just characters 5 and 6 using the mid function. As in:

ORDER BY mid ([Your Field], 5, 2)

The first number says "start at this character". The second number is for
how many characters.

Good Luck!

 

[Chriske911]

I have a field that contains values like this

1234be567
2222cd111
3333ab333

I need to filter on character 5 and 6 ("be" for example).

Can I parse the field ? Is there a way to filter on just the 5th and 6th
characters ?

BTW. The database has 4 milllion entries so moving it to excel is not
possible.

Thank you in advance.

create a query holding all the required fields
then add a new field using this as field description:
customdata: mid([fieldnameofchoice],5,2)
then set the criteria to whatever you are searching for
or use a parameter as criteria

grtz

 

[Kai Apel \(Berlin\)]

Did it means after the first 4 numbers comes the 2 characters? Then you
could try this:
Left(Right([YourTable]![YourField];Len([YourTable]![YourField])-4);2) as
Expression:
4 is the count of your 4 first numbers, then follow 2 characters?!

Greets
Kai Apel (Berlin)

 

[Kai Apel \(Berlin\)]

you´re right, that´s the shorter way!

Kai

 

[Larry Linson]

Look in Help for the Mid function. That should do exactly what you want.

SELECT * FROM yourtable WHERE Mid(yourfield,5,2) = "LL";

should return all those records with "LL" in the 5th and 6th character
position of the field named "yourfield".

Larry Linson
Microsoft Access MVP