Typical Invoice Number

[Ashfaque]

Hi,

On my invoice header form, I am selecting new customer from a combo that brings customer name and customer code in diff text boxes on the form.

A field called SalesInvNum is field that generate invoice number in below manner.

Example if the Customer is Peter and his code is 102 then SalesInvNum will have

Pe-100509-102-2

Pe = first 2 characters of the customername field on form
100509 = is current date
102= is customer code that picked up from CustomerCode field on form
2= is the highest number of invoice of particular customer.

On after update of CboSelectCustomer, I have below code that generates the above invoice format.

Me.SalesInvNum = Mid(CustomerName, 1, 2) & "-" & Format(InvDate, "YYMMDD") & "-" & Me.CustomerCode & "-"

What I am looking for is like this, when the user creates new invoice and select new customer from CboSelectCustomer, the textbox SalesInvNum should bring last (max) invoice number of that customer on that day.

Let us say Peter (code 102) have total 4 invoices on the same date.

Pe-100509-102-1
Pe-100509-102-2
Pe-100509-102-3
Pe-100509-102-4

And when user click to add new invoice for the same customer in the same date it should add + 1 to last number of his invoice so it will be

Pe-100509-102-5

To see what is the last invoice for this customer in the same date for same customer, I placed one text box on form that will display last invoice for this customer .To do this I tried this way but no use.

Me.Text118 = DMax("SalesInvNum", "T_SalesInvHead", "CustomerName='" & CustomerName & "' And CustomerCode=" & CustomerCode)

Can anyone help me out.

Thanks,

 

[HiTechCoach]

Hi,

On my invoice header form, I am selecting new customer from a combo that brings customer name and customer code in diff text boxes on the form.

A field called SalesInvNum is field that generate invoice number in below manner.

Example if the Customer is Peter and his code is 102 then SalesInvNum will have

Pe-100509-102-2

Pe = first 2 characters of the customername field on form
100509 = is current date
102= is customer code that picked up from CustomerCode field on form
2= is the highest number of invoice of particular customer.

On after update of CboSelectCustomer, I have below code that generates the above invoice format.

Me.SalesInvNum = Mid(CustomerName, 1, 2) & "-" & Format(InvDate, "YYMMDD") & "-" & Me.CustomerCode & "-"

What I am looking for is like this, when the user creates new invoice and select new customer from CboSelectCustomer, the textbox SalesInvNum should bring last (max) invoice number of that customer on that day.

Let us say Peter (code 102) have total 4 invoices on the same date.

Pe-100509-102-1
Pe-100509-102-2
Pe-100509-102-3
Pe-100509-102-4

And when user click to add new invoice for the same customer in the same date it should add + 1 to last number of his invoice so it will be

Pe-100509-102-5

To see what is the last invoice for this customer in the same date for same customer, I placed one text box on form that will display last invoice for this customer .To do this I tried this way but no use.

Me.Text118 = DMax("SalesInvNum", "T_SalesInvHead", "CustomerName='" & CustomerName & "' And CustomerCode=" & CustomerCode)

Can anyone help me out.

Thanks,

Try:

Me.Text118 = DMax("SalesInvNum", "T_SalesInvHead", "CustomerCode=" & Me.CustomerCode)

 

[Ashfaque]

Thanks,

This will give me nothing. Bcz the condition SalesInvNum or Text118 should display last invoice number of that particular customer on that date.

I think below might work.

