How to add a Bill on earlier date and change all the further existing bill Numbers

[deepakg27]

Hi Guys,



I am working on billing database. I Have tblSale which has BillNumber (Set to No Duplicates) and BillDate. I have a code in form which restricts date from being older than last bill date. Now I have a situation where I would like to add a bill on earlier date, which I can do with a new bill number. But What I want to achieve is, I will try to explain in Example:

Suppose I have bill Number 1001 Dated 1st Sep 2017 TO Bill Number 1018 dated 31st Dec 2017. Bill Number 1010 is dated 15th Oct 2017. Now Supposedly I want to add another bill dated 15th Oct 2017, I can add it with bill Number 1019, But What I want to achieve is that after entering Bill date 15th Oct 2017 Bill should be given bill number 1011 and all the further bill numbers to be increased by 1.

Hope I have been able to explain my problem.

Awaiting your help and guidance.
Thanks and Regards
Deepak Gupta

[orange]

You have explained your issue, but why does the Bill Number have such importance to you. You know the Bill, the Customer and thee BillDate.
You should really gives readers the whole picture of your st up and requirement. It seems you are deep into physical Access and perhaps have not fully analyzed your requirements. If that is true, then more analysis and possible redesign may be part of the solution.

[deepakg27]

Dear Orange,

Bill Number has importance to me as it is a billing database and bill Number (OR Invoice Number) needs to be in order . For example Bill number Bill Number 1011 is 16th Oct 2017 and we cannot have bill number 1019 dated 15th Oct 2017. it is not right and it should not be done to maintain order for future checking and reference.

Awaiting for your help.
Thanks and Regards
Deepak Gupta

[SierraJuliet]

I know what you are talking about for bill numbering increment by one because I did that but with far more evaluation than four digits (i.e. 1010). To answer your original question.

UPDATE tblTableName
SET Field1 = Value1, Field2 = Value2, Field3 = Value3, etc
WHERE Field1 = CurrentValue1, Field2 = CurrentValue2, Field3 = CurrentValue3;

Add or subtract whatever fields and values you do not want to change.

For example just add your second 15th Oct 2017 so it has everything as all other bills have, including the 1019.

Then do the update starting with your most recent bill number (the second 15th Oct 2017) and set it to 1020. Then work in reverse with 1018 to 1019, 1017 to 1018, 1016 to 1017, and so forth since it opens up a place holder for the bill number itself. Then at the very end of the update add another entry for the second 15th Oct 2017 and set it to 1011.

Just be careful though because this can mess your database up if not done right. Note I have not done this in a while so you may need to do each one individually using the same order described above and shown below.

Example:

UPDATE tblSale
SET [BillNumber1] = 1020, [BillNumber2] = 1019, [BillNumber3] = 1018, etc
WHERE [BillNumber1] = 1019, [BillNumber2] = 1018, [BillNumber3] = 1017, etc;

The "SET" is what you want it to be.
The "WHERE" is what it currently is.
The "WHERE" runs first for the statement so it knows what it is looking at then it "SET" the new value based on order 1, 2, 3, etc for both "WHERE" and "SET".
The BillNumber1, 2, 3 is the same field that I am just showing as being for separate billing numbers, so they should all be like [BillNumber] = 1019, [BillNumber] = 1018, etc if the information you gave is correct.

[SierraJuliet]

Dear Orange,

Bill Number has importance to me as it is a billing database and bill Number (OR Invoice Number) needs to be in order . For example Bill number Bill Number 1011 is 16th Oct 2017 and we cannot have bill number 1019 dated 15th Oct 2017. it is not right and it should not be done to maintain order for future checking and reference.

Awaiting for your help.
Thanks and Regards
Deepak Gupta

In my line of work an invoice number (or bill number) is set once it is sent out. I cannot go back and change that because it is explicitly against the law where I am located. Moreover, what happens when a customer who has that bill number comes back and asks about it by referencing it?

[orange]

My concern was that if a Bill (or many) has/have been sent to a Customer(s) and in effect processed, How could you rationalize, going back to your files (database) and renumbering records???

Your ordering of records is based on Date or Date/Time.

[SierraJuliet]

I agree. It is illegal in many places and they should probably check there industry regulations first because if customer attempts to reference that bill and they are like no that is not right then they contact legal, which opens a can of worms.

Putting the legal concern aside I still would not do it. I would simply leave it as is. This brings to question what was that bill number originally if anything and does multiple customers have the same reference number or at this rate up to three for one number and now everyone else since then up until now will have at least two customers with the same bill number if the OP changes the billing numbers. This now magnifies the problem many fold versus just the one or possibly two depending on how that originally played out.

[orange]

Please tell us in business terms why the Bill numbers MUST be sequential.
Also, please tell us HOW (with details) you will renumber Bills that have already been sent to or processed by Customers.

I'm curious of what your processes or plan B might be.

[SierraJuliet]

This is why some businesses do pseudo random numbering, so it doesn't matter.

I will help them out. Plan B add a notes field to tblSale and put the original bill number on it so it may still be referenced to an already processed invoice.

[orange]

Many organizations would give you the next available Bill Number with details and the BillDate.
There was a time when checks etc were numbered (on paper) and you have to account for every number (including print problems/paper jams/ink loss or mess etc).

[deepakg27]

Hi Guys,

Thanks for your suggestions. I agree with every word of what you people have said and suggested. I just wanted to know the way of doing it. Thanks. Can we do this update query on the fly.

Thanks and Regards
Deepak Gupta

[SierraJuliet]

You can run the UPDATE statement whenever you need to, so long as you have a means of entering and executing SQL. You can add something like this to your form if it makes it easier and if the navigation and what not is locked down. See post #6 https://www.accessforums.net/showthread.php?t=70040

[deepakg27]

thanks for all the help provided. I will try the update query system and if any problems arise will come back to you for help.

Regards
Deepak Gupta

[SierraJuliet]

^ okay .