Knowing Previous number (1 Viewer)

Ravi Kumar

Registered User.
Local time
Today, 07:36
Joined
Aug 22, 2019
Messages
162
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

AWF VIP
Local time
Yesterday, 18:06
Joined
Mar 9, 2014
Messages
5,423
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.
 
Last edited:

vba_php

Forum Troll
Local time
Yesterday, 21:06
Joined
Oct 6, 2019
Messages
2,884
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.
 

June7

AWF VIP
Local time
Yesterday, 18:06
Joined
Mar 9, 2014
Messages
5,423
vba_php described two scenarios. Which one applies to you? The second one doesn't seem feasible.
 

vba_php

Forum Troll
Local time
Yesterday, 21:06
Joined
Oct 6, 2019
Messages
2,884
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

Super Moderator
Staff member
Local time
Yesterday, 22:06
Joined
Feb 19, 2002
Messages
42,981
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

Registered User.
Local time
Today, 07:36
Joined
Aug 22, 2019
Messages
162
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

Registered User.
Local time
Today, 07:36
Joined
Aug 22, 2019
Messages
162
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.
 

Users who are viewing this thread

Top Bottom