Solved How can I (1 Viewer)

[yunhsuan]

Hello
I want to make floating numbers, composed by date and serial number.
I have succeed to make floating numbers, which serial numbers are reset every day.
Actually, the serial numbers need to be reset every month.
How can I revise the code in this access?

 

[theDBguy]

Hi. Welcome to AWF!

Unfortunately, when I downloaded and tried to open your attachment, I got a warning message that it was corrupted. I can't see any of your objects.

 

[plog]

I get a warning as well but can see your objects. However their names aren't using the Latin alphabet (abcdef...) so I can't make sense of anything. So I can just give you general advice:

1. Don't do this. Just use an autonumber and be done with it. The ultimate purpose of a serial numbering system is to provide uniqueness. An autonumber is guaranteed to do this. Yes, it won't be "pretty" or conform to any particluar format, but its easy and serves its purpose. Encoding other data into it (date, ordinality), really does nothing in the big picture--you have a database if that other information is needed you can use the autonumber to look it up and get all that data and more from the database.

2. After you disregard the great advice in #1, you need 2 fields in your table to accomodate this. An immutable date field ([SerialDate]) and a number field ([SerialNumber]). Then you need a function to get the "next" [SerialNumber]. You would pass the function the [SerialDate] of a new serial number, the function would use a DMax(https://www.techonthenet.com/access/functions/domain/dmax.php) to find the highest [SerialNumber] in the database for the day/month of the [SerialDate] value. If its NULL the function returns 1, otherwise the function adds 1 to DMax and returns it and that's your new [SerialNumber].

A couple issues that might arise--records get deleted--that means you might get gaps in your [SerialNumber] values for a day/month and there goes your "pretty" system. Typos and data changes happen to--if [SerialDate] gets changed [SerialNumber] won't, meaning it can appear out of order, or even create duplicates if the [SerialDate] is moved backwards to a time period already using that serial number.

Again, though, go with #1.

 

[yunhsuan]

Sorry...I forgot to change my code to Eng.

Unfortunately, the rule of this serial number is set several years ago. So I am bound to use the advice in #2.

Here is the code I used.

Private Sub SerialDate_AfterUpdate()
1001 Z = DMax("SerialNumber", "Table1")
1002 If IsNull(Z) = False Then
1003 Me![SerialNumber] = Format(Year(Me![SerialDate]) - 1911, "000") & Format(Me![SerialDate], "mmdd") & Format(CStr(Val(Right(Z, 3) + 1)), "000")
1004 Else
1005 Me![SerialNumber] = Format(Year(Me![SerialDate]) - 1911, "000") & Format(Me![SerialDate], "mmdd") & "001"
1006 End If
End Sub

I hope result look like this
1100801001
1100801002
1100802003
1100803004
.
.
.
1100901001
1100901002
(110 is the same as 2021in my country)

But, I don't how to find the max value of the serial number monthly.

 

[plog]

DMax("SerialNumber", "Table1")
...Format(CStr(Val(Right(Z, 3) + 1)), "000")

Do you have a SerialDate field in Table1? If so, you will need to include it in the criteria argument of DMax so your numbers reset each time period. If not, then you need to construct a criteria filter such that you can filter SerialNumber to just those with a similar prefix of the one you are constructing. Much much easier with 2 seperate fields. Much much much much easier to use autonumbers.

 

[arnelgp]

this will reset every month:

Private Sub SerialDate_AfterUpdate()
1001 Z = DMax("SerialNumber", "Table1", "Val(Left([SerialDate],3))+1911=Year(Date()) And Mid([SerialDate],4,2) = '" & Format(Date(),"mm") & "'")
1002 If IsNull(Z) = False Then
1003 Me![SerialNumber] = Format(Year(Me![SerialDate]) - 1911, "000") & Format(Me![SerialDate], "mmdd") & Format(CStr(Val(Right(Z, 3) + 1)), "000")
1004 Else
1005 Me![SerialNumber] = Format(Year(Me![SerialDate]) - 1911, "000") & Format(Me![SerialDate], "mmdd") & "001"
1006 End If
End Sub

 

[yunhsuan]

DMax("SerialNumber", "Table1")
...Format(CStr(Val(Right(Z, 3) + 1)), "000")

Do you have a SerialDate field in Table1? If so, you will need to include it in the criteria argument of DMax so your numbers reset each time period. If not, then you need to construct a criteria filter such that you can filter SerialNumber to just those with a similar prefix of the one you are constructing. Much much easier with 2 seperate fields. Much much much much easier to use autonumbers.

There are SerialDate field and SerialNumber field in Table1.
I have corrected my access file below.

 

[yunhsuan]

this will reset every month:

Private Sub SerialDate_AfterUpdate()
1001 Z = DMax("SerialNumber", "Table1", "Val(Left([SerialDate],3))+1911=Year(Date()) And Mid([SerialDate],4,2) = '" & Format(Date(),"mm") & "'")
1002 If IsNull(Z) = False Then
1003 Me![SerialNumber] = Format(Year(Me![SerialDate]) - 1911, "000") & Format(Me![SerialDate], "mmdd") & Format(CStr(Val(Right(Z, 3) + 1)), "000")
1004 Else
1005 Me![SerialNumber] = Format(Year(Me![SerialDate]) - 1911, "000") & Format(Me![SerialDate], "mmdd") & "001"
1006 End If
End Sub

I just tried this code.
But the last 3 number keep 001 in the same day.
ex: 1100903001, 1100903001
I hope the last 3 number is serial number.
ex:
1100801001
1100801002
1100802003
1100803004
.
.
.
1100901001
1100901002

 

[arnelgp]

your table should have SerialDate (date/time)
and SerialNumber (string).
see this demo.

 

[yunhsuan]

your table should have SerialDate (date/time)
and SerialNumber (string).
see this demo.

Both of SerialDate and SerialNumber are in my database.
But, the last three number of SerialNumber should start from 001 every month.
How can I edit it?

 

[arnelgp]

test this again.

 

[yunhsuan]

test this again.

Thank you! Arnelgp, you are my hero!!