Assign value to set of values (1 Viewer)

Russp42

Registered User.
Local time
Yesterday, 18:27
Joined
Nov 27, 2017
Messages
53
I have a calculated field in a table which returns the difference of 2 values. If the difference is between 1 and 18 I want to return a new value of 1, between 19 and 36 =2 and 37 and above =3. How do I do this?
 
Last edited:

MarkK

bit cruncher
Local time
Yesterday, 18:27
Joined
Mar 17, 2004
Messages
8,178
What if the difference is less than one? What if the difference is between 18 and 19, or between 36 and 37?
Not sure we have all the bases covered yet,
Mark
 

A S MANN

New member
Local time
Today, 06:57
Joined
Oct 18, 2017
Messages
9
CalValue : Int([Value]/18 ) +1
value CalValue
<18 1
<36 2
Etc.
 

apr pillai

AWF VIP
Local time
Today, 06:57
Joined
Jan 20, 2005
Messages
735
Call this Function with the value as parameter and get the new number:
Code:
Public Function Test(ByVal getval As Integer) As Integer

Select Case getval
    Case 1 To 18
       Test = 1
    Case 19 To 36
       Test = 2
    Case Is > 36
       Test = 3
End Select

End Function

x = Test(25)
Result: 2
 

apr pillai

AWF VIP
Local time
Today, 06:57
Joined
Jan 20, 2005
Messages
735
First, copy the VBA Code given below into VBA Standard Module.
  1. Press ALT+F11 to display VBA Window.
  2. Select Module from Insert Menu.
  3. Copy and Paste the following Code into the Module and save the Code.


    Code:
    Public Function Test(ByVal getval As [B]Double[/B]) As Integer
    
    Select Case getval
        Case 1 To 18
           Test = 1
        Case 19 To 36
           Test = 2
        Case Is > 36
           Test = 3
    End Select
    
    End Function
  4. Create a Select Query.
  5. Insert the following expression into a New Colum where you calculate the differenc:

Difference:Test([Colum1]-[Column2])

Replace Column1 and Column2 with Column Names on which you find the difference.

You may use the expression =Test([Column1]-[Column2]) in a Textbox Control on a Form, linked with your Table, to display the difference on the Form or Report. Permanently storing the result of these kind of calculations in the Table is not advisable.

As far as I know the expression cannot be used in an Access Table Calculated Field.
 
Last edited:

Russp42

Registered User.
Local time
Yesterday, 18:27
Joined
Nov 27, 2017
Messages
53
What if the difference is less than one? What if the difference is between 18 and 19, or between 36 and 37?
Not sure we have all the bases covered yet,
Mark

Differences are whole numbers
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:27
Joined
Feb 19, 2002
Messages
42,981
For stuff like this, I'm more inclined to make a table along with a maintenance form if it is something the user can manage himself. If only the programmer can change the ranges, then I don't make a form unless it would be simpler for the programmer to use a form than to update the table directly.
 

Russp42

Registered User.
Local time
Yesterday, 18:27
Joined
Nov 27, 2017
Messages
53
First, copy the VBA Code given below into VBA Standard Module.
  1. Press ALT+F11 to display VBA Window.
  2. Select Module from Insert Menu.
  3. Copy and Paste the following Code into the Module and save the Code.


    Code:
    Public Function Test(ByVal getval As [B]Double[/B]) As Integer
    
    Select Case getval
        Case 1 To 18
           Test = 1
        Case 19 To 36
           Test = 2
        Case Is > 36
           Test = 3
    End Select
    
    End Function
  4. Create a Select Query.
  5. Insert the following expression into a New Colum where you calculate the differenc:

Difference:Test([Colum1]-[Column2])

Replace Column1 and Column2 with Column Names on which you find the difference.

You may use the expression =Test([Column1]-[Column2]) in a Textbox Control on a Form, linked with your Table, to display the difference on the Form or Report. Permanently storing the result of these kind of calculations in the Table is not advisable.

As far as I know the expression cannot be used in an Access Table Calculated Field.
Running the Query produces error "Undefined function Test in expression". The text box on the form produces an error. I don't particularly want to store the result. I only created the calculated field as I am not sure whether you can do a calculation on a calculation in a query
 
Last edited:

Russp42

Registered User.
Local time
Yesterday, 18:27
Joined
Nov 27, 2017
Messages
53
For stuff like this, I'm more inclined to make a table along with a maintenance form if it is something the user can manage himself. If only the programmer can change the ranges, then I don't make a form unless it would be simpler for the programmer to use a form than to update the table directly.
I do not want to update the table. What I eventually want to achieve is a form with a number of calculations based on 3 fields in a table with fixed values, and 2 text boxes to enter variable values. My problem is getting the result of a calculation returning a different value which I can then use in another calculation I will amend and elaborate on my original thread
 

apr pillai

AWF VIP
Local time
Today, 06:57
Joined
Jan 20, 2005
Messages
735
Are you sure you have copied the code into a Standard VBA Module (Global Module).

Open the module again and try to Compile all the VBA Code by selecting the option from the top menu. If you have errors in any of the code in your database. It may not work.

If you could compile the entire database code without errors then the Test() Function will work, provided the value you pass to the function is numeric type.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:27
Joined
Feb 19, 2002
Messages
42,981
And if you are having a problem with a query, always post the SQL string so we can see what you are running.
 

Users who are viewing this thread

Top Bottom