Find last item in table with specific criteria

[shod90]

Dear Gents,
I have a table that stores some items like the following.

ID	Mask ID	Company
1	1A
2	2A
3	1B
4	3A

i Need a formula that returns the last maskID which ends with "B" , How should be done ?
Thanks

[Welshgasman]

Use DMax() ?

[CJ_London]

Suspect your example data is too simplistic. Last has no meaning without an order so need to know last in terms of what? Per your example that would in terms of ID and would return the record where ID=3.

And since your mask ID is a number followed by a letter, sorting will be a string sort so if you have

5B
22B
333B

5B will be last

[shod90]

Use DMax() ?

unfortunately you didn't read my post well

[shod90]

Suspect your example data is too simplistic. Last has no meaning without an order so need to know last in terms of what? Per your example that would in terms of ID and would return the record where ID=3.

And since your mask ID is a number followed by a letter, sorting will be a string sort so if you have

5B
22B
333B

5B will be last

Actually after 5B we should add 6B and 7B etc , But between them the other maskID which ends with A

[CJ_London]

OK so you will never go over 9B?

if you did, you are OK for adding ....4B, 5B, 6B, 7B, 8B,9B but if you add 10B, 9B will still be last

Or are you saying if your data looked like this, you still want 1B returned

ID	Mask ID	Company
1	1A
2	3B
3	1B
4	3A

[Welshgasman]

unfortunately you didn't read my post well

For what you posted that would work? I'm not a mind reader.
However you would still have the issue that 5B would be greater than 22B

[Gicu]

Please have a look at Query1 in attached file.

Cheers,

[shod90]

PERFECT , That's what I want , Thanks.

[Gicu]

You're very welcome, good luck with your project!

Cheers,