Serial No with date (1 Viewer)

naobao

Registered User.
Local time
Today, 03:04
Joined
Feb 13, 2014
Messages
76
Hi,


How to auto add a Serial No with date in a form field, like the following sample format...
N201911190001
N201911190002
N201911190003
N201911190004
N201911200001
N201911200002
N201911200003
N201911210001
N201911210002
the last four digital can reset everyday


Thank !!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:04
Joined
Oct 29, 2018
Messages
21,358
Hi. The usual approach is to keep the components of the serial no separate. For example, there should be at least a date field and a numeric field. You can compare the value in the date field to the current date, and you can also calculate the last number in sequence from the number field.
 

naobao

Registered User.
Local time
Today, 03:04
Joined
Feb 13, 2014
Messages
76
Can you give me some sample code?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:04
Joined
Oct 29, 2018
Messages
21,358
Can you give me some sample code?
Sure. Let's say you have a separate date field and a numeric field for the sequence number. To get the next number in sequence, you could try something like:
Code:
Nz(DMax("NumberFieldName","TableName","Format(DateFieldName,'yyyymm')=Format(Date(),'yyyymm')"),0)+1
 

naobao

Registered User.
Local time
Today, 03:04
Joined
Feb 13, 2014
Messages
76

Attachments

  • 1.png
    1.png
    12.5 KB · Views: 79
  • 2.png
    2.png
    9.7 KB · Views: 79

isladogs

MVP / VIP
Local time
Today, 10:04
Joined
Jan 14, 2017
Messages
18,186
Your SerialNo field is text. Change it to Number
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:04
Joined
Oct 29, 2018
Messages
21,358
Hi,


I use your code in a query, but it seem not work...
Hi. For what you're showing us, try the following expression instead:

Code:
test: Nz(DMax("serialno","TblData","dd=#" & Format([dd],"yyyy-mm-dd") & "#"),0)+1
 

naobao

Registered User.
Local time
Today, 03:04
Joined
Feb 13, 2014
Messages
76
Hi. For what you're showing us, try the following expression instead:

Code:
test: Nz(DMax("serialno","TblData","dd=#" & Format([dd],"yyyy-mm-dd") & "#"),0)+1
Left hand side is the query result,
but I need right hand side result...
 

Attachments

  • 111.png
    111.png
    15.9 KB · Views: 72

Users who are viewing this thread

Top Bottom