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?
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
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
First, copy the VBA Code given below into VBA Standard Module.
Press ALT+F11 to display VBA Window.
Select Module from Insert Menu.
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
Create a Select Query.
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.
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
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.
First, copy the VBA Code given below into VBA Standard Module.
Press ALT+F11 to display VBA Window.
Select Module from Insert Menu.
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
Create a Select Query.
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
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
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.