Dim db as DAO.database
Dim rst as DAO.Recordset
Dim LastInvNum As Text (I am not sure what type to use as

Set db=Currentdb.Openrecordset("Select Max(SalesInvNum) As LastInvNum From T_SalesInvHead Where CustomerName = Mid(CustomerName, 1, 2) And InvDate = Format(InvDate, "YYMMDD") And CustomerCode = " & Me.CustomoerCode "

SalesInvNum= rst!LastInvNum + 1
'I need to add next invoice number here for this customer if he has next invoice in the same date. If it is not the same date and customer is same, it should allocate Invoice Number Pe-100509-102-1 because it first invoice for that customer in that date.

rst.close
Set rst = Nothing

Should be something like.....Any idea?

Thanks,
With kind regards,
Ashfaque

 

[HiTechCoach]

Thanks,

This will give me nothing. Bcz the condition SalesInvNum or Text118 should display last invoice number of that particular customer on that date.

I think below might work.

Dim db as DAO.database
Dim rst as DAO.Recordset
Dim LastInvNum As Text (I am not sure what type to use as

Set db=Currentdb.Openrecordset("Select Max(SalesInvNum) As LastInvNum From T_SalesInvHead Where CustomerName = Mid(CustomerName, 1, 2) And InvDate = Format(InvDate, "YYMMDD") And CustomerCode = " & Me.CustomoerCode "

SalesInvNum= rst!LastInvNum + 1
'I need to add next invoice number here for this customer if he has next invoice in the same date. If it is not the same date and customer is same, it should allocate Invoice Number Pe-100509-102-1 because it first invoice for that customer in that date.

rst.close
Set rst = Nothing

Should be something like.....Any idea?

Thanks,
With kind regards,
Ashfaque

It has been a long time since I have seen this done. With the ability of current databases, etc, it is not as common today. I have to look back at code I wrote in 1995 to refresh my memory on how to do this.

Just to be clear, you should have these field in the Invoice header table:

InvoiceID - autonumber primary key

fields used to calculate the displayed Invoice number:

CustomerCode - foreign key to Customer Table
InvoiceDate - data type is date/time
Invoice Seq Number - long integer

You use the above data to calculate a invoice number "SalesInvNum" for display only. It should NOT be stored since it can be calculated from other fields in the records. To the user it will Appear to be stored.


To get the next Invoice Seq Number, I would use DMax() to get the last Seq Number used by CustomerCode and InvDate

Try something like:

Me.InvoiceSeqNumber = Nz(DMax("SalesInvSeqNum", "T_SalesInvHead", "CustomerCode=" & Me.CustomerCode & " and [InvDate] =#" & Me.InvDate & "#" ) , 0) + 1


Now you could use something like this to display the Calculated Invoice Number in an unbound text box.

Me.SalesInvNum = Left(Me.txtCustomerName, 2) & "-" & Format(InvDate, "YYMMDD") & "-" & Me.CustomerCode & "-" & Me.InvoiceSeqNumber

 

[Ashfaque]

Hi,

InvoiceSeqNumber is just displaying 1. Even there are more than 2-3 invoices for the same customer in same date, it displayes 1 only.

Any other thoughts?

 

[Galaxiom]

A relevant question would be why you would choose such a convoluted, pointless and denormalised invoice numbering structure.

Firstly it won't be guaranteed to be unique. Two customers with the same starting letters to their names who purchased on the same day could easily have the same invoice number.

It would be far easier and much, much faster to query the relevant date, time and customer fields to return the required invoice.

 

[HiTechCoach]

A relevant question would be why you would choose such a convoluted, pointless and denormalised invoice numbering structure.

Firstly it won't be guaranteed to be unique. Two customers with the same starting letters to their names who purchased on the same day could easily have the same invoice number.

It would be far easier and much, much faster to query the relevant date, time and customer fields to return the required invoice.

Galaxiom,

They are also including the Customer Code which should make it unique.

I do agree that the invoice numer seams like a lot of extra work for no real benefit. At least I can not think of any practical ones.

 

[Galaxiom]

They are also including the Customer Code which should make it unique.

Ahh, I didn't read it well enough.

Even if the client required this numbering system it would be quite inappropriate to retrieve invoices based on parsing the invoice number as is being attempted. Even more hideous is the possibility they might be anticipating joins based on the customer code obtained by extraction from the invoice number or sorting by a similarly derived date.

The complex invoice number is not only pointlless but would be likely inappropriate from the POV of a financial auditor. Auditors generally prefer to see an unbroken sequence of invoice numbers.

 

[HiTechCoach]

Ahh, I didn't read it well enough.

Even if the client required this numbering system it would be quite inappropriate to retrieve invoices based on parsing the invoice number as is being attempted. Even more hideous is the possibility they might be anticipating joins based on the customer code obtained by extraction from the invoice number or sorting by a similarly derived date.

The complex invoice number is not only pointlless but would be likely inappropriate from the POV of a financial auditor. Auditors generally prefer to see an unbroken sequence of invoice numbers.

I totally agree with you.

It may be a requirement for a project just to see if they can do it. Maybe a class assignment.

 

[Galaxiom]

It may be a requirement for a project just to see if they can do it. Maybe a class assignment.

You could be onto something there.

However as good as it might be as a challenging exercise I would find it disturbing for a teacher to base a question on profoundly denormalised structures like that.

Maybe it is a trick question.

 

[gemma-the-husky]

to do this you need a NZ in there

if its a new date/new customer the dlookup will return null, so you need something to deal with that

so this sort of thing, formatted correctly

lastinv = nz(dmax("invno","invtable","cust=whatever and date=whatever","")

 

[Ashfaque]

Thanks,

You are correct. It is something inappropriate but is requirement of my client. I anyhow generating the autonumber in my header table but client need in that way.

Ashfaque

 

[Galaxiom]

Just to clarify. I believe was misled by a very common misuse of terminology which I do wish posters would take more care about.

Fields are in tables and queries. The textboxes on forms are NOT fields but CONTROLS. The difference is VITALLY important because forms have both fields (by virtue of their RecordSet property) and controls. They are NOT the same thing.

The invoice number in this case should never be a field because it is derived from values in the fields of the current record of the form. Storing it in a field would be a mistake, a breach of normalisation.

I do believe the OP is using correct methodology but incorrect terminology.

 

[Ashfaque]

InNum in tbl T_SalesInvHead is an autonumber field that generates auto invoice number. The SalesInvNum is another text field in which I need to display the invoice number in the fashion where the date part is being taken from field InvDate in specific format;

Pe-100509-102-1
Pe-100509-102-2
Pe-100509-102-3
Pe-100509-102-4

I will save this SalesInvNum data while saving the complete invoice record.

The thing is after selecting new customer from combo CboSelectCustomer, it should check whether the selected customer has any invoice in the current date and if so, then add 1 to last part of SalesInvNum. If there is no invoice for this customer in the current date or date taken from field InvDate, it should consider 1st invoice and generate the SalesInvNum in above fashion.

I know that this is not good practice but client need this way and I am sure there must be some way to sort it out.

All thoughts are appreciated...

 

[Galaxiom]

There is no good really reason to store SalesInvNumber. It is derived from the CustCode (FK to Customer table), CustName (from Customer table via CustCode relationship), InvDate and InvTime.

So long as the client can see the SalesInvNum displayed on the forms and reports they should not care if it is stored in a table or not.

To find the sequence number use a count of the previous invoices on that day to that CustomerCode and add one.

One way, in the Control Source of the SalesInvNumber control:

= Left(tblCustomers.CustName,2) & "-" Format(tblInvoices.InvDate, "yymmdd") & "-" & tblInvoices.CustNum & "-" & (DCount("*", "tblInvoices",  "CustNum = txtCustomerNum AND InvDate = txtInvoiceDate AND InvTime < txtInvoiceTime") +1)

Or alternatively avoid the DCount by generating the sequence number in a subform linked to the Invoice form by CustomerCode and Date.

You will need a default value of the current time for new invoices. Update this as it saves to the time the invoice is posted.

This way two sales can be made by different terminals and they will be correctly numbered unless they are completed at the very same second.

Be sure to save the record before printing so that the correct sequence number will be used if another sale was generated elsewhere for the same customer while the invoice was being entered. This could be worked around by adding a second to one of them.

 

[Ashfaque]

Thanks Galaxiom,

I think time factor will make again create complications so it is better to avoid time indications.

I used your trick with below modification but it counts total records in the table and adds 1. E.g. if I have 5 records of diff customers in T_SalesInvHead tbl, it adds + 1 to the SalesInvNum field on form;

SalesInvNum = Left(CustomerName, 2) & "-" & Format(InvDate, "yymmdd") & "-" & CustomerCode & "-" & (DCount("SalesInvNum", "T_SalesInvHead", "CustomerCode = Me.CustomerCode") + 1)

At least things started to move... It is on the way to close.....

Extend help please....

 

[Galaxiom]

You will need to include the Date condition in the DCount to limit to the same days invoices.

However without the time factor the technique cannot work as a Control Source displaying for the derived SalesInvNum because invoices cannot be put in a sequence. All invoices for the customer and day would be counted, giving them all the same number.

Without the InvTime the expression could be used to allocate an invoice sequence number using VBA, counting only the already existing invoices before the current invoice is saved.

However overlapping sales to the same customer from different terminals could result in duplicate SalesInvNum. To avoid this you could save the invoice on creation and then update the values.

The use of the InvTime field and not storing SalesInvNum gets around the whole issue. Besides, storing the transaction time is good practice for invoicing systems.

 

[Ashfaque]

Hi,

You will need to include the Date condition in the DCount to limit to the same days invoices.

The problem is my date format is yymmdd without any slash in it and its a middle part of SalesInvNum. I do not know the way to use DCount for any mid part criteria for date.

Regards,

 

[Galaxiom]

You could derive a field for Date from the SalesInvNum field.
InvDate: Mid(SalesInvNum,4,6)

Since yymmdd has the Most Significant Digits descending in a Left to Right order the string can even be used with > and < operators but it is slower than working with a proper Date field.

However I cannot overstate that you are managing this whole problem entirely backwards by storing the CustomerCode and Date as part of the Invoice number. You would be much, much better off having separate fields for this information. Otherwise everything you relate to the table will require the SalesInvNum field to be parsed.

Finding all the invoices between two dates will mean parsing the SalesInvNum. Simply storing the date in its own field will be much simpler and faster.

Relating the invoice to the customer will require parsing the SalesInvNum if it isn't stored in its own field.

It isn't the right way to approach this. Store the InvDate, CustomerCode and InvTime in the Invoice table as separate fields. Derive the SalesInvNum on the fly. It need never be stored anywhere since it is easily calculated from the other information already stored in fields.

You are allowing the client's specification of SalesInvNum to define the data structure when it should be treated as a derivitave value.

 

[Ashfaque]

Still no luck.....

Thanks,