[b]Dmax number in a string field. Get 10 instead of 9 value[/b]

[CTOROCK]

I have a field that have values such as: Q1, Q2, Q3, Q4 etc. I am trying to find the max value of a field and +1. It is a string and i know that in a string a 9 is greater than 10 but I can't seem to get around it. So when it's Q10, the max is Q9. I would like the max to be 10, not 9. I'm trying this:

Dim strRefNo As String

strRefNo = CInt(Mid(DMax("QRefNo", "tblCommissionQueries"), 2))

DoCmd.GoToRecord , , acNewRec

txtQRefNo.Locked = False
txtQRefNo.Value = "Q" & (strRefNo + 1)

txtQRefNo.Locked = True
Me.Refresh

I've also tried:

DMax("CLng([QRefNo]))", "tblCommissionQueries") + 1

and

DMax(CLng((Mid("QRefNo", 2))), "tblCommissionQueries")

or

Mid(DMax("QRefNo", "tblCommissionQueries"), 2)

But I still can't get it to go past 9. Any suggestions would be apreciated. Thanks in advance for any help.

The greatest risk, is not taking one.

 

[Remou]

this would appear to be an Access question forum705.

Try:

DMax("CLng(Mid(QRefNo, 2))", "tblCommissionQueries")

 

[CTOROCK]

Thanks a lot, that works!

The greatest risk, is not taking one.