Knowing Previous number

[Ravi Kumar]

hello all ,
In my form the first field to enter is kaizen number, & I have 5 depts., so the number would be a1,b1,c1,d1&e1 and so on .
Now as the list is getting long, I have to check each time what was the last number given to a particular dept. & I have to write the next number.
Is there any way to simplify this problem??
Thank you in advance.

[June7]

Certainly can be dealt with.

Alpha sort rules apply. a15 will sort before a2 so if you searched for Max(fieldname), a2 would return instead of a15. Placeholder zeros can assure sequential order: a00001 … a00015.

So if you stored identifier parts in separate fields (Prefix, Number) an expression can concatenate to a sequential value when needed:

Prefix & Format(Number, "00000")

Or code can split value and recombine with formatting when needed.

[vba_php]

Originally Posted by Ravi Kumar

I have to check each time what was the last number given to a particular dept. & I have to write the next number.

r u saying that your next numbers would be like "a2, a3, etc..." for records pertaining to your 1st department and "b2, b3, etc..." for the 2nd department? or r u saying that each future record would need to be "f1, g1, h1, etc..."?

June is giving a solution for the 1st scenario.

[Ravi Kumar]

Yes Sir it is exactly as you described...

[June7]

vba_php described two scenarios. Which one applies to you? The second one doesn't seem feasible.

[vba_php]

Originally Posted by Ravi Kumar

Yes Sir it is exactly as you described...

Ravi,

This answer of yours is very vague! You didn't answer my question, or June's latest message. Please clarify WHICH situation I described applies to your situation. That way we can help you.

[Pat Hartman]

Start by keeping the two fields separate. Then you can use a DMax() to find the highest numeric value for the department and add 1 to generate the next number.

Me.NextNum = Nz(DMax("SeqNum", "yourtable", "DeptLetter = '" & Me.DeptLetter & "'"), 0) +1

[Ravi Kumar]

Originally Posted by vba_php

Ravi,

This answer of yours is very vague! You didn't answer my question, or June's latest message. Please clarify WHICH situation I described applies to your situation. That way we can help you.

Hello sir , I am very sorry , I was not in town hence I couldn't answer to your questions.
My requirement is like this : While entering kaizen details I give unique names to it for index purpose like for production department it would be PRD-01 , PRD-02 and for calibration department CD-01,CD-02 & so on .
As now it has become sufficiently big , before giving the number I need to first look at the last number allotted in my datasheet ,again come back to form & enter the same .

So I need to know whether this can be simplified in any way ??

[Ravi Kumar]

Originally Posted by Pat Hartman

Start by keeping the two fields separate. Then you can use a DMax() to find the highest numeric value for the department and add 1 to generate the next number.

Me.NextNum = Nz(DMax("SeqNum", "yourtable", "DeptLetter = '" & Me.DeptLetter & "'"), 0) +1

Hello sir thank you. I Will try & let you know the result.