use date as Invoice Number

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

Guest

I hope I can ask this correctly,
In my database for each order I use todays date and then the ordernumber for
that day.
example
051106-1
051106-2
051106-3 and so on I would like for this to be automatic instead of having
to type it in everytime. If this isn't possible could I have the date
followed by the orderIDnumber and if so How would I code this for the default
value. I currently have the following for the default value but it isn't
working.
=Date() & "-" & +[OrderID]
Thanks in Advance
 
lisaalvin41 said:
I hope I can ask this correctly,
In my database for each order I use todays date and then the ordernumber for
that day.
example
051106-1
051106-2
051106-3 and so on I would like for this to be automatic instead of having
to type it in everytime. If this isn't possible could I have the date
followed by the orderIDnumber and if so How would I code this for the default
value. I currently have the following for the default value but it isn't
working.
=Date() & "-" & +[OrderID]
Click to expand...


The order table needs two fields, A DateTime field for the
order date and a second field for the day's number. Don't
try to cram both pieces of information into one field. You
can format the two field into a single string whenever you
want to display it on a user form or report by using an text
box with the expression:
=Format(OrderDate, "mmddyy") & "-" & DayNum

The unique index on the Orders table would be a compound
index of both fields.

Use code in the form's BeforeUpdate event procedure to set
the OrderDate and DayNum fields:

If Me.NewRecord Then
Me.OrderDate = Date
Me.DayNum = Nz(DMax("DayNum", "Orders", _
"OrderDate=" & Format(Date, "\#m\/d\/yyyy\#"),0) + 1
End If
 
I think if I used the orderID instead of Daynum it may just about garantee no
duplicate Invoice#'s because OrderID is specific for each order shouldn't it?
so I changed the code a little. Here is what I have
=Format([OrderDate],"mmddyy") & "-" & [OrderID]
I also tried
=Format(OrderDate, "mmddyy") & "-" & DayNum
the results for Both of them is as follows

051206-

it shows nothing after the "-" symbol
Thanks for the quick reply

Marshall Barton said:
lisaalvin41 said:
I hope I can ask this correctly,
In my database for each order I use todays date and then the ordernumber for
that day.
example
051106-1
051106-2
051106-3 and so on I would like for this to be automatic instead of having
to type it in everytime. If this isn't possible could I have the date
followed by the orderIDnumber and if so How would I code this for the default
value. I currently have the following for the default value but it isn't
working.
=Date() & "-" & +[OrderID]
Click to expand...


The order table needs two fields, A DateTime field for the
order date and a second field for the day's number. Don't
try to cram both pieces of information into one field. You
can format the two field into a single string whenever you
want to display it on a user form or report by using an text
box with the expression:
=Format(OrderDate, "mmddyy") & "-" & DayNum

The unique index on the Orders table would be a compound
index of both fields.

Use code in the form's BeforeUpdate event procedure to set
the OrderDate and DayNum fields:

If Me.NewRecord Then
Me.OrderDate = Date
Me.DayNum = Nz(DMax("DayNum", "Orders", _
"OrderDate=" & Format(Date, "\#m\/d\/yyyy\#"),0) + 1
End If
Click to expand...
 
I have no idea what the OrderID field is so I can not
comment on that. If it's an AutoNumber field, then it is
inappropriate for this purpose.

Did you create the DayNum field in the table?

Did you add the suggested code in the form's BeforeUpdate
event procedure? Without code to set the value in the
table, none of this will do anything useful.
--
Marsh
MVP [MS Access]

I think if I used the orderID instead of Daynum it may just about garantee no
duplicate Invoice#'s because OrderID is specific for each order shouldn't it?
so I changed the code a little. Here is what I have
=Format([OrderDate],"mmddyy") & "-" & [OrderID]
I also tried
=Format(OrderDate, "mmddyy") & "-" & DayNum
the results for Both of them is as follows

051206-

it shows nothing after the "-" symbol
Thanks for the quick reply

Marshall Barton said:
lisaalvin41 said:
I hope I can ask this correctly,
In my database for each order I use todays date and then the ordernumber for
that day.
example
051106-1
051106-2
051106-3 and so on I would like for this to be automatic instead of having
to type it in everytime. If this isn't possible could I have the date
followed by the orderIDnumber and if so How would I code this for the default
value. I currently have the following for the default value but it isn't
working.
=Date() & "-" & +[OrderID]
Click to expand...


The order table needs two fields, A DateTime field for the
order date and a second field for the day's number. Don't
try to cram both pieces of information into one field. You
can format the two field into a single string whenever you
want to display it on a user form or report by using an text
box with the expression:
=Format(OrderDate, "mmddyy") & "-" & DayNum

The unique index on the Orders table would be a compound
index of both fields.

Use code in the form's BeforeUpdate event procedure to set
the OrderDate and DayNum fields:

If Me.NewRecord Then
Me.OrderDate = Date
Me.DayNum = Nz(DMax("DayNum", "Orders", _
"OrderDate=" & Format(Date, "\#m\/d\/yyyy\#"),0) + 1
End If
Click to expand...
Click to expand...
 
Another reason for keeping the two fields separate is that
IF you are trying to sort the invoices in date sequence and there are
multiple years if the invoice # is stored as the string then years will
be intermingled. If you have them as separate fields, then they will be
in date sequence

string method yields
010506-001
010506-002
010507-001
020606-001
020607-001

separate method yields
010506-001
010506-002
020606-001
010507-001
020607-001
 
Back
Top