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

 

[BruceM via AccessMonster.com]

If you need the S- to show for every number, just add the prefix for display
purposes:

Me.UnboundTextboxName = "S-" & Me.Factuurnr

On another point, the numbers you show do not contain a hyphen, but your code
adds one, so I don't know quite what is going on there. I would increment a
number, then append it to the year and the month. If tNummer contains only
the incrementing number you could not use it alone as a unique field such as
primary key, but if that is not a consideration here is the way I would
probably do it, assuming you are storing the date in DateField:

Me.Factuurnr = Nz(DMax("Factuurnr","Orders", _
"Year([DateField] = " & Year(Date()) & _
"And Month([DateField] = " & Month(Date()),0) + 1

To display the number, in a query based on the Orders table:

FullNumber: "S-" & Format([DateField],"yyyymm") & "-" & Format([Factuurnr],
"00")

If you need to store the year and month parts of the number because you are
using it as the primary key field or it otherwise needs to be unique, I would
still store the value as a number, but would use a different Nz value than in
the previous example:

Dim lngNum as Long

lngNum = CLng(Format(Date,"yyyymm") & "00")

Me.Factuurnr = Nz(DMax("Factuurnr","Orders", _
"Year([DateField] = " & Year(Date()) & _
"And Month([DateField] = " & Month(Date()),lngNum) + 1

To display the number:

FullNumber: "S-" & Left([Factuurnr],6) & "-" & Right([Factuurnr],2)

In any case, two points: Note that there are simpler ways than you have used
to extract the year and month portions of a date; and it simplifies matters
if you increment a number field rather than a text field containing non-
alphanumeric characters such as a hyphen.

In a multi-user environment you may want to take extra precautions against
the possibility of a duplicated number. The form's Before Update event is
one way to be almost certain two users will not attempt to grab the same
number. The trouble with Before Insert is that the record is not saved, and
another user could come along and start a record before the first record is
saved. There are other ways, but Before Update is probably among the
simplest.

 

[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

 

[BruceM via AccessMonster.com]

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]

 

[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

--



.