Subtracting two fields

[AbeA]

I have three fields in a form. "Starting Miles", "Ending Miles", and "Total Miles" I want the "Total Miles" field to automatically calculate the difference between Starting/Ending miles fields. So if starting miles is 2000 and ending miles is 2200 I want the total miles to list 200.

Thanks.

 

[boblarson]

Now, you shouldn't store Total Miles as you can calculate it from Starting and Ending miles any time. So, put this in the Total Miles control source property:

=Nz([Ending Miles],0) - Nz([Starting Miles],0)

 

[GAB]

Now, you shouldn't store Total Miles as you can calculate it from Starting and Ending miles any time. So, put this in the Total Miles control source property:

=Nz([Ending Miles],0) - Nz([Starting Miles],0)

Hi Bob,

Because I don't know, why the nz and the 0 instead of just =[ending miles]-[starting miles]? I'm not questioning that you are right I am inquiring for my own education.

Thanks,

Gary

 

[boblarson]

Hi Bob,

Because I don't know, why the nz and the 0 instead of just =[ending miles]-[starting miles]? I'm not questioning that you are right I am inquiring for my own education.

Thanks,

Gary

Calculations will give you an #Error if any of the fields are NULL so best to deal with them so they don't give the error. Also, if you want to have an aggregate sum at the bottom of the form, or report, if you don't use the NZ function to eliminate nulls, it won't give you a result either.

 

[GAB]

Calculations will give you an #Error if any of the fields are NULL so best to deal with them so they don't give the error. Also, if you want to have an aggregate sum at the bottom of the form, or report, if you don't use the NZ function to eliminate nulls, it won't give you a result either.

That Clarifies it for me. I've been setting the defaut to =0 in the properties box. This makes a lot more sense. Thanks for the response on this.

Gary

 

[AbeA]

Now, you shouldn't store Total Miles as you can calculate it from Starting and Ending miles any time. So, put this in the Total Miles control source property:

=Nz([Ending Miles],0) - Nz([Starting Miles],0)

OK, gotcha. You've clairifed a bunch for me. So, what would be best to use to calculate the starting/ending miles to get the total miles? should I just create a new query? Ultimately this will be appearing in a Report - should I create it in the report instead?

Thanks.

 

[boblarson]

You can do it in a query and just create a column:

TotalMiles:IIf(Nz([Ending Miles],0) > Nz([Starting Miles],0), Nz([Ending Miles],0) - Nz([Starting Miles],0),0)

The extra IIf is to check to make sure that you don't have the Ending miles null and then replacing a zero and then subtracting starting miles which might have an entry but no ending entry. If that occurs then zero is returned.