Using current date to create a part number. need code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a database that keeps track of part numbers. I have had help with
this database before, and so it will automatically create a part number
incrementing by one.

Now I am going to create a new table, for a different kind of part and would
like to find a way to include the current date in the number and still
increment by one. Could be an added dash number, then the basenumber could be
the current date, maybe?
Does anyone have a code to be able to do this?
Here is the code I am using without the date included.
Dim NextNumber As Integer
NextNumber = Nz(DMax("[Extension]", "ECR LOG", "[BaseNumber]='" &
Me.BASENUMBER & "'"), 0) + 1
Me.EXTENSION = NextNumber
 
Now I am going to create a new table, for a different kind of part and would
like to find a way to include the current date in the number and still
increment by one. Could be an added dash number, then the basenumber could be
the current date, maybe?
Click to expand...

That's a BAD IDEA.

Storing two disparate pieces of data in one field is incorrect design.
Fields should be "atomic" - have only one, undivided value.

If you wish to store both a sequential number and a date, use two
fields - the date/time field can default to Date() to automatically
add the date as of when the record was created. The two fields can be
concatenated for display purposes.

John W. Vinson[MVP]
 
Maybe I didnt explain very well. I just want to use the date to create a new
number.

I tried the following but I am getting a "Compile error: Method or data
member not found."

Private Sub btnASSIGNECR_Click()
Dim NextNumber As Integer
NextNumber = Nz(DMax("[ECRNumber]", "ECR LOG2", "[RequestDate]='" &
Me.RequestDate & "'"), 0) + 1
Me.ECRNumber = NextNumber
End Sub

Later and on other forms and reports I will have to tie the whole number to
reports and forms and such.
 
nevermind that the code below did work!! whoo hoo.

Any idea how I can make make the date show like 1005 instead of 10/19/05?

Thanks for your help!!

Tammy said:
Maybe I didnt explain very well. I just want to use the date to create a new
number.

I tried the following but I am getting a "Compile error: Method or data
member not found."

Private Sub btnASSIGNECR_Click()
Dim NextNumber As Integer
NextNumber = Nz(DMax("[ECRNumber]", "ECR LOG2", "[RequestDate]='" &
Me.RequestDate & "'"), 0) + 1
Me.ECRNumber = NextNumber
End Sub

Later and on other forms and reports I will have to tie the whole number to
reports and forms and such.

John Vinson said:
That's a BAD IDEA.

Storing two disparate pieces of data in one field is incorrect design.
Fields should be "atomic" - have only one, undivided value.

If you wish to store both a sequential number and a date, use two
fields - the date/time field can default to Date() to automatically
add the date as of when the record was created. The two fields can be
concatenated for display purposes.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Any idea how I can make make the date show like 1005 instead of 10/19/05?
Click to expand...

Set the Format property of the textbox in which you're displaying it
to

"mmyy"


John W. Vinson[MVP]
 
Back
Top