iff or nz statement

[angie]

I am using the following as the default value for a field in a form. if the owner address is not null it shows the information in the field , but if the owner address is null the following customer information listed name,address,city,state,zip does not show. should this be IFF statement and not the NZ statement...

=Nz([Forms]![Customer]![Owner Address],[Forms]![customer]![firstname] & " " & [Forms]![customer]![lastname] & Chr(13) & Chr(10) & [Forms]![customer]![street number] & " " & [Forms]![customer]![address] & Chr(13) & Chr(10) & [Forms]![customer]![City] & " " & [Forms]![customer]![state] & " " & [Forms]![customer]![zip])


this is what is in the control source for the owner address...
=[CompID].COLUMN(1) & Chr(13) & Chr(10) & [CompID].COLUMN(2) & Chr(13) & Chr(10) & [CompID].COLUMN(3) & " " & [CompID].COLUMN(4) & " " & [CompID].column(5)



Did know if you would need the above added just in case Thanks Angie

[burrina]

Nz is for handling Nulls so you don't get an error. Her is a link that may be helpful. http://office.microsoft.com/en-us/ac...001228890.aspx

[June7]

You say the first expression is in DefaultValue property? DefaultValue only triggers with a new record. A record is initiated with first input to any control and that will trigger the DefaultValue. Also, DefaultValue is intended to save value to record. Do you really want to save that info into record?

Use the Nz expression in ControlSource or in query.

[angie]

Yes I do want to save it ( it's the billing address for creating a invoice for a service call)
i am creating a new record when I create a new invoice.
If the owners address is empty than I want to bill the customer.

[June7]

What is this form behavior? Is this a form/subform? How are the owner and customer known to the form?

I doubt DefaultValue will help with this requirement. Saving the full address string will probably require code (I use only VBA, not macros).

[angie]

Nz Angies.zip

I am attaching a db to try and explain. when you open it and go to customer form there is two records created. on the first record if you select "service call" button up top it will open the service calls for that customer, then select create invoice. the top box should have the billing address for the customer since no owner is selected, but nothing is showing.

if you go to the second customer record and do the same you will see that the owner address shows in the box because the owner was selected in the customer form.

what I want to do is if a owner is selected then that address information will show if not it will be the customer default information for billing. (in my old DB it worked fine but I am reworking a few things to clean the DB up a little and now I am having problems)

I have tried several different things to fix the above but so far no luck . Any suggestions would help thanks Angie

[June7]

Those steps open Workorders form. The 'top box' is a combobox bound to Bill To field. I don't see any place for address display on Workorders form. How is this supposed to supply address info to Customer form?

Seems to me you want that IIf expression in textbox on Customer form.

Then if you want to save the full address string, will need code in some event, perhaps form BeforeUpdate. What record do you want to save the string to?

[angie]

When you open the property sheet for the bill to. The default value is entered. I have the bill to as a combo box because sometime I have to bill a insurance company or warranty company that is Listed in the combo box. But the bill to is always the customer information unless there is a owner . I will try to make a bill to field in the main form customer.

[June7]

I now see there are Bill To fields in Customers and Workorders tables. And also see the DefaultValue setting in the combobox. Problem is the Nz references the textbox with concatenated string. That textbox is never null nor empty string because of the concatenation with spaces and Chr(13) & Chr(10). Use IIf() expression instead and reference the CompID combobox.

IIf(Not IsNull([Forms]![Customer]![CompID]), [Forms]![Customer]![Owner Address], ...)

[angie]

Is this how I will need to write it?

=IIF(not isnull([forms]![customer]![compid]),[forms]![customer]![company address],[Forms]![customer]![firstname] & " " & [Forms]![customer]![lastname] & Chr(13) & Chr(10) & [Forms]![customer]![street number] & " " & [Forms]![customer]![address] & Chr(13) & Chr(10) & [Forms]![customer]![City] & " " & [Forms]![customer]![state] & " " & [Forms]![customer]![zip])

[angie]

I tried the above but get error. The expression you entered contains invalid syntax

[June7]

This works:

IIf(Not IsNull([Forms]![Customer]![CompID]),[Forms]![Customer]![Owner Address],[Forms]![customer]![firstname] & " " & [Forms]![customer]![lastname] & Chr(13) & Chr(10) & [Forms]![customer]![street number] & " " & [Forms]![customer]![address] & Chr(13) & Chr(10) & [Forms]![customer]![City] & " " & [Forms]![customer]![state] & " " & [Forms]![customer]![zip])

[angie]

Thank you again June7. You are great. It worked wonderful. Thanks Angie