If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Number sequence
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" wrote in message ... 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" wrote in message ... 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 |
#12
|
|||
|
|||
Number sequence
Mark, I think it might be as simple as going into table daylog3 and finding
the 07-999 in field RUN# and changing it to 07-0999. When 2008 arrives, make sure the first entry is 08-0001, not 08-001. You need 4 digits. UpRider "Mark G" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#13
|
|||
|
|||
Number sequence
Thank You UpRider your'e Awsome. That was too easy.
Thanks Mark "UpRider" wrote: Mark, I think it might be as simple as going into table daylog3 and finding the 07-999 in field RUN# and changing it to 07-0999. When 2008 arrives, make sure the first entry is 08-0001, not 08-001. You need 4 digits. UpRider "Mark G" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#14
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#15
|
|||
|
|||
Number sequence
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#16
|
|||
|
|||
Number sequence
Mark, this code in the before update event will force a user to enter 4 (or
whatever) characters in a textbox. Private Sub DateCode_BeforeUpdate(Cancel As Integer) If Len(DateCode) 4 Then Cancel = True MsgBox "Entry must be exactly 4 digits", vbOKOnly, " D A T A E N T R Y E R R O R " End If End Sub UpRider "Mark G" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#17
|
|||
|
|||
Number sequence
Thanks I will give that a try.
"UpRider" wrote: Mark, this code in the before update event will force a user to enter 4 (or whatever) characters in a textbox. Private Sub DateCode_BeforeUpdate(Cancel As Integer) If Len(DateCode) 4 Then Cancel = True MsgBox "Entry must be exactly 4 digits", vbOKOnly, " D A T A E N T R Y E R R O R " End If End Sub UpRider "Mark G" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#18
|
|||
|
|||
Number sequence
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#19
|
|||
|
|||
Number sequence
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
|
Thread Tools | |
Display Modes | |
|
|