Need to find a specific set of numbers in a record set and increment them by 0.000001

[forbesk]

Hello everyone,
I inherited this database that is used by my department. Condition IDs are numbers such as 1.00005 1.00006, each number means something to someone, but nothing to me. The users have asked me to create routine that will resequence these numbers from specific spot. For example, I need to add a condtion between condition 1.00005 and 1.00006, so let's have a new condition inherit the 1.00006 number and every number after, that begins with 1 needs to be renumbered.

Stop when you get to number 2.
This is what I have so far: My two big problems:
* Can not get variables to work for the beginning and ending numbers (Right now I have actual numbers in there as placeholders)
* Can not get the records to Increase by 0.000001 (The numbers are either replaced by 0.00001 or they switch to increasing from 0.00000 instead of whatever numbers I have set as the begin and end numbers.

Private Sub Test_Click()
Dim dbs As DAO.Database
Dim rstConditions As DAO.Recordset
Dim strSQL As String
Dim intI As Integer
Dim LConditionID As Double
Dim LRound As Integer
Dim CurrentNumber As Double




 LConditionID = InputBox("Please enter the first condition number that has to be resequenced.", "Renumber condtion IDS")
 LRound = Round(LConditionID) + 1
 
On Error GoTo ErrorHandler


   Set dbs = CurrentDb


   'Open a recordset on all records from the Condition table that have
   'a value that is equal to the input box.
   strSQL = "SELECT * FROM Condition WHERE (((Condition.ConditionID)>=7.000001 And (Condition.ConditionID)<=8.000001));"
   Set rstConditions = dbs.OpenRecordset(strSQL, dbOpenDynaset)


  
   'If the recordset is empty, exit.
   If rstConditions.EOF Then Exit Sub


   intI = 1
      With rstConditions
      Do Until .EOF
         .Edit
         ![ConditionID] = [ConditionID] + 0.000001
         .Update
         .MoveNext
         intI = intI + 1
      Loop
   End With


   rstConditions.Close
   dbs.Close

Thanks in advance for any help.


Kim

[ssanfu]

Not clear what you want.

If you have values like 1.00005 & 1.00006 (5 decimals), do you want to increment the two numbers to
1.00006 & 1.00007 (5 decimals)
or
1.000051 & 1.000061 (6 decimals)?

Examples would be nice;
what you have & what you want........




This line

         ![ConditionID] = [ConditionID] + 0.000001

should be

         ![ConditionID] = ![ConditionID] + 0.000001

Note the bang (!) in front of the 2nd "ConditionID".


Edit: Maybe something like the attached dB?

[forbesk]

Thanks Steve,
It is actually 6 decimals to 6 decimals--I had to recount the zeros. Worked perfectly thanks.