Generate numbers with fixed interval (1 Viewer)

[Sarma]

I have a range say 12.03 to 13.11.

I need to generate all numbers falling within this range with 0.01 increment.
(This increment is based on the numbers; if the numbers have 2 decimals, it should be 0.01. If the numbers have 3 decimals, the increment should be 0.001. Both the numbers will have the same number of decimals)

The answer is: 12.03, 12.04, 12.05, etc. etc. till 13.10, 13.11.

Is there a way we can do it in MS Access ?

Grateful for help.

Regards

Sarma

 

[Uncle Gizmo]

Are these numbers in a Table?

If so what does the Table look like?

How to Show your Table in a Post

 

[Sarma]

Thanks, friend.

[Table 1]:
Minimum Value - 12.01
Maximum Value - 13.11
Increment - 0.01

Answer will be in a query [Numbers]:

 

[Uncle Gizmo]

When are these values added to the table?

In other words, what trigger are you going to use to add them?

 

[Sarma]

I do not have a trigger. But I will create the table which contains the minimum number, the maximum number and the uniform increment. When I click on the query, system must be able to generate the numbers in a field 'Number'. Possible ?

 

[Uncle Gizmo]

So your Table HERE:-

Thanks, friend.

[Table 1]:
Minimum Value - 12.01
Maximum Value - 13.11
Increment - 0.01

Answer will be in a query [Numbers]:

Will control the values added to the field 'Number' in a new Table?

 

[Sarma]

There is no addition involved.

I am looking for an answer as under:

Query: [Numbers]

Field: Number

Values

12.03
12.04
12.05
etc
etc
13.11

 

[Uncle Gizmo]

Where is the table that contains the field 'number'?

 

[Sarma]

I don't know if I am able to clearly show my problem.

I have a Table [Table 1]

This contains 3 fields.

[Minimum No]
[Maximum No]
[Increment]

The data is as under:

Minimum No - 12.03
Maximum No - 13.12
Increment - 0.01

Now I need to create a Query [Query 1]

This query should have a field [Number]

The field must contain all numbers between 12.03 to 13.12 with an increment of 0.01.

The answer would have the following numbers:

12.03
12.04
12.05
etc
etc
13.11
13.12

Hope I am clear.

 

[JHB]

You can't fetch values into a query out of "the air", so create a sub/procedure which insert the numbers in a table, then you can base the query on that table.
I've made a small sample for you, open the form and click the button.

 

[gemma-the-husky]

bear in mind that it is not possible to construct some numbers exactly.

for instance 0.1 is not exactly representable in a binary format. There you have to be very careful how you use real numbers, as you may get unexpected errors

 

[Sarma]

JHB: You have created the Table [tblMinMax]. This should be the input. You have created another table tblNumbers]. Where from this table come from ? This should be the outcome of a query but not a table because I do not want to create this table. I want to get it as an output of a query. Perhaps, some VB command may have to be given wherein starting with minimum value, numbers must be generated with the increment till the maximum value is reached. This is the solution that I am looking for. Thanks for your interest.

 

[gemma-the-husky]

as JHB said, a query has to be based on a table - so you need to store the values in a table

you could store the values in an array, if you want to go that way - but then you won't get a query, just vba to process the array.

 

[JHB]

JHB: You have created another table tblNumbers]. Where from this table come from ? This should be the outcome of a query but not a table because I do not want to create this table. I want to get it as an output of a query. Perhaps, some VB command may have to be given wherein starting with minimum value, numbers must be generated with the increment till the maximum value is reached. This is the solution that I am looking for. Thanks for your interest.

Sometimes there is a difference between what you want to get, and what it is possible to get.
And this is one of those cases.
If you want the result in a query, then create a query based on the table - as mentioned before, you can not fetch values ​​into a query out of "the air"!

 

[BlueIshDan]

How is it that you're going to identify which
[Table1] Record you're going to query? I suggest and id field.

with that you'll be able to create your temp table and query it.

 

[BlueIshDan]

How does this work for you?

' Inputs: Ranges table record id[ID].
' Proccess: Use ID to retrieve the min, max and increment values.
'           Remove all records from Result table and Insert range
'           of numbers into the Result table.
'           Display the QResult DataSheet.
' Outputs: None
Private Sub GenerateRange(ByVal ID As Integer)

    '// Declarations
    Dim i As Double
    Dim rsRanges As Recordset
    Dim rsResults As Recordset
    
    '// Database Connections
    
    '    Rangess table rs
    Set rsRanges = CurrentDb.OpenRecordset("SELECT * FROM Ranges WHERE ID = " & ID)
    
    '    Results table rs
    '       Be sure to clear the table before inserting new records.
    CurrentDb.Execute "DELETE * FROM Results"
    Set rsResults = CurrentDb.OpenRecordset("Results")
    
    '// Loop through Range Table
    '//     from min to max stepping through by inc
    With rsRanges
        .MoveFirst
        For i = !min To !max Step !increment
            ' Insert new record
            rsResults.AddNew
            rsResults!Number = i
            rsResults.Update
        Next
    End With
    
    '// Close Tables.
    rsResults.Close
    rsRanges.Close
    
    '// Open Query
    DoCmd.OpenQuery "QResults", acViewNormal, acReadOnly ' acReadOnly  acAdd acEdit
    
    '// Refresh All - Display New Data Instead of
    '                 #Deleted value in field.
    SendKeys "+({f9})"
End Sub

Query [QResults]:

       SELECT Results.number
       FROM Results
       ORDER BY Results.number;

Table [Ranges]

min - Number(decimal)
max - Number(decimal)
increment - Number(decimal)

Table [Results]

number - Number(decimal)