Invoice numbers with text
in [Help]
|
Prev: Windows installer errors running Access 2003 MDE
Next: Access 2007 - Use Report or HTML file as body of Outlook email message
From: gorsoft on 9 Nov 2009 19:53 I am trying to construct invoice numbers in the series D0000001/ D0000002 etc etc. I ma importing records to a temporary table and then poulating the invoice no field (among other things). The code I am using is: Set db = CurrentDb() Set rs = db.OpenRecordset("tblInvoiceHeadersTemp") strNextInvNum = DMax("fldInvoiceNo", "tblInvoiceHeaders") Do While Not rs.EOF rs.Edit rs!fldInvoiceNo = strNextInvNum + 1 rs!fldInvoiceNo = FORMAT(strNextInvNum, "000000") rs!fldInvoiceNo = "D" & strNextInvNum rs.Update strNextInvNum = strNextInvNum + 1 rs.MoveNext Loop rs.Close Set rs = Nothing Set db = Nothing This doesn't work.. Should the invoice no be a number or text field? In building the table of invoice headers, how do I get round the fact that for the first pass there is no starting invoice number, so Dmax produces an "Invalid use of null" error? Gordon
From: Rich P on 9 Nov 2009 22:12 note that D0000001 is an alphanumeric number - not a numeric number. It appears that you are trying to use numeric functions on an alpha (text) numeric expressions. That won't work. You need to use string functions. I am interpreting your invoice number(s) to be a constant length of 8 characters. You can enumerate this expression by incrementing the nonzero number (1, 2, 3...) and append that to a 7 char string of D0000000. When the numeric portion exceeds 9 you can append 10 (11, 12, ...) to a 6 char string of "D000000" etc. When your invoice number exceeds 1123 you would append "D000" & 1123 for an invoice of "D0001123" You can extract the numeric portion of your invoice expression in a loop like this (make it a user defined function) Function fcnInvoice() As String Dim i As Integer, j as integer, v1 as variant, v2 as variant, iNum As Integer, str1 as string '--Invoice in this sample will be "D0000123" For i = 7 to 0 Step -1 v = Mid(Invoice, i, 1) If IsNumeric(v) And v > 0 then v2 = v1 & v2 j = j + 1 else exit for end if next j = 7 - j iNum = cInt(v2) iNum = iNum + 1 for i = 0 to j str1 = str1 & "0" Next Invoice = "D" & str1 & iNum fcnInvoice = Invoice End Function Rich *** Sent via Developersdex http://www.developersdex.com ***
From: gorsoft on 10 Nov 2009 02:51 On Nov 10, 3:12 am, Rich P <rpng...(a)aol.com> wrote: > note that D0000001 is an alphanumeric number - not a numeric number. It > appears that you are trying to use numeric functions on an alpha (text) > numeric expressions. That won't work. You need to use string > functions. I am interpreting your invoice number(s) to be a constant > length of 8 characters. You can enumerate this expression by > incrementing the nonzero number (1, 2, 3...) and append that to a 7 char > string of D0000000. When the numeric portion exceeds 9 you can append > 10 (11, 12, ...) to a 6 char string of "D000000" etc. When your invoice > number exceeds 1123 you would append "D000" & 1123 for an invoice of > "D0001123" > > You can extract the numeric portion of your invoice expression in a loop > like this (make it a user defined function) > > Function fcnInvoice() As String > Dim i As Integer, j as integer, v1 as variant, v2 as variant, iNum As > Integer, str1 as string > '--Invoice in this sample will be "D0000123" > For i = 7 to 0 Step -1 > v = Mid(Invoice, i, 1) > If IsNumeric(v) And v > 0 then > v2 = v1 & v2 > j = j + 1 > else > exit for > end if > next > j = 7 - j > iNum = cInt(v2) > iNum = iNum + 1 > for i = 0 to j > str1 = str1 & "0" > Next > Invoice = "D" & str1 & iNum > fcnInvoice = Invoice > End Function > > Rich > > *** Sent via Developersdexhttp://www.developersdex.com*** Hi Rich Thanks for your response. I'm not sure how would use your function in the process that I'm using. For example, I have table of invoice records with a field for Invoice No which is currently blank, I want to uodate that field to D0000001, D0000002, D0000003 etc. When that first update is done I will import those records into another table and empty the table. For the next batch of "invoices", I again will have a table of blank records and this time I will want to populate the invoice no field starting with the last number used (say D0000123) + 1 ie D0000124. And so on. Gordon
From: Roger on 10 Nov 2009 06:08 On Nov 9, 5:53 pm, "gors...(a)hotmail.com" <gors...(a)hotmail.com> wrote: > I am trying to construct invoice numbers in the series D0000001/ > D0000002 etc etc. > > I ma importing records to a temporary table and then poulating the > invoice no field (among other things). The code I am using is: > Set db = CurrentDb() > Set rs = db.OpenRecordset("tblInvoiceHeadersTemp") > > strNextInvNum = DMax("fldInvoiceNo", "tblInvoiceHeaders") > Do While Not rs.EOF > rs.Edit > rs!fldInvoiceNo = strNextInvNum + 1 > rs!fldInvoiceNo = FORMAT(strNextInvNum, "000000") > rs!fldInvoiceNo = "D" & strNextInvNum > rs.Update > strNextInvNum = strNextInvNum + 1 > rs.MoveNext > Loop > rs.Close > Set rs = Nothing > Set db = Nothing > > This doesn't work.. Should the invoice no be a number or text field? > In building the table of invoice headers, how do I get round the fact > that for the first pass there is no starting invoice number, so Dmax > produces an "Invalid use of null" error? > > Gordon try using NZ() Set db = CurrentDb() Set rs = db.OpenRecordset("tblInvoiceHeadersTemp") strNextInvNum = nz(DMax("fldInvoiceNo", "tblInvoiceHeaders") ,0) + 1 Do While Not rs.EOF rs.Edit rs!fldInvoiceNo = FORMAT(strNextInvNum, "000000") rs!fldInvoiceNo = "D" & strNextInvNum rs.Update strNextInvNum = strNextInvNum + 1 rs.MoveNext Loop rs.Close Set rs = Nothing Set db = Nothing
From: Salad on 10 Nov 2009 11:58
gorsoft(a)hotmail.com wrote: > On Nov 10, 3:12 am, Rich P <rpng...(a)aol.com> wrote: > >>note that D0000001 is an alphanumeric number - not a numeric number. It >>appears that you are trying to use numeric functions on an alpha (text) >>numeric expressions. That won't work. You need to use string >>functions. I am interpreting your invoice number(s) to be a constant >>length of 8 characters. You can enumerate this expression by >>incrementing the nonzero number (1, 2, 3...) and append that to a 7 char >>string of D0000000. When the numeric portion exceeds 9 you can append >>10 (11, 12, ...) to a 6 char string of "D000000" etc. When your invoice >>number exceeds 1123 you would append "D000" & 1123 for an invoice of >>"D0001123" >> >>You can extract the numeric portion of your invoice expression in a loop >>like this (make it a user defined function) >> >>Function fcnInvoice() As String >>Dim i As Integer, j as integer, v1 as variant, v2 as variant, iNum As >>Integer, str1 as string >>'--Invoice in this sample will be "D0000123" >>For i = 7 to 0 Step -1 >> v = Mid(Invoice, i, 1) >> If IsNumeric(v) And v > 0 then >> v2 = v1 & v2 >> j = j + 1 >> else >> exit for >> end if >>next >>j = 7 - j >>iNum = cInt(v2) >>iNum = iNum + 1 >>for i = 0 to j >> str1 = str1 & "0" >>Next >>Invoice = "D" & str1 & iNum >>fcnInvoice = Invoice >>End Function >> >>Rich >> >>*** Sent via Developersdexhttp://www.developersdex.com*** > > > Hi Rich > Thanks for your response. I'm not sure how would use your function > in the process that I'm using. For example, I have table of invoice > records with a field for Invoice No which is currently blank, I want > to uodate that field to D0000001, D0000002, D0000003 etc. When that > first update is done I will import those records into another table > and empty the table. For the next batch of "invoices", I again will > have a table of blank records and this time I will want to populate > the invoice no field starting with the last number used (say D0000123) > + 1 ie D0000124. And so on. > > Gordon If the first char of an invoice number is "D", you could get just the number. Ex: Mid(InvNum,2). This extracts everything from the 2nd char onward. To convert that to a number, perhaps Clng(). Ex: X = Clng(Mid(InvNum,2)). Now you can add 1 to the number X. |