Custom generated text

[Chrism2]

Hi folks,

Can anyone help me work out how to generate some specific reference codes.

My company uses Purchase Order numbers in the following format:

08.09.011

mm.yy.###

I have a basic text field in my DB which I'd like to populate automagically when the user creates a new record.

Is there some useful code that could be used to:

Get the month for mm
Get the year for yy
Get the first available number based on three digits; resetting to 001 when mm changes.

Any help much appreciated!


C

 

[HiTechCoach]

Sure.



strMM = Format (date(),"MM")
strYY$ = Format (date(),"YY")

Use DmAx() to find the last value for the MM + DD

If not found use something like:


= strMM & "." & strYY & ".001"

if found then use something like:

= strMM & & "." & strYY & "." & Format(Val(Right([strLastValue],3)) + 1, "000")

 

[gemma-the-husky]

does the xxx run form 1 each month, or is it continuous.

its often best to split stuff like this into the prefix, and the sequential suffix - as if you store the hyphens it becomes a string - and you cant add 1 to a string.

if you store the bits separately, you can PRESENT them back WITH whatever spacing punctuation you like.

 

[Chrism2]

does the xxx run form 1 each month, or is it continuous.

its often best to split stuff like this into the prefix, and the sequential suffix - as if you store the hyphens it becomes a string - and you cant add 1 to a string.

if you store the bits separately, you can PRESENT them back WITH whatever spacing punctuation you like.

Yeah, that's the problem: the xxx runs monthly; so the first order of the month would always be 001 and then would add one each time a new order is placed. I'd like to retain this system as its been in use for years and I'm trying to make the transition painless.

Thanks, HiTech for the pointers - that's very helpful. I'll confess I'm stumped on the code to make the xxx digits work. If it were a case of adding 1 every time, I reckon I'd be okay. It's the resetting back to 001 that gets me!

PONumbers for March 09

03.09.001
03.09.002
03.09.003
etc

April

04.09.001
04.09.002
etc

At the moment, I'm storing [PONumber] as a string in tblPurchaseOrders. Should I be breaking this up?

Thanks in advance!

 

[gemma-the-husky]

you have two choices to do this

in both cases - store the month string and the counter separately

a) use a dmax

so now the dmax needs to look like

dmax("counter","table","month = whatever")

if there isnt one for this month, it will return a null, so you need to deal with this by using a nz

nextnumber = nz(dmax("counter","table","month = whatever"),0) + 1

(note that this methodology is bascially the same, even if you never reset the sequence. Its just that in that case, you wouldnt need the where (red) bit in the dmax.


b) store the counter in a separate table

then you need to add code to look up the last/next number form the table, and increment it as you use numbers. these would give you more control over the number sequence - as you can set the control to whatever you like.

 

[Chrism2]

you have two choices to do this

in both cases - store the month string and the counter separately

a) use a dmax

so now the dmax needs to look like

dmax("counter","table","month = whatever")

if there isnt one for this month, it will return a null, so you need to deal with this by using a nz

nextnumber = nz(dmax("counter","table","month = whatever"),0) + 1

(note that this methodology is bascially the same, even if you never reset the sequence. Its just that in that case, you wouldnt need the where (red) bit in the dmax.


b) store the counter in a separate table

then you need to add code to look up the last/next number form the table, and increment it as you use numbers. these would give you more control over the number sequence - as you can set the control to whatever you like.

Thanks for all your help - I've used the above suggestions to create the below which does excatly what I need.

Dim strMM As String
Dim strYY As String
Dim strNNN As String

strYY = Format(Me.PODate, "YY")
strMM = Format(Me.PODate, "MM")

strNNN = Nz(DMax("[POSuffix]", "tblPurchaseOrders", "Month([PODate]) = " & strMM), 0) + 1
strNNN = Format(strNNN, "000")

'Output

'MsgBox strMM & "." & strYY & "." & strNNN

Thanks so much again!

 

[gemma-the-husky]

this will be ok because strmm is a number

note that if you used a string then you would need the strmm to be surrounded by quote chars (there's a single quote hidden between the dble quotes)

"Month([PODate]) = '" & strMM & "'"

and you will see this sort of thing