Field calcuation with an included yy

[Stinky Pete]

Hi,

I work in a manufacturing environment that needs to record non
conformances (NCF) against goods or processes. At the moment, QA can
open a form to automatically generate a new NCF number each time the
"New NCF" button is pressed on the main form. This number is in the
format yy-xxxx (see the code below). Each day we need to generate a
list of NCF numbers for manufacturing so that night shift can allocate
an NCF as necessary but this is done manually in Excel, not the
database. What I want to do is generate a report that at the end of
the day provides a lsit of about 10 new NCF numbers calcuated from the
last NCF number entered that day. It is not an option to allow
manufacturing access to the db.

What I was thinking of doing was generating a report that uses the code
below to make the first new number (one above the last entry) in a
field and then add 1 to this number etc up to the 10, via a new text
field or the like. The unsed manually generated numbers are not
entered into the db, so there's no chance of duplication when morning
shift comes in the next day. Anyway, if anyone can show me how to add
one to the highest number in the NCR field and not worry about the
"yy-" in the front of the field, please feel free to respond.

Regards,
Peter ;-)

Private Sub Form_Load()

Dim rst, NCRNumber, strcriteria, ddate, ddatestart, ddateend

Dim stDocName As String
Dim stLinkCriteria As String
Dim dbs As Database
ddate = Format(Date, "yy")
'ddatestart = ddate & "-" & Format(1, "0000")
'ddateend = ddate & "-" & "9999"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("NCR_Table New", dbOpenDynaset)

For NCRNumber = 1 To 9999
strcriteria = "[NCR] = " & "'" & ddate & "-" & Format([NCRNumber],
"0000") & "'"
With rst
.FindFirst strcriteria
If .NoMatch Then
Exit For
End If
End With
Next NCRNumber

stDocName = "NCF Manual Numbers"
DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
Forms![NCF Manual Numbers]![NCR] = ddate & "-" & Format(NCRNumber,
"0000")
'Forms![NCF Manual Numbers]![Date Initiated] = Date

End Sub

[pietlinden]

Read, drink, be merry... Someone has already solved this problem for
you...

http://www.mvps.org/access/strings/str0007.htm

[Stinky Pete]

pi********@hotmail.com wrote:

Read, drink, be merry... Someone has already solved this problem for
you...

http://www.mvps.org/access/strings/str0007.htm

Hi,

I did read, and got very merry - well sort of. Anyway, I get the gist
of what you're saying, with two following questions. The table has NCF
numbers from 4003-7854 (ish) with no preceeding yy- option and this
solution does not appear to work with the existing yy- formated numbers
unless the 4000-7000 numbers are deleted from the table (which I can't
do for traceability). The second quesiton, how do you get the leading
0 to remain in the field. Currently one of the higest numbers is
06-0895, but this funtion changes it to 06-865.

Hope to hear back,

Peter ;-)

[Donald Grove]

Your problem is that you have a data type number, and you need data
type text. A data type number will never have preceding zeroes.

I once had the same problem with preceding zeros, and the number of
preceding zeros varied depending on which table required such a
number. So I wrote a function that took two arguments: the number in
question and the total number of characters required (which in your
case is 4).

Function addzeros(lngNumber as long, _
intDesiredLength as integer )strNewTextNumber as string
dim intCurrentLength as integer
dim strTextNumber as string

'make the number into a text string
strTextNumber = lngNumber

'find it's length
intCurrentLength = len(strTextNumber)

'loop adding zeros until the desired number of zeros are added
do while intCurrentLength < intDesiredLength
strTextNumber = "0" & strTextnumber
intCurrentLength = len(strTextNumber)
loop

addzeros = strNewTextNumber
End Function
On 26 Jun 2006 23:09:42 -0700, "Stinky Pete"
<Pe**********@symbionhealth.com> wrote:

pi********@hotmail.com wrote:

Read, drink, be merry... Someone has already solved this problem for
you...

http://www.mvps.org/access/strings/str0007.htm

Hi,

I did read, and got very merry - well sort of. Anyway, I get the gist
of what you're saying, with two following questions. The table has NCF
numbers from 4003-7854 (ish) with no preceeding yy- option and this
solution does not appear to work with the existing yy- formated numbers
unless the 4000-7000 numbers are deleted from the table (which I can't
do for traceability). The second quesiton, how do you get the leading
0 to remain in the field. Currently one of the higest numbers is
06-0895, but this funtion changes it to 06-865.

Hope to hear back,

Peter ;-)