Finding max partial string

[Coolboy55]

I have the following:

Set Rec = CurrentDb.OpenRecordset("SELECT Max([AssignedNumber]) As MaxNumber FROM Models WHERE [AssignedNumber] Like " & Chr(34) & "?????####" & Chr(34))

AssignedNumber is a text format number of the form ?????####. I want to find the maximum #### regardless of the ????? part. How do I modify my query?

Thanks!

CB55

[Stewart Ross]

Hi CB55. Assuming that you always have the rightmost four digits, as I think you are indicating by the use of the #### symbols, you can use:

SELECT Max(Val(right$([AssignedNumber], 4))) As MaxNumber

I won't ask you why it is a text format number in two parts...

-Stewart

[Coolboy55]

Hi CB55. Assuming that you always have the rightmost four digits, as I think you are indicating by the use of the #### symbols, you can use:

SELECT Max(Val(right$([AssignedNumber], 4))) As MaxNumber

I won't ask you why it is a text format number in two parts...

-Stewart

Thanks Stewart! The number format was voted on by a group of people who know almost nothing about programming. My protests were overruled. :(