Calculated field - some tips

[JamesWB]

Hi, I have to do a formula based on the input in four fields and display the calculated value in a report. I only need to use this calculated value in one report.

Is the best way to do this to use a calculated field in the table where the four fields are located? Or should I be thinking of performing the calculation when I display it in the report using VBA?

Also please, I don't want to run the calculation if only one or none of the four fields are empty. How do I test for this in a calculated field expression?

I am using Access 2013.

thanks!

 

[plog]

Or should I be thinking of performing the calculation when I display it in the report using VBA?

I would build a function inside a module that takes your values and returns the calculated value. Checking for null values would be part of the function's logic. Here's an example of a function to help get you started:

Public Function get_Sum(in_A, in_B, in_C) 
    '  adds 3 values together, if any are null, returns 0

ret = 0          
    ' return value, default is 0

If(IsNull(in_A)=False AND IsNull(in_B)=False AND IsNull(in_C)=False) Then ret = in_A + in_B + in_C  


get_Sum = ret

End Function

Then to call it, like in a report you would use this:

=get_Sum([field1], [field2], [field3])

 

[JamesWB]

I would build a function inside a module that takes your values and returns the calculated value. Checking for null values would be part of the function's logic. Here's an example of a function to help get you started:

Public Function get_Sum(in_A, in_B, in_C) 
    '  adds 3 values together, if any are null, returns 0

ret = 0          
    ' return value, default is 0

If(IsNull(in_A)=False AND IsNull(in_B)=False AND IsNull(in_C)=False) Then ret = in_A + in_B + in_C  


get_Sum = ret

End Function

Then to call it, like in a report you would use this:

=get_Sum([field1], [field2], [field3])

That sounds interesting, thanks, I will try it. Where do I store a Module in Access, does that have to be associated with an event from a form or a query or something, or is it separate?

 

[plog]

A module is its own object. You can just go to Create-Macro-then select Module and then put your code in the screen that comes up. Do a google search and there are lots of tutorials.