Invoice number

[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

 

[Stefan Hoffmann]

hi Betha,

The code below gives me an invoice number like: 20100301, 20100302, 20100302
etc...

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")& "-"

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



mfG
--> stefan <--

 

[Paolo]

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

 

[BetHa]

Tx for the effort guys!!

Works great!!

KR. Betha

 

[BetHa]

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

 

[BetHa]

Bruce,

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

TIA

Betha

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

[quoted text clipped - 37 lines]

End If
End Sub

--
Message posted via AccessMonster.com


.