If Statements to Check Record Exists

[sk1hotpepr]

I am trying to populate a field on a form that will give me the sum of two numbers if that project number exists or return a zero value if it does not.

Table
ProjectNumber (check to see if there is a project number)
PropCost (sum)
AppCost (sum)

If there is a record, my equation works but I'm not sure how to get a zero if there is no record. This is in a textbox field with my Control Source containing a select statement. Any ideas/ direction? I've been staring at this too long to make sense anymore.

 

[markgrizzle]

If the form's recordsource is a query, created a calculated field in the query named something unique like SumPropAppCosts: iif(isnumeric([ProjectNumber]),nz([PropCost],0)+nz([AppCost],0))

What this string means is...
if ProjectNumber contains a number, then add PropCost to AppCost and Display the result as SumPropAppCosts.

The nz function (null to zero) returns a zero for the PropCost field, if there's a null value in the field.

Hope this helps,
Mark

 

[sk1hotpepr]

Mark,
That was quick! The other problem is that I have a subform linking that Project Number together, but there isn't always a record started yet so obviously I get an error. Would it be better to remove the child/parent link and check for that project number some other way? Once I get that part figured out the iif statement will help.

Thanks again!
Sherry

 

[markgrizzle]

Hi Sherry:
I haven't often worked with subforms. You might try setting a default (zero?) value for the Project Number field. Then the subform would find no matches, and generate no error.

Hope that helps,
Mark