# Help With Some Calculations

This topic is 5713 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

First Calculation:

I have a calculation that gives me a family’s entire home address in vertical fashion. It works great. The address ultimately looks like this:

Steve Dutton

123 Main St.

1st Floor

City, State 11111

USA

That’s the result of this calculation:

"¶" & Home Address 2) & "¶" & Home City &Case(not IsEmpty(Home State);

", " & Home State) & " " & Home Postal Code & "¶" & Home Country

My problem is, some people don’t have a home address; they only give a work address. What do I have to add to the above calculation to have the calculation result completely blank (so that if there’s no “home address 1”, the calculation stops right there). For my merge document in Microsoft Word to work properly, I need this calculated field to be totally blank if there’s no home address.

Second Calculation:

I want to combine one’s entire home address and one’s entire work address in a vertical fashion. If I have both addresses in my database, it works great. The combined addresses look like this:

123 Main St.

1st Floor

City, State 11111

USA

456 Main St.

2nd Floor

Work City, Work State 22222

USA

That’s the result of this calculation:

My problem here is similar to the first calculation problem. Some people have just a home address, and some people have just a work address. With my calculation, I get this:

456 Main St.

2nd Floor

Work City, Work State 22222

USA

Or vice versa.

123 Main St.

1st Floor

City, State 11111

USA

What I need is for the calculation to evaluate if there’s just a home address or just a work address or both or neither.

It should ultimately look like this:

123 Main St.

1st Floor

City, State 11111

USA

OR

456 Main St.

2nd Floor

Work City, Work State 22222

USA

OR

123 Main St.

1st Floor

City, State 11111

USA

456 Main St.

2nd Floor

Work City, Work State 22222

USA

OR

(nothing – empty field)

Hope this all makes sense,

Steve

##### Share on other sites

Try writing your calculation to check to make sure the necessary fields contain data, and the correct data at that. If the fields contain data and that data is kosher, then have the calc proceed to putting that data together in the format you require. Else, have it output nothing, or a default.

By using a function to evaluate the fields first to make sure they have valid data, you can avoid printing addresses missing a city, state, zip or street address.

##### Share on other sites

One technique I've used in the past was an auto-entry calc number field that held either a 1 or a 0. The calc would sit around doing nothing but evaluating the data entry fields every time they were changed. If the data was kosher, the validation field would contain "0". If something was wrong, the field would contain "1".

All I would have to do then was have my scripts that worked with the data in those fields check the validation field, and if the field was "0", the script/calculation would go ahead. If not, the script would escape with an error, or continue along another logic branch until it output something else altogether.

A dark whisper in my head would repeat silently whenever I used that particular trick, "One calc to rule them all, and in the darkness bind them."

##### Share on other sites

You gave the key to the solution in your second calc, you just need to use it:

First calculation =

Let (

"¶" & Home Address 2) & "¶" & Home City &Case(not IsEmpty(Home State);

", " & Home State) & " " & Home Postal Code & "¶" & Home Country

;

)

Second calculation =

Let ( [

homeAddress = << same as above >> ;

workAddress = << a similar calculation here >>

] ;

Case ( not IsEmpty ( homeAddress & workAddress ) ; ¶ ) &

)

##### Share on other sites

Thank you for all your help.

Steve

##### Share on other sites

This topic is 5713 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account