Re: Number sequence

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thank you Tina

"tina" wrote:

well, you're welcome, though it sounds like it's not going to be useful to
you. but you can always learn from reading other people's code, so here it
is:

Public Function GenNextSequence2(ByVal tbl As String, _
ByVal fld As String) As String

Dim rst As DAO.Recordset, strSQL As String
strSQL = "SELECT Max(CLng(Right([" & fld _
& "],Len([" & fld & "])-3))) AS SeqVal " _
& "FROM " & tbl & " WHERE (((Left([" _
& fld & "],2))=Right(Year(Date()),2)))"

Set rst = CurrentDb.OpenRecordset(strSQL, dbReadOnly)

GenNextSequence2 = Right(Year(Date), 2) _
& "-" & Format(Nz(rst("SeqVal"), 0) + 1, "0000")

End Function

as for forcing users to enter four digits, looks like Uprider's code should
work. i'd have thought that an input mask woudl, as well - but we're working
pretty much in the dark here re the tables and forms setup and how the
program runs.

if there's anything else i can do to help you out, don't hesitate to ask. i
pretty much have a standing offer of assistance to anyone in firefighting,
law enforcement, or military.

hth


"Mark G" <MarkG@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5AD209B1-E931-4127-9768-1029614323EF@xxxxxxxxxxxxxxxx
Thank you Tina for all the work you put into this. I would be curious to
see
the code you wrote. I did a few tests with UpRiders solution and it
seamed
to work well as long as the four digit format is used. If it is allowed
to
automatically assign the numbers I don't think there will be a problem,
but
we have people who insist on entering the run number on their own and only
use two or three digits. I tried to use an input mask to force the
correct
format but it didn't work. Would this be because of the custom code? I
tried it in both the table properties and also the field properties on the
form.

Thanks again Tina for the time you put into it
Mark

"tina" wrote:

yes, the code does use that custom function also. (very good catch!)

okay, the problem with the code is that it is not incrementing a
*number*,
such as 999. instead, it is incrementing each *character* in the string
"-999" as a text value; so it is looking at "9" and returning a zero for
each "9" value (because no single character number is higher than 9),
then
incrementing the dash (-) to the next Ascii character, which is a period
or
dot. that accounts for the return value of "07.000" that i got when i
tested
the code.

the suggestion posted by UpRider would solve your immediate problem, i
think. but you're going to run into the same issue every year, unless
you
have less than 1000 fire runs in a year. i can post an alternate
function
that will automatically increment the number, from 1 up to 9999 (i'm
guessing you'll never have that many runs in one year!). i wrote the
function to automatically restart the increment on the first record of
each
new year - based on the assumption that if you log a run at 12:01 AM on
Jan
1, you will want that run to count as the first run of the new year. if
you
have lag time between the run and the logging of it, that may be an
issue
for you.

here are some other issues you have to consider: whether you use my
solution or Uprider's, you're going to have problems with sorting the
stored
values in the table. because the value is Text, you won't get the right
sequence from *this year's* records. instead, an ascending sort on the
field
will return

001
002
....
099
100
1000
1001
....
1009
101
1010
1011
....
1019
102
1020
1021
....
1029
103
1030
1031
....
1039
104
etc, etc.

you can address this issue by using an Update query to change all the
existing RUN# field values for 2007 to match the "-0000" format. this
isn't
hard to do, but you'll want to make a COPY of the database as a backup
FIRST, so if you hose the data you can start over with a new copy of the
backup.

another issue with my solution is that we don't know where else the
original
GenNextSequence() function may be called. if it is called elsewhere in
the
database, you may end up with conflicting data at some point. it's
impossible to say for sure without seeing the database itself.

if you want to see the alternate function i wrote, and/or if you want
help
with writing an Update query, or have any other questions, post back

hth


"Mark G" <MarkG@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4E333217-EF4C-48CC-A39F-D43EBE796352@xxxxxxxxxxxxxxxx
It looks like it also uses "incrementdigit" Here is that one as well.

Function IncrementDigit(c As Byte) As Byte

'{increments a digit in a sequence field. Returns true if there is no
carry,
false if another
' carry operation on the next most significant digit must be
performed.}
' begin
If (Chr(c) <> "9") And (UCase(Chr(c)) <> "Z") Then
IncrementDigit = c + 1
Else
If c = Asc("9") Then IncrementDigit = Asc("0")
If c = Asc("z") Then IncrementDigit = Asc("a")
If c = Asc("Z") Then IncrementDigit = Asc("A")
End If
End Function

Thanks
Mark

"tina" wrote:

well, i see that the function is using another custom function,
"AdvanceSequence()". since i don't see anything that would account
for
the
result you described (07.0000), i think we'll need to see that
function
procedure too. find the function, the same way you found the first
one,
and
post the code, please.

hth


"Mark G" <MarkG@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:49EAB3D6-7744-4531-B6A9-EA910429D52B@xxxxxxxxxxxxxxxx
Thank you for the quick reply, and the instructions. Here is the
information.

Public Function GenNextSequence(TableName, FieldName, Seed)
' gets next sequence value from TableName and FieldName, Seed is
the
starting value
Dim rs

On Error Resume Next
Seed = Nz(Seed, "0")
Set rs = CurrentDb.OpenRecordset("Select TOP 1 [" & FieldName &
"]
as
SeqVal from [" & TableName & "] ORDER BY [" & FieldName & "]
DESC")
If Not rs.EOF Then Seed = CStr(IIf(Nz(rs("SeqVal"), "") = "",
Seed,
rs("SeqVal")))
GenNextSequence = AdvanceSequence(CStr(Seed))
If Err Then GenNextSequence = "#SeqErr"
rs.Close
Set rs = Nothing

End Function

Thanks
Mark

"tina" wrote:

looks like a custom function: GenNextSequence(). assuming that
this
is
where the number assignment is actually coming from, we'd need
to
see
the
function procedure in order to make recommendations.

open your database, and open any module on the Modules tab. from
the
menu
bar, click Edit | Find. in the Find dialog, type "Function
GenNextSequence("
only without the double quotes. in the Search section below,
choose
Current
Project, then click Find Next.

you should find the function procedure, which starts with the
line
you
typed
in (Function may be preceded by Public), and ends with "End
Function" -
again, sans quotes. copy the entire procedure from beginning to
end,
and
paste it into a post, so we can see it.

hth


"Mark G" <MarkG@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:585481AD-82D6-44E9-85B4-BE8570063882@xxxxxxxxxxxxxxxx
We have a field in one of our forms in our database that
automatically
generates a run number for fire calls in sequence. The format
that we
use
is
07-999. Everything was working fine until we hit what should
have
been
07-1000. Now when it is supposed to assign the next number in
the
sequence
it displays 07.0000. Any help would be greatly apreciated. I
just
dabble
with access and have no formal training which is more than the
rest of
the
department, and since I do I kind of inherited this database
we
are
using.
This code is in a tab labeled "calculations" on a subform. I
think
this
is
what is telling the field what to do.
=GenNextSequence("daylog3","RUN#","05-0001"). I would like it
to
transition
from 07-999 to 07-1000 just as it did for the first 999
numbers.

Thanks
Mark












.

Quantcast