Filtering query based on last two digits of string

[litrelord]

Hi,

I'm pretty new to designing in access and wasn't sure what to search for so please go easy on me if this is obvious/has been answered many times.

I have a table of data in my query and I want to filter this table by one of the columns. The column contains a string of data which ends in a two digit figure that represents the year eg abd123403 for 2003 then abc123404 for 2004.

I need to find a way of showing just those codes which end in 03 which don't have code for 04. Using the above example abc123403 would not be shown as abc123404 is present. However, if I were to delete abc123404 and run the query then it would show abc123403 and I would know to create it's successor.

Sorry for the repeated explanation.

If anyone wouldn't mind walking me through this I'd be extremely grateful . Even just pointing me in the general direction may help.

Anyway, thanks for taking the time to read this.

Regards

Nick

 

[jobb]

I'll take a stab at this..

maybe if you designed a query which broke your code up into two calculated fields

CodeLeft: Left([yourcode], (Len([yourcode])-2))
CodeRight: Right([yourcode],2)

the next step, I'm not sure if you could do this within the same query, or if you would have to write another query based on the above, but

Group By: CodeLeft
Max: CodeRight

then write a query based on THAT query with the criterion Like"*03" for CodeRight.

Let me know if that makes sense. There is probably an easier way though.

 

[bcmcjeep]

I had a similar siutation that the board members helped me with.
I put this into a column of a query.
SortField: Right$([name of table].[name of column],2)
Then sorted on the last 2 digits.
blc