How to increment a number

[Gkirkup]

I have a document number which appears on certain documents, in the format QM00156.
I know how to format the number like that, my problem is that I need a source of the 'base' number (156 in this case), to incement when I need it to.
My work-around is to append a record to a 'number' table, and then use a count of the records, or the Max of autonumber. But that doesn't seem reliable, and I am sure there must be an easier method.
How do I get an incrementable number?

Robert

 

[datAdrenaline]

So ... is the data STORED as Text that is "QM00156"? ...

 

[EraserveAP]

In VBA you might try something like this:
Dim NumLng As Long, DocNum As String, strDocNum As String

strDocNum = "QM00156"

'Grab the number from the string
NumLng = Right(strDocNum, 5)

'Increment the Number by 1
NumLng = NumLng + 1


'Add the 00 to the QM as the leading Zeros will no longer be present and rebuild your
'Document Number
DocNum = "QM00" & NumLng

'Display the New Document Number
MsgBox DocNum

Of course you could make the code smarter but for a basic solution I think this is adequate.

 

[Gkirkup]

My apologies, I was not very clear. The formatted string really has nothing to do with this. My question is really: How do I grab a number from a table, and the next time I go to that table, have that number incremented by 1?

Robert

 

[EraserveAP]

you looking for VBA or Query?

 

[EraserveAP]

In a query as long as your data type is numeric, say a Long Integer, you can do [FieldName] +1 as an update query using specific criteria to single out 1 record.

Really need more input from you, otherwise the above VBA code would still work if you strip the strings out of it. Unless of course you are looking for a complete VBA solution with the recordset query and what not?