txt to # and back & keep leading zeros?

[brewpedals]

Hi all,

I have a sql statement I am using in a VBA sub that I intended to find the maximum value of the last three characters in a text field (JOB_NO) whose values are all in this format - "00L000". Example - "05J123"

It works fine when the last three characters represent a 3 digit number like "123", but fails when they are "002" - it returns the value "5J2".

Later in my code I increment the value (NextSeqNbr) by one to find the next available job number. How can I preserve the precedeing zeros?

I appreciate your help.

SQL Statement:
SELECT Max(Right([Project_Tracking].[JOB_NO],3)) AS NextSeqNbr FROM Project_Tracking HAVING ((((Left([Project_Tracking].[JOB_NO],2)=Right(Year(Now()),2)))));

 

[pbaldy]

Try:

Format([NextSeqNbr],"000")

 

[brewpedals]

That worked perfectly. Many thanks Paul.

 

[testdba]

What if

How would this be done if you wanted the "000" to be used up?

Format([NextSeqNbr],"000") would always give me three leading zeros, correct?

If my number were INV0001 and I used Format([NextSeqNbr],"000") what would I do when I had ten orders? How could I make INV00010 (one digit too many) into INV0010?

Also, would this have to be done through a query or could I do it more easily in VBA using textboxes?

 

[testdba]

I think I found what I was looking for.

http://www.access-programmers.co.uk/forums/showthread.php?t=50389&highlight=count+query