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 10 Nov 2009 13:10 On Nov 10, 4:58 pm, Salad <o...(a)vinegar.com> wrote: > gors...(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.- Hide quoted text - > > - Show quoted text - Thanks for the tips, guys but I'm not sure I have explained adequately what I am trying to do. I say again. I have temp table of invoice records with a field for Invoice No (text) which is currently blank, I want to update that field to D0000001, D0000002, D0000003 etc. When that first update is done I will import those records into the main invoice records table and empty the temp table. For the next batch of "invoices", I again will have a table of invoice records in the temp table 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. I am doing all this in code from a button on a form. The code I am using is: Set db = CurrentDb() Set rs = db.OpenRecordset("tblInvoiceHeadersTemp") strNextInvNum = Nz(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 Of course I get a type mismatch error because I am trying to add a number to a text field, but I don't know what to do next. Grateful for any help. Gordon
From: Salad on 10 Nov 2009 14:19 gorsoft(a)hotmail.com wrote: > On Nov 10, 4:58 pm, Salad <o...(a)vinegar.com> wrote: > >>gors...(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.- Hide quoted text - >> >>- Show quoted text - > > > Thanks for the tips, guys but I'm not sure I have explained adequately > what I am trying to do. I say again. I have temp table of invoice > records with a field for Invoice No (text) which is currently blank, > I want > to update that field to D0000001, D0000002, D0000003 etc. When that > first update is done I will import those records into the main invoice > records table and empty the temp table. > For the next batch of "invoices", I again will > have a table of invoice records in the temp table 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. I am doing all this in code from a button > on a form. > > The code I am using is: > Set db = CurrentDb() > Set rs = db.OpenRecordset("tblInvoiceHeadersTemp") > strNextInvNum = Nz(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 > > Of course I get a type mismatch error because I am trying to add a > number to a text field, but I don't know what to do next. > Grateful for any help. > > Gordon > > I don't understand your problem. I created a table, Table1, with field InvNum. There are 3 records with InvNum values of D1, D2, and D3. I run the following. Sub InvNum() Dim lngNum As Long Dim strNextNum As String 'in case there are no records, I use NZ to set initial val to 0. lngNum = Nz(DMax("Mid(InvNum,2)", "Table1"), 0) + 1 strNextNum = "D" & Format(lngNum, "000000") MsgBox strNextNum End Sub I get D000004. If there are no records in Table1, I get D000001. Do you need more code examples?
From: gorsoft on 11 Nov 2009 05:45 On Nov 10, 7:19 pm, Salad <o...(a)vinegar.com> wrote: > gors...(a)hotmail.com wrote: > > On Nov 10, 4:58 pm, Salad <o...(a)vinegar.com> wrote: > > >>gors...(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.- Hide quoted text - > > >>- Show quoted text - > > > Thanks for the tips, guys but I'm not sure I have explained adequately > > what I am trying to do. I say again. I have temp table of invoice > > records with a field for Invoice No (text) which is currently blank, > > I want > > to update that field to D0000001, D0000002, D0000003 etc. When that > > first update is done I will import those records into the main invoice > > records table and empty the temp table. > > For the next batch of "invoices", I again will > > have a table of invoice records in the temp table 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. I am doing all this in code from a button > > on a form. > > > The code I am using is: > > Set db = CurrentDb() > > Set rs = db.OpenRecordset("tblInvoiceHeadersTemp") > > strNextInvNum = Nz(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 > > > Of course I get a type mismatch error because I am trying to add a > > number to a text field, but I don't know what to do next. > > Grateful for any help. > > > Gordon > > I don't understand your problem. I created a table, Table1, with field > InvNum. There are 3 records with InvNum values of D1, D2, and D3. I > run the following. > Sub InvNum() > Dim lngNum As Long > Dim strNextNum As String > 'in case there are no records, I use NZ to set initial val to 0. > lngNum = Nz(DMax("Mid(InvNum,2)", "Table1"), 0) + 1 > strNextNum = "D" & Format(lngNum, "000000") > MsgBox strNextNum > End Sub > > I get D000004. If there are no records in Table1, I get D000001. Do > you need more code examples?- Hide quoted text - > > - Show quoted text - Hi Salad, Many thanks for that. Worked a treat. My problem was not understanding how to apply your orignal tip in the recordset code I was using behind the form. Your second example made that clearer to me. Code now reads: Set db = CurrentDb() Set rs = db.OpenRecordset("tblInvoiceHeadersTemp") Dim lngNum As Long Dim strNextNum As String 'in case there are no records, use NZ to set initial val to 0. lngNum = Nz(DMax("Mid(fldInvoiceNoTxt,2)", "tblInvoiceHeaders"), 0) + 1 Do While Not rs.EOF rs.Edit rs!fldInvoiceNoTxt = "D" & Format(lngNum, "000000") rs.Update lngNum = lngNum + 1 rs.MoveNext Loop etc etc Thanks again - really appreciate it. Gordon
From: Salad on 11 Nov 2009 10:28
gorsoft(a)hotmail.com wrote: > On Nov 10, 7:19 pm, Salad <o...(a)vinegar.com> wrote: > >>gors...(a)hotmail.com wrote: >> >>>On Nov 10, 4:58 pm, Salad <o...(a)vinegar.com> wrote: >> >>>>gors...(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.- Hide quoted text - >> >>>>- Show quoted text - >> >>>Thanks for the tips, guys but I'm not sure I have explained adequately >>>what I am trying to do. I say again. I have temp table of invoice >>>records with a field for Invoice No (text) which is currently blank, >>>I want >>>to update that field to D0000001, D0000002, D0000003 etc. When that >>>first update is done I will import those records into the main invoice >>>records table and empty the temp table. >>>For the next batch of "invoices", I again will >>>have a table of invoice records in the temp table 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. I am doing all this in code from a button >>>on a form. >> >>> The code I am using is: >>> Set db = CurrentDb() >>> Set rs = db.OpenRecordset("tblInvoiceHeadersTemp") >>> strNextInvNum = Nz(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 >> >>>Of course I get a type mismatch error because I am trying to add a >>>number to a text field, but I don't know what to do next. >>> Grateful for any help. >> >>>Gordon >> >>I don't understand your problem. I created a table, Table1, with field >>InvNum. There are 3 records with InvNum values of D1, D2, and D3. I >>run the following. >>Sub InvNum() >> Dim lngNum As Long >> Dim strNextNum As String >> 'in case there are no records, I use NZ to set initial val to 0. >> lngNum = Nz(DMax("Mid(InvNum,2)", "Table1"), 0) + 1 >> strNextNum = "D" & Format(lngNum, "000000") >> MsgBox strNextNum >>End Sub >> >>I get D000004. If there are no records in Table1, I get D000001. Do >>you need more code examples?- Hide quoted text - >> >>- Show quoted text - > > > Hi Salad, > Many thanks for that. Worked a treat. My problem was not > understanding how to apply your orignal tip in the recordset code I > was using behind the form. Your second example made that clearer to > me. Code now reads: > Set db = CurrentDb() > Set rs = db.OpenRecordset("tblInvoiceHeadersTemp") > Dim lngNum As Long > Dim strNextNum As String > 'in case there are no records, use NZ to set initial val to 0. > lngNum = Nz(DMax("Mid(fldInvoiceNoTxt,2)", "tblInvoiceHeaders"), 0) > + 1 > Do While Not rs.EOF > rs.Edit > rs!fldInvoiceNoTxt = "D" & Format(lngNum, "000000") > rs.Update > lngNum = lngNum + 1 > rs.MoveNext > Loop > etc etc > > Thanks again - really appreciate it. > > Gordon :) It's hard for us to know the programming level of a person by their posts. I'm glad you got it working and are ready for the next problem to solve. |