Re: Number sequence

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








.

Relevant Pages

  • Re: Number sequence
    ... Thank you Tina for all the work you put into this. ... okay, the problem with the code is that it is not incrementing a *number*, ... database, you may end up with conflicting data at some point. ... '{increments a digit in a sequence field. ...
    (microsoft.public.access.forms)
  • Re: Number sequence
    ... it is incrementing each *character* in the string ... incrementing the dash to the next Ascii character, ... the suggestion posted by UpRider would solve your immediate problem, ... database, you may end up with conflicting data at some point. ...
    (microsoft.public.access.forms)
  • Re: Number sequence
    ... it is incrementing each *character* in the string ... incrementing the dash to the next Ascii character, ... database, you may end up with conflicting data at some point. ... '{increments a digit in a sequence field. ...
    (microsoft.public.access.forms)
  • Re: Rename of FileName using PERL
    ... database table where column is seqquence number. ... Perform a database query, giving it the file name and receiving a sequence number. ...
    (comp.lang.perl.misc)
  • Increment & back page
    ... I have a database and a webpage with a table view linked to a formview to ... I would like to be able to display the number of times a particular record ... Is there an easy way of incrementing a value with SQL code or do I need to ...
    (microsoft.public.dotnet.framework.aspnet.webservices)
Loading