If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Append Invoice
Hi Guys,
I have an got an invoice application and want to be able to part dispatch items and append a letter to each invoice , so for each dispatch I can append a letter to the end of the invoice. The situation is this: An order invoice is raised for 6 items. The order is to be completed in 3 shipments, so the first invoice would go out with 1001A Next items ship invoice would be 1001B Final shipment is done invoice is 1001C Naturally would want to reuse the append characters I have tried this solution suggested by Karl Dewey, which appends a number on the the string, but does the same for the next dispatched invoice: Left([ship invoice], 4) & Chr(Asc(Right([ship invoice], 1))+1) I needs a way to know what the last appended character/number and add the next in sequence. (The invoice number is auto number) Setup is SQL 2005 BE with Access 2007 FE Many thanks Lez |
#2
|
|||
|
|||
Append Invoice
Hi Lez,
Let me see if I understand this correctly. You have invoices with a particular invoice number (1001) and there are multiple shippings, which are given a separate "ship invoice" number? What exactly is your invoice / ship invoice table structure? One table? Two tables? I am thinking it should be two. tblInvoices InvoiceID etc. tblShipInvoices InvoiceID (linked to tblInvoices.InvoiceID) ShipID (text field) Then you can use something like this to determine the next ShipID if the InvoiceID is numeric: Nz(DMax("ShipID", "tblShipInvoices", "InvoiceID = " & [txtInvoiceID]), "A") Or if the InvoiceID is text: Nz(DMax("ShipID", "tblShipInvoices", "InvoiceID = """ & [txtInvoiceID] & """"), "A") Or if the InvoiceID is text and might contain the quote symbol: Nz(DMax("ShipID", "tblShipInvoices", "InvoiceID = """ & Replace([txtInvoiceID], """", """""") & """"), "A") If that does not help, post back with the pertinant information such as where this is being done (on a form, in a query) and the table(s) and field(s) involved. Clifford Bass "Lez" wrote: Hi Guys, I have an got an invoice application and want to be able to part dispatch items and append a letter to each invoice , so for each dispatch I can append a letter to the end of the invoice. The situation is this: An order invoice is raised for 6 items. The order is to be completed in 3 shipments, so the first invoice would go out with 1001A Next items ship invoice would be 1001B Final shipment is done invoice is 1001C Naturally would want to reuse the append characters I have tried this solution suggested by Karl Dewey, which appends a number on the the string, but does the same for the next dispatched invoice: Left([ship invoice], 4) & Chr(Asc(Right([ship invoice], 1))+1) I needs a way to know what the last appended character/number and add the next in sequence. (The invoice number is auto number) Setup is SQL 2005 BE with Access 2007 FE Many thanks Lez |
#3
|
|||
|
|||
Append Invoice
Hi Clifford,
Many thanks and sorry about the cross post, not sure where it should have been placed. Anyway, to advise on the structure I have a tblInvoices (MainForm) invoiceID is autonumber for invoice number (PK) I have tblinvoiceLineItems: (subform) with FK invoiceID, this table field fldDespatchStatus (true/false) The invoice number is simply being added within a query applied to produce a printed invoice/dispatch note. Ideally, I would like to add the appended letter to be saved as part of the line items of the invoice to identify which items where shipped with what appended letter should any issues arise at a later date. i.e Invoice 1001-A Product 1 Product 2 Product 3 Invoice 1001-B Product 4 Product 5 Product 6 I looked at your suggestion, and sort of get it, but not su A: How can I reuse the letters for different invoices? B: what value would I save it table ship? Hopefully this give you a bit more info on what I am looking for. Regards Lez "Clifford Bass" wrote in message ... Hi Lez, Let me see if I understand this correctly. You have invoices with a particular invoice number (1001) and there are multiple shippings, which are given a separate "ship invoice" number? What exactly is your invoice / ship invoice table structure? One table? Two tables? I am thinking it should be two. tblInvoices InvoiceID etc. tblShipInvoices InvoiceID (linked to tblInvoices.InvoiceID) ShipID (text field) Then you can use something like this to determine the next ShipID if the InvoiceID is numeric: Nz(DMax("ShipID", "tblShipInvoices", "InvoiceID = " & [txtInvoiceID]), "A") Or if the InvoiceID is text: Nz(DMax("ShipID", "tblShipInvoices", "InvoiceID = """ & [txtInvoiceID] & """"), "A") Or if the InvoiceID is text and might contain the quote symbol: Nz(DMax("ShipID", "tblShipInvoices", "InvoiceID = """ & Replace([txtInvoiceID], """", """""") & """"), "A") If that does not help, post back with the pertinant information such as where this is being done (on a form, in a query) and the table(s) and field(s) involved. Clifford Bass "Lez" wrote: Hi Guys, I have an got an invoice application and want to be able to part dispatch items and append a letter to each invoice , so for each dispatch I can append a letter to the end of the invoice. The situation is this: An order invoice is raised for 6 items. The order is to be completed in 3 shipments, so the first invoice would go out with 1001A Next items ship invoice would be 1001B Final shipment is done invoice is 1001C Naturally would want to reuse the append characters I have tried this solution suggested by Karl Dewey, which appends a number on the the string, but does the same for the next dispatched invoice: Left([ship invoice], 4) & Chr(Asc(Right([ship invoice], 1))+1) I needs a way to know what the last appended character/number and add the next in sequence. (The invoice number is auto number) Setup is SQL 2005 BE with Access 2007 FE Many thanks Lez |
#4
|
|||
|
|||
Append Invoice
Hi Lez,
Okay, that can work. So, going from what you currently have, you need to add a shipment ID column to the tblInvoiceLineItems table, in which you will store for each item an A or B or whatnot. One way to deal with that, including the assignment of the next shipment ID may be to add a third table that holds the main shipment data for each invoice. Things such as invoice ID, shipment ID, shipment date, notes, tracking number, and so on. Then it would be related to the tblInvoiceLineItems through the invoice ID and the shipment ID. tblinvoiceLineItems InvoiceID ItemID ShipmentID ... tblShipments (compound primary key of InvoiceID, ShipmentID) InvoiceID ShipmentID ShipmentDate Notes ShippingMethodCode TrackingNumber ..... This assumes that you ship all of a particular item and do not divide quantities. If you divide quantities (i.e. ordered 10, shipped 3, 7 back ordered) you will need to do something a bit different. You will need a new form (or subform) for creating shipments. When you go to save a new shipment, you can use your shipments form's Before Update event to determine the shipment ID to use: Private Sub Form_BeforeUpdate(Cancel As Integer) ' Assumes the invoice ID is in a text box named "txtInvoiceID". ' Assumes the shipment ID will go in a text box named "txtShipmentID". ' First shipment gets "A". Allows for values up through the end of the ' UNICODE range of characters. (More than you will ever need!) Dim lngShipmentID As Long Dim varShipmentID As Variant If Me.NewRecord Then varShipmentID = DMax("ShipmentID", "tblShipments", _ "InvoiceID = " & [txtInvoiceID].Value) If IsNull(varShipmentID) Then [txtShipmentID].Value = "A" Else lngShipmentID = AscW(varShipmentID) If lngShipmentID 65535 Then [txtShipmentID].Value = ChrW$(lngShipmentID + 1) Else ' No more possible UNICODE values MsgBox "No more shipments possible". Cancel = True End If End If End If End Sub Hope that helps, Clifford Bass "Lez" wrote: Hi Clifford, Many thanks and sorry about the cross post, not sure where it should have been placed. Anyway, to advise on the structure I have a tblInvoices (MainForm) invoiceID is autonumber for invoice number (PK) I have tblinvoiceLineItems: (subform) with FK invoiceID, this table field fldDespatchStatus (true/false) The invoice number is simply being added within a query applied to produce a printed invoice/dispatch note. Ideally, I would like to add the appended letter to be saved as part of the line items of the invoice to identify which items where shipped with what appended letter should any issues arise at a later date. i.e Invoice 1001-A Product 1 Product 2 Product 3 Invoice 1001-B Product 4 Product 5 Product 6 I looked at your suggestion, and sort of get it, but not su A: How can I reuse the letters for different invoices? B: what value would I save it table ship? Hopefully this give you a bit more info on what I am looking for. Regards Lez |
#5
|
|||
|
|||
Append Invoice
What I posted should not have appended a number on the the string but
increment the sufix letter. Left([ship invoice], 4) & Chr(Asc(Right([ship invoice], 1))+1) I just tested it and it works. -- Build a little, test a little. "Lez" wrote: Hi Guys, I have an got an invoice application and want to be able to part dispatch items and append a letter to each invoice , so for each dispatch I can append a letter to the end of the invoice. The situation is this: An order invoice is raised for 6 items. The order is to be completed in 3 shipments, so the first invoice would go out with 1001A Next items ship invoice would be 1001B Final shipment is done invoice is 1001C Naturally would want to reuse the append characters I have tried this solution suggested by Karl Dewey, which appends a number on the the string, but does the same for the next dispatched invoice: Left([ship invoice], 4) & Chr(Asc(Right([ship invoice], 1))+1) I needs a way to know what the last appended character/number and add the next in sequence. (The invoice number is auto number) Setup is SQL 2005 BE with Access 2007 FE Many thanks Lez |
#6
|
|||
|
|||
Append Invoice
Hi Clifford,
Sorry for not responding to this sooner, been sorting some other stuff out. To confirm the process I need to apply here is the ability to part ship an order and Append the invoice number with the shipping character i.e. Original order number = 1001 6 items ordered first shipment dispatches 3 items invoice number = 1001-A next shipment sends the next 3 items, invoice number = 1001-B So just wondering if this suggestion would work? Regards Lez "Clifford Bass" wrote in message ... Hi Lez, Okay, that can work. So, going from what you currently have, you need to add a shipment ID column to the tblInvoiceLineItems table, in which you will store for each item an A or B or whatnot. One way to deal with that, including the assignment of the next shipment ID may be to add a third table that holds the main shipment data for each invoice. Things such as invoice ID, shipment ID, shipment date, notes, tracking number, and so on. Then it would be related to the tblInvoiceLineItems through the invoice ID and the shipment ID. tblinvoiceLineItems InvoiceID ItemID ShipmentID ... tblShipments (compound primary key of InvoiceID, ShipmentID) InvoiceID ShipmentID ShipmentDate Notes ShippingMethodCode TrackingNumber ..... This assumes that you ship all of a particular item and do not divide quantities. If you divide quantities (i.e. ordered 10, shipped 3, 7 back ordered) you will need to do something a bit different. You will need a new form (or subform) for creating shipments. When you go to save a new shipment, you can use your shipments form's Before Update event to determine the shipment ID to use: Private Sub Form_BeforeUpdate(Cancel As Integer) ' Assumes the invoice ID is in a text box named "txtInvoiceID". ' Assumes the shipment ID will go in a text box named "txtShipmentID". ' First shipment gets "A". Allows for values up through the end of the ' UNICODE range of characters. (More than you will ever need!) Dim lngShipmentID As Long Dim varShipmentID As Variant If Me.NewRecord Then varShipmentID = DMax("ShipmentID", "tblShipments", _ "InvoiceID = " & [txtInvoiceID].Value) If IsNull(varShipmentID) Then [txtShipmentID].Value = "A" Else lngShipmentID = AscW(varShipmentID) If lngShipmentID 65535 Then [txtShipmentID].Value = ChrW$(lngShipmentID + 1) Else ' No more possible UNICODE values MsgBox "No more shipments possible". Cancel = True End If End If End If End Sub Hope that helps, Clifford Bass "Lez" wrote: Hi Clifford, Many thanks and sorry about the cross post, not sure where it should have been placed. Anyway, to advise on the structure I have a tblInvoices (MainForm) invoiceID is autonumber for invoice number (PK) I have tblinvoiceLineItems: (subform) with FK invoiceID, this table field fldDespatchStatus (true/false) The invoice number is simply being added within a query applied to produce a printed invoice/dispatch note. Ideally, I would like to add the appended letter to be saved as part of the line items of the invoice to identify which items where shipped with what appended letter should any issues arise at a later date. i.e Invoice 1001-A Product 1 Product 2 Product 3 Invoice 1001-B Product 4 Product 5 Product 6 I looked at your suggestion, and sort of get it, but not su A: How can I reuse the letters for different invoices? B: what value would I save it table ship? Hopefully this give you a bit more info on what I am looking for. Regards Lez |
#7
|
|||
|
|||
Append Invoice
Hi Lez,
It should. You may need to tweak it depending on your needs. Note that your A, B and whatnot are stored in a separate field. If you want to report the expanded "invoice numbers" as a single item, just concatenate the InvoiceID and ShipmentID fields (for example: InvoiceID & "-" & ShipmentID). How about you give it a try in a development copy of the database? Clifford Bass "Lez" wrote: Hi Clifford, Sorry for not responding to this sooner, been sorting some other stuff out. To confirm the process I need to apply here is the ability to part ship an order and Append the invoice number with the shipping character i.e. Original order number = 1001 6 items ordered first shipment dispatches 3 items invoice number = 1001-A next shipment sends the next 3 items, invoice number = 1001-B So just wondering if this suggestion would work? Regards Lez |
#8
|
|||
|
|||
Append Invoice
Ok Thanks Clifford,
I have used your code and it works, 'kind of' As all the items have already been added to an invoice. this does not work for me, what I am thinking is however, as I have a select box to choose the items I am to ship, this would be the ideal option to append the shippingID to the invoice. So when I select the item I would want to the A, B etc at that point. My thoughts are that if I change that after update of the check box it will add the letter, but I would assume at this point, without trying, each row would be appended a letter. I will try and advise. Any thoughts appreciated. Regards Lez "Clifford Bass" wrote in message ... Hi Lez, It should. You may need to tweak it depending on your needs. Note that your A, B and whatnot are stored in a separate field. If you want to report the expanded "invoice numbers" as a single item, just concatenate the InvoiceID and ShipmentID fields (for example: InvoiceID & "-" & ShipmentID). How about you give it a try in a development copy of the database? Clifford Bass "Lez" wrote: Hi Clifford, Sorry for not responding to this sooner, been sorting some other stuff out. To confirm the process I need to apply here is the ability to part ship an order and Append the invoice number with the shipping character i.e. Original order number = 1001 6 items ordered first shipment dispatches 3 items invoice number = 1001-A next shipment sends the next 3 items, invoice number = 1001-B So just wondering if this suggestion would work? Regards Lez |
#9
|
|||
|
|||
Append Invoice
Hi Clifford,
To advise, I have tried the update as suggested and modified the code as below: Private Sub ChkShip_AfterUpdate() ' Assumes the invoice ID is in a text box named "txtInvoiceID". ' Assumes the shipment ID will go in a text box named "txtShipmentID". ' First shipment gets "A". Allows for values up through the end of the ' UNICODE range of characters. (More than you will ever need!) Dim lngShipmentID As Long Dim varShipmentID As Variant If Me.ChkShip = True Then varShipmentID = DMax("ShipmentID", "tblInvoicelineitems", _ "invoiceID = " & [txtInvoiceID].Value) If IsNull(varShipmentID) Then [txtshipmentID].Value = "A" Else lngShipmentID = AscW(varShipmentID) If lngShipmentID 65535 Then [txtshipmentID].Value = ChrW$(lngShipmentID + 1) Else ' No more possible UNICODE values MsgBox "No more shipments possible" Cancel = True End If End If End If End Sub As expected, each row is now appended with a letter, so need to think how I can just add the first letter to regardless of the number of rows selected, then when we ship the balance of the order I can append the next number to the invoice. I am just wondering if I should just do this as part of the query for the items I am selecting, so we simply append the letter to the invoiceID, so just add a shipmentID to the tblInvoice? Regards Lez "Clifford Bass" wrote in message ... Hi Lez, It should. You may need to tweak it depending on your needs. Note that your A, B and whatnot are stored in a separate field. If you want to report the expanded "invoice numbers" as a single item, just concatenate the InvoiceID and ShipmentID fields (for example: InvoiceID & "-" & ShipmentID). How about you give it a try in a development copy of the database? Clifford Bass "Lez" wrote: Hi Clifford, Sorry for not responding to this sooner, been sorting some other stuff out. To confirm the process I need to apply here is the ability to part ship an order and Append the invoice number with the shipping character i.e. Original order number = 1001 6 items ordered first shipment dispatches 3 items invoice number = 1001-A next shipment sends the next 3 items, invoice number = 1001-B So just wondering if this suggestion would work? Regards Lez |
#10
|
|||
|
|||
Append Invoice
Hi Karl,
To advise, my invoiceID is an autonumber field (int) and so adds a number rather than a letter. I did try it using a text field and it works as you suggested. I might come back to this again in the future. Many thanks Lez "KARL DEWEY" wrote in message ... What I posted should not have appended a number on the the string but increment the sufix letter. Left([ship invoice], 4) & Chr(Asc(Right([ship invoice], 1))+1) I just tested it and it works. -- Build a little, test a little. "Lez" wrote: Hi Guys, I have an got an invoice application and want to be able to part dispatch items and append a letter to each invoice , so for each dispatch I can append a letter to the end of the invoice. The situation is this: An order invoice is raised for 6 items. The order is to be completed in 3 shipments, so the first invoice would go out with 1001A Next items ship invoice would be 1001B Final shipment is done invoice is 1001C Naturally would want to reuse the append characters I have tried this solution suggested by Karl Dewey, which appends a number on the the string, but does the same for the next dispatched invoice: Left([ship invoice], 4) & Chr(Asc(Right([ship invoice], 1))+1) I needs a way to know what the last appended character/number and add the next in sequence. (The invoice number is auto number) Setup is SQL 2005 BE with Access 2007 FE Many thanks Lez |
Thread Tools | |
Display Modes | |
|
|