Custom Number Field

[MattBaldry]

Hi All,

I am designed a new returns database at work. To generate a returns number I would like it to be something like below.

20-09-001 where the 20 is the year, 09 is the month and 001 is the number of return for that year and month.

I can work this fine using DMax, but what I ideally want is each month the return count starts again at 001.

This way we can monitor which months are generating the most returns at a quick glance.

In my table I am thinking I need 3 fields, ReturnNumber, ReturnYear, ReturnMonth. I then display these in the format above for my list.

The part I am struggling with is the ReturnNumber part resetting in a new month.

Am I looking at this too complex? Is there any easier way?

~Matt

 

[arnelgp]

have a look at this.
check the code generator in Module1.
add some record to the form.

 

[MattBaldry]

have a look at this.
check the code generator in Module1.
add some record to the form.

Perfect, thank you. You're a star.

~Matt

 

[arnelgp]

you're welcome!

 

[namliam]

Dont want to knock @arnelgp 's work, his solution certainly does work, but custom keys are worse than bad, make sure you use an autonumber inside your database as the "real" primary key.

While @arnelgp 's work works, I disagree with his approach. Because it limits you to 0001 = 9999 returns, what if the company grows and goes above that number of returns?
Not to mention it destroys data, data can only be destroyed once unfortunatly. Detroying data on average is a bad idea.


I would make 2 possibly 3 fields...
ReturnDate, date field storing the full date
YourSequenceNumber, integer holding anything from 1 to 9999999999

Third possible field
YourYearMonth field, to store YY-MM to make selecting a little more easy. This is not strictly neede but makes it a little easier.
Or as per the OP you could further split this into 2 fields to hold the full year and month, both being integers....
Strictly speaking the ReturnDate though should be enough.

now entire function can be reduced to :

dcount("*","Table1","YourYearMonth = """ & Format(Date, "yy-mm") & """") +1

or if you split it to year and month
dcount("*","Table1","yourYear = " & Format(Date, "yyyy") & " and yourMonth = " & Format(Date, "mm") ) +1

Since Dcount always will return 0 when nothing is found, where DMax will return a NULL value, there is no need for a "complicated" catch to reset YourSequenceNumber.

Anyway, my 2 cents.

 

[MattBaldry]

Dont want to knock @arnelgp 's work, his solution certainly does work, but custom keys are worse than bad, make sure you use an autonumber inside your database as the "real" primary key.

While @arnelgp 's work works, I disagree with his approach. Because it limits you to 0001 = 9999 returns, what if the company grows and goes above that number of returns?
Not to mention it destroys data, data can only be destroyed once unfortunatly. Detroying data on average is a bad idea.


I would make 2 possibly 3 fields...
ReturnDate, date field storing the full date
YourSequenceNumber, integer holding anything from 1 to 9999999999

Third possible field
YourYearMonth field, to store YY-MM to make selecting a little more easy. This is not strictly neede but makes it a little easier.
Or as per the OP you could further split this into 2 fields to hold the full year and month, both being integers....
Strictly speaking the ReturnDate though should be enough.

now entire function can be reduced to :

dcount("*","Table1","YourYearMonth = """ & Format(Date, "yy-mm") & """") +1

or if you split it to year and month
dcount("*","Table1","yourYear = " & Format(Date, "yyyy") & " and yourMonth = " & Format(Date, "mm") ) +1

Since Dcount always will return 0 when nothing is found, where DMax will return a NULL value, there is no need for a "complicated" catch to reset YourSequenceNumber.

Anyway, my 2 cents.

Thank you. I shall give this way a try. The previous one does work, but only if the form stays open.

If I close the form and open it to add more numbers, it just keeps adding the same last number.

~Matt

 

[MattBaldry]

Dont want to knock @arnelgp 's work, his solution certainly does work, but custom keys are worse than bad, make sure you use an autonumber inside your database as the "real" primary key.

While @arnelgp 's work works, I disagree with his approach. Because it limits you to 0001 = 9999 returns, what if the company grows and goes above that number of returns?
Not to mention it destroys data, data can only be destroyed once unfortunatly. Detroying data on average is a bad idea.


I would make 2 possibly 3 fields...
ReturnDate, date field storing the full date
YourSequenceNumber, integer holding anything from 1 to 9999999999

Third possible field
YourYearMonth field, to store YY-MM to make selecting a little more easy. This is not strictly neede but makes it a little easier.
Or as per the OP you could further split this into 2 fields to hold the full year and month, both being integers....
Strictly speaking the ReturnDate though should be enough.

now entire function can be reduced to :

dcount("*","Table1","YourYearMonth = """ & Format(Date, "yy-mm") & """") +1

or if you split it to year and month
dcount("*","Table1","yourYear = " & Format(Date, "yyyy") & " and yourMonth = " & Format(Date, "mm") ) +1

Since Dcount always will return 0 when nothing is found, where DMax will return a NULL value, there is no need for a "complicated" catch to reset YourSequenceNumber.

Anyway, my 2 cents.

Just to add, I do have an autonumber ID field that is the primary key and the relationship key between the other tables.

So, my original idea of having the 3 different fields looks okay. I shall try out your method tomorrow.

~Matt

 

[namliam]

Well 3 fields is a good idea... 2 fields is a good idea, your sequence number and your date field. At the root you only need these 2 fields.

Having 3 fields, year, month, sequence is a halfway solution... your still destroying information. (the day part the date) which is generaly a bad idea.

 

[MattBaldry]

Well 3 fields is a good idea... 2 fields is a good idea, your sequence number and your date field. At the root you only need these 2 fields.

Having 3 fields, year, month, sequence is a halfway solution... your still destroying information. (the day part the date) which is generaly a bad idea.

Hi There,

I tested your code above but it is not working I'm afraid.

If there is no data in the table, it makes the first ReturnNumber as 1. But after that it just fails, Data Type Mismatch.

Looking at the code I cannot see where it is looking to check the YearMonth first to see if it needs to add to that months ReturnNumber or start again at 1 for the new month.

I do appreciate the help people post in here and I always learn code offered to me before using it.

I had tried to work from the below link, but got lost somewhere.

Custom autonumbers

~Matt

 

[Gasman]

Personally I would be using separate fields.?
If you return a 0 then add new year and month record with a value of 1 and use that 1 to start with.?
Next time you should get 2 and so on.?

You do of course need to update the table with the last value as well?

 

[Gasman]

However, I have just downloaded arnelgp's db and it works fine for apart from subsequent records being a digit short, so I have corrected that.?

Public Function fCustomGenerator() As String

    Const TABLE_NAME As String = "[Table1]"
    Const FIELD_NAME As String = "[CustomNumber]"
    
    Dim strValue As String
    
    strValue = Nz(DMax("Mid(" & FIELD_NAME & ",7)", TABLE_NAME, "Left(" & FIELD_NAME & ",5)='" & Format(Date, "yy-mm") & "'"), "0")
    If strValue = "0" Then
        fCustomGenerator = Format(Date, "yy-mm-0001")
    
    Else
        fCustomGenerator = Format(Date, "yy-mm-") & Format(Val(strValue) + 1, "0000")
    End If
End Function

 

[MattBaldry]

However, I have just downloaded arnelgp's db and it works fine for apart from subsequent records being a digit short, so I have corrected that.?

Public Function fCustomGenerator() As String

    Const TABLE_NAME As String = "[Table1]"
    Const FIELD_NAME As String = "[CustomNumber]"
   
    Dim strValue As String
   
    strValue = Nz(DMax("Mid(" & FIELD_NAME & ",7)", TABLE_NAME, "Left(" & FIELD_NAME & ",5)='" & Format(Date, "yy-mm") & "'"), "0")
    If strValue = "0" Then
        fCustomGenerator = Format(Date, "yy-mm-0001")
   
    Else
        fCustomGenerator = Format(Date, "yy-mm-") & Format(Val(strValue) + 1, "0000")
    End If
End Function

Thanks Gasman,

It does work, but after looking I think I need to remove the - from the number as it looks too much like a date. I also only need 2 digits in the sequence. We have only had 180 returns in the last 6 years so I am not too worried about only having 99 per month.

I have tried tinkering but I keep breaking it

~Matt

 

[Gasman]

Substitute the - for underscores then?

Public Function fCustomGenerator() As String

    Const TABLE_NAME As String = "[Table1]"
    Const FIELD_NAME As String = "[CustomNumber]"
  
    Dim strValue As String
  
    strValue = Nz(DMax("Mid(" & FIELD_NAME & ",7)", TABLE_NAME, "Left(" & FIELD_NAME & ",5)='" & Format(Date, "yy_mm") & "'"), "0")
    If strValue = "0" Then
        fCustomGenerator = Format(Date, "yy_mm_01")
  
    Else
        fCustomGenerator = Format(Date, "yy_mm_") & Format(Val(strValue) + 1, "00")
    End If
End Function

You could probably get away with

  Const TABLE_NAME As String = "[Table1]"
    Const FIELD_NAME As String = "[CustomNumber]"
  
    Dim strValue As String
  
    strValue = Nz(DMax("Mid(" & FIELD_NAME & ",7)", TABLE_NAME, "Left(" & FIELD_NAME & ",5)='" & Format(Date, "yy_mm") & "'"), "0")
    fCustomGenerator = Format(Date, "yy_mm_") & Format(Val(strValue) + 1, "00")
    
End Function

 

[MattBaldry]

Substitute the - for underscores then?

Public Function fCustomGenerator() As String

    Const TABLE_NAME As String = "[Table1]"
    Const FIELD_NAME As String = "[CustomNumber]"

    Dim strValue As String

    strValue = Nz(DMax("Mid(" & FIELD_NAME & ",7)", TABLE_NAME, "Left(" & FIELD_NAME & ",5)='" & Format(Date, "yy_mm") & "'"), "0")
    If strValue = "0" Then
        fCustomGenerator = Format(Date, "yy_mm_01")

    Else
        fCustomGenerator = Format(Date, "yy_mm_") & Format(Val(strValue) + 1, "00")
    End If
End Function

You could probably get away with

  Const TABLE_NAME As String = "[Table1]"
    Const FIELD_NAME As String = "[CustomNumber]"

    Dim strValue As String

    strValue = Nz(DMax("Mid(" & FIELD_NAME & ",7)", TABLE_NAME, "Left(" & FIELD_NAME & ",5)='" & Format(Date, "yy_mm") & "'"), "0")
    fCustomGenerator = Format(Date, "yy_mm_") & Format(Val(strValue) + 1, "00")
  
End Function

Both methods do work fine, but is there no way of removing the separator? Or, is it possible to remove it on display only, and have the data itself contain them.

EDIT: I have figured it out. Left,Mid,Right and I can then display it without the - separator. Thank you all for your help.

~Matt

 

[Gasman]

Well yes, you can remove the separator, then just adjust the value arnelgp used to get the number part accordingly.? otherwise you will break it. again.
Don't use Left,Mid & Right all the time. Set it as you want it in that function.

 

[Pat Hartman]

Using Max()/Min() on a text field will give you surprising results. For example, 9 will be > 100 because text fields are sorted character by character, left to right and 9 is > 1. End of story.

Your sequence number field needs to be numeric for the dMax() to work. Therefore, I would NOT mush all three parts together. As the others have mentioned, you can keep yy-mm in one field if you want because the year is first and will be two digits and month is formatted with a leading zero so it will compare correctly.

If you zero fill the text field, 9 will end up as 009 and so will be < 100 but as someone already pointed out, zero-filling will limit the number of returns. If you zero-fill, I would use at least 5 digits so you never have to think about this again. The company can grow substantially before this length becomes a problem. Amazon couldn't live with it but your company can.

@namliam,
dCount() is dangerous in this case unless deletes are not allowed. Even if deletes are not allowed, I wouldn't use it just on principle. The safer solution is dMax() with an embedded NZ() to handle the first return for each month. dMax() makes the intention of the code clear. You want the largest value.

 

[namliam]

@Pat Hartman
How would deletes be allowed in this case?? Or most any case, surely one would mark records as "obsolete" or "in error" or what not over deleting data.... Agree that DMax will guarantee results better than DCount on the other hand proper data management should be enforced....

 

[MattBaldry]

Using Max()/Min() on a text field will give you surprising results. For example, 9 will be > 100 because text fields are sorted character by character, left to right and 9 is > 1. End of story.

Your sequence number field needs to be numeric for the dMax() to work. Therefore, I would NOT mush all three parts together. As the others have mentioned, you can keep yy-mm in one field if you want because the year is first and will be two digits and month is formatted with a leading zero so it will compare correctly.

If you zero fill the text field, 9 will end up as 009 and so will be < 100 but as someone already pointed out, zero-filling will limit the number of returns. If you zero-fill, I would use at least 5 digits so you never have to think about this again. The company can grow substantially before this length becomes a problem. Amazon couldn't live with it but your company can.

@namliam,
dCount() is dangerous in this case unless deletes are not allowed. Even if deletes are not allowed, I wouldn't use it just on principle. The safer solution is dMax() with an embedded NZ() to handle the first return for each month. dMax() makes the intention of the code clear. You want the largest value.

Hi Pat,

I understand your reasoning, but I am struggling to implement this. I have the fields ReturnID (Autonumber Primary Key), ReturnDate (Date/Time) and ReturnNumber (Number).

So am I right in thinking your suggestion is to have the function check the date and then increase the sequence in ReturnNumber if there is already one from that Date/Month, but start a new sequence if not.

How could I function this please?

EDIT: I don't allow deletes in my database. I mark them as obsolete and they are hidden from any listboxes, but can still be found in the backend.

Regards,

~Matt

 

[Pat Hartman]

Your expression will look something like this:

NewSeq = Nz(dMax("SeqNum", "yourtable", "Format(YourDate, """yymm""") = '" & Format(Date, "yymm") & "'", 0) +1

The expression is enclosed in a Nz() function to convert null to 0 because the dMax() will return null if no entry is found for the given date. Since you are keeping a date in the table rather than YYMM, it is necessary to convert the date to yymm to compare it to the yymm of the current date. Hopefully the " are all properly paired. I didn't test the expression.

 

[The_Doc_Man]

EDIT: I don't allow deletes in my database. I mark them as obsolete and they are hidden from any listboxes, but can still be found in the backend.

Good choice. If you ever ran into auditor issues, they would agree with you. Hide what you don't want to see, don't delete it. It is far easier to unhide than to recover.