Results 1 to 9 of 9

Thread: Invoice Numbers

  1. #1
    Join Date
    Oct 2004
    Posts
    29

    Unanswered: Invoice Numbers

    I posted this before but I think I made it sound to complicated.

    I have a form that has a key field which is an auto number. (I'm not concerned what this number is)

    I then have another field which is the invoice number.
    I cant add 1 to each invoice number each time one is entered.
    Each day the number jumps a 200 series.
    There is a starting number on another table for each calendar day to start the series.
    The part I dont know how to do is write the code to look up the highest number already used for that day and add +1.

    So when a date is enterd the after update checks the invoice number table for the right starting number. Then it needs to look up the highest invoice already entered and add +1 to it.

    Make sense or still sound confusing?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You could use a DLookUp or run a query or make a query to do this ... Take your pick ... I have no idea of which table you mean to perform the lookup on (or what it contains - data wise) ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Oct 2004
    Posts
    29
    Table 1 has the invoice info
    Table 2 has dates for the next 20 years with a "starting" number for the day.

    The user enters a date in form 1 the (AfterUpdate) in the date field runs a DLookup to find the starting number for that day.

    Now I need it to look up the highest number being used for the day and add +1. This is the part I'm not sure how to do.

    There are 5 invoices entered for 11/23/04 Table2 says the start number is 14800, So the invoice numbers on table 1 will be 14801 - 05
    The user goes to enter another invoice for 11/23/04 I need it to make the new invoice 14806.
    But if the user enters an order for 11/24/04 and the starting number is 15000.
    The first order for that day would be 15001 not 14807.

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would create a query that selects all the records with an Invoice Date = Date() then have it return the Max of the Invoice ID.

  5. #5
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146
    Quote Originally Posted by DCKunkle
    I would create a query that selects all the records with an Invoice Date = Date() then have it return the Max of the Invoice ID.
    This presents a problem doesn't it? What happens when the date of the last invoice was yesterday? I'd think that you need an initial invoice for today for the query to work. If not, it would simply return NULL because there were no invoices today, wouldn't it?

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Good point smacdonaldnc. I personally would manage something like this in code. I would probably create a table that stored the last Invoice Number. Then I would write a function to look at that table read the last number used, increment it and pass it back to whoever needed a number.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Thinking out of the box, what are you trying to achieve?. From your earlier postings I <think> you are attempting to use an order number in a specific series so that you can easily manually file the order according to day. You have a limit of 200 orders per day / week. Is this 200 per day fixed in concrete or is some smartaleck going to come along later and allow say 400 invocies per day / week.

    An alternative - keep the exisiting autonumber field (as your primary key and unique reference), and mask with superfluous characters, with some code that makes sense to you. The code could be generated from the date of the invoice.

    I have succesfully used in the past something similar to
    YWWRRIIIIII
    where
    Y is a single digit code (A to Z) indicating the year eg A=2004,B=2003 etc.
    WW is a 2 digit week code (01 - week 1 etc...)
    RR is a 2 digit random number
    IIIIII was a 6 digit sequential ordernumber.

    The customer was a small manufacturer who didn't want to let his competition / customers easily understand how many / few orders were being placed. The back office was entirely manual and wanted to quickly track down an invoice filed in date order (the files were by year by month)

    It sounds complex and long winded but it worked really well, the info that mattered for invoicing was the last 6 digits. The manual filing could be easily done using the YWW code - you knew automatically which physical file / section to look in. The RR was to confuse things (you could use as many or as few digits as required.

    As a matter of fact it has now all been replaced as the company is bigger and uses a networked accounting system whuch handles all that crud. Or possibly the owner is a lot less concerned about customers seeing how many invoices they are generating these days. We did go through an interim stage of printing invoices to Acrobat Writer / Distiller which gave the full facsimilie of the invoice on the desktop, but thats another story.....

  8. #8
    Join Date
    Oct 2004
    Posts
    29
    Healdem:
    The main reason for skipping a 200 series each day is to help mask the amount of business the company is doing. The mask dosent have to be complicated. Just enough to make the sequence not trackable at a glance. If someone wants to knw bad enough they can figure it out but I dont figure anyone will take the time to do it.

    We usually only have about 10 to 15 invoices a day. Thats not to say it wont get bigger. The number 200 came up years ago before I started working with a database. I've come a long way with access and have made things a lot more automated and user friendly in the company. However this is one of the last things I have yet to solve.
    There is really no reason I have to stick with the 200 series jump each day. I just cant have it count 1 - 10 and have it easily trackable. I have vendors that send me bills and I can track their invoices and know what they are doing, do to they fact that they count 1 - 10.
    But anyway right now the DLookup pulls up the first number for the day on each invoice entered. We then change the last 2 numbers to make the invoice we need (as to not make a duplicate).

    Is there a way to write code that looks up invoices matching the date entered then look for the highest number used on that day and add 1 to the number?
    If not the code you talked about using random and sequential numbers together to create an invoice would work. I would just need a little help exploring that option if you wouldnt mind.
    Something like A=year DDD=day in the year then a 6 digit sequential number?
    Last edited by chrisn6104; 11-24-04 at 12:49.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    chris
    try the attached its an A2002 version but it'll give you a flavour of what you can do. Can't do the weeknumbers (I forgot its part of an accounting calendar table)
    haven't added the random number stuff - easy enough to do
    -open the form put in a number upto 6 digits
    press the top button
    press the second button to decode
    adjust the 2nd text box to decode other numbers

    its pretty crude, but you may want to adjust the number of characters in the invoice number to say 3 rather than the 6. You could also adjust the day to be an offset as the month is offset by 3
    HTH
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •