DMax Issues - If DMax is what i want

[indesisiv]

I am trying to use DMax to sort out some number issues i have. Here is what i want to do.

I have a field (Text) made up of a 4 letter code (AAAA) then a number.

WHat i want to do is grab the highest number from this. where the code matches what is in a text box.

I have got the following fields:
[Main Table]![Project No] = AAAA### (letter code and then number)
txtType - I set this to be the 4 letter code i want to find the highest number
txtHighestNumber - I want to set this to the highest number.

I am trying to do something like this but it keeps giving me errors. I think i may be over complicating it:

Me.txtHighNumber = DMax(Right("[Main Table]![Project No]", (Len("[Main Table]![Project No]") - 4)), "[Main Table]") ', Left("[Main Table]![Project No]", 4) & "=" & Me.txtType)

Please Help!!

 

[namliam]

I would probably just query it... having a query is so much easier if you are manipulating fields.
Dmax generaly works with "full fields", you could try something like:

Me.txtHighNumber = DMax("[Project No]", "[Main Table]", "Left([Main Table]![Project No], 4) = '" & Me.txtType & "'")

 

[indesisiv]

I'll have a play with queries later .. in the mean time thanks for the code .. it works great.