Invoice numbers with text

[gor...@hotmail.com]

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

[Rich P]

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 ***

[gor...@hotmail.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

[Roger]

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

[Salad]

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.

[gor...@hotmail.com]

On Nov 10, 4:58 pm, Salad <o...@vinegar.com> wrote:

> 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

[Salad]

gor...@hotmail.com wrote:

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?

[gor...@hotmail.com]

> 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

[Salad]

:)

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.