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 said:
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 said:
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
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
:
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
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
