Invoice number

  • Thread starter Thread starter BetHa
  • Start date Start date
B

BetHa

Hello,

The code below gives me an invoice number like: 20100301, 20100302, 20100302
etc...
I want to add a letter in front of it, like S-20100301, S-20100302,
S-20100302 etc...

Please can somebody help me out to change the code below?

TIA

Betha


Private Sub Form_BeforeInsert(Cancel As Integer)
Dim tNummer As String
tNummer = Format(Right(DatePart("yyyy", Date), 4), "0000") &
Format(DatePart("m", Date), "00") & "-"

If DCount("*", "Orders", "left(Factuurnr,7)= '" & tNummer & "'") = 0 Then
Me.Factuurnr = tNummer & "01"
Else
Me.Factuurnr = tNummer & Format(DMax("right(Factuurnr,2)", "Orders",
"left(Factuurnr,7)= '" & tNummer & "'") + 1, "00")
End If
End Sub
 
hi Betha,

The code below gives me an invoice number like: 20100301, 20100302, 20100302
etc...
Click to expand...
Nope, it gives you 201003-01, 201003-02 etc.
I want to add a letter in front of it, like S-20100301, S-20100302,
S-20100302 etc...
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim tNummer As String
tNummer = Format(Right(DatePart("yyyy", Date), 4), "0000")&
Format(DatePart("m", Date), "00")& "-"
Click to expand...

tNummer = Format(Date(), "\S-yyyymm-")



mfG
--> stefan <--
 
Hi BetHa,
you can change those lines in this way
If DCount("*", "Orders", "left(Factuurnr,7)= '" & tNummer & "'") = 0 Then
Me.Factuurnr = "S-" &tNummer & "01"
Else
Me.Factuurnr = "S-" & tNummer & Format(DMax("right(Factuurnr,2)",
"Orders",
"left(Factuurnr,7)= '" & tNummer & "'") + 1, "00")
End If

or if you prefer to use the solution Stefan proposed in order to make it
works, you must replace all the occurrance of left(Factuurnr,7) with
left(Factuurnr,9)

HTH Paolo
 
Tx paolo for your solution but it gives me a problem when executing it:

It says;

"failure 2147352567 (80020009) During executing field is too small for the
amount of data that you are etc....
Can you figure out what the problem is?

Tx

betha
 
Bruce,

It happens at:
Me.Factuurnr = "S-" &tNummer & "01"

TIA

Betha

BruceM via AccessMonster.com said:
Perhaps the text field is too small for the value. On what line does the
error occur?
Tx paolo for your solution but it gives me a problem when executing it:

It says;

"failure 2147352567 (80020009) During executing field is too small for the
amount of data that you are etc....
Can you figure out what the problem is?

Tx

betha
Hi BetHa,
you can change those lines in this way
Click to expand...
[quoted text clipped - 37 lines]
End If
End Sub
Click to expand...
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
Back
Top