IIf statement and a new line 1

[jamespeters01]

Hello,

I have an IIf statement in one of my reports which seems to work fine. I would like to instead of the fields listing across the page horizontally, jump to a new line after each record is diplayed.


=IIf(IsNull([Customer add 1]),[Customer add 5] & " " & [Customer add 6],[Customer add 1] & " " & [Customer add 2] & " " & [Customer add 3] & " " & [Customer add 4]) & " " & [Customer add 5] & " " & [Customer add 6]

This is my IIf statement

Any help would be appreciated !

 

[CosmoKramer]

Check out Access help for the vbCrLf constant. Just include it in your string wherever you want to jump to a new line. Something like:

=IIf(IsNull([Customer add 1]),[Customer add 5] & " " & [Customer add 6],[Customer add 1]

& vbCrLf &

[Customer add 2] & " " & [Customer add 3].....

 

[VicM]

jp

[Customer add 5] & chr(10) & chr(13) & [Customer add 6] ... etc.

HTH,

Vic

 

[jamespeters01]

Is the vbCrLf constant an access 2000 thing ?? Cant find it in the Access 97 help anywhere

 

[CosmoKramer]

In A97, look under the Find tab in help.....

 

[jamespeters01]

Can you have more than one record as Null in the same IIf statement??

 

[CosmoKramer]

Do you mean more than one field??

 

[jamespeters01]

Yes, sorry. More than one field. It was a long day !

 

[VicM]

Cosmo

And all this time I've been typing all those characters: Chr(10) & Chr(13).

Never remebered about using vbCrLf. Must have been a non-attention day in my continuing education of Access' functionality.

Thanks,

Vic

P.S. And to me that is worth a star for you!

 

[CosmoKramer]

Multiple fields in the IIf would look something like this:

=IIf([Customer add 1] Is Null And [Customer add 2] Is Null, truestuff, falsestuff)

Is this what you are looking for?

 

[jamespeters01]

Sortof, I have three different combinations that I need to include. How can I like these 3 IIf statments together?

In plain English.....If Customer add 2 is null I need fields 1,5 and 6 printed.

If cust add 3 is null I need fields 1,2,5 and 6.

and finally if Cust. add 4 is null, I need fields 1,2,3,5 and 6 printed.

All this to be in one statment.

Is this possible ??

 

[CosmoKramer]

OK, I hope I'm on the right track...Using the vbCrLf constant, do you want to simulate a mailing label, in other words stack the controls vertically?? If so, try something like this:

Add an unbound text box to your detail section to hold the formatted address fields(txtCustAddress). Make it wide enough to handle the longest field and set its Can Grow and Can Shrink properties to Yes.

In the On Format event of your detail section, put code like this to populate that text box you just added:

If IsNull([Customer add 2]) Then
   Me.txtCustAddress = Me.[Customer add 1] & vbCrLf & Me.[Customer add 5] & vbCrLf & Me.[Customer add 6]
Else
   If IsNull([Customer add 3]) Then
   Me.txtCustAddress = Me.[Customer add 1] & vbCrLf & Me.[Customer add 2] & vbCrLf & Me.[Customer add 5] & vbCrLf & Me.[Customer add 6]
Else
   If IsNull([Customer add 4]) Then
   Me.txtCustAddress = Me.[Customer add 1] & vbCrLf & Me.[Customer add 2] & vbCrLf & Me.[Customer add 3] & vbCrLf Me.[Customer add 5] & vbCrLf & Me.[Customer add 6]
End If
End If
End If

This should work if you specifically want certain fields to print when others are null. Another option if you simply want to suppress any blank fields when they are stacked vertically, is to use the Trim function in the control source....

 

[jamespeters01]

I get a run time error 2465.

Can't find field Customer add 2.


Does there need to be anything in the control source for the new text box ??

 

[CosmoKramer]

No, you don't need anything in the control source....

Can you send me a zipped copy of the db?

sw3540@yahoo.com

 

[CosmoKramer]

If you can't send it me, then please check this:

The controls referenced in the above statements should be the names of the text boxes, not the control source names. That's probably why you're getting the 2465 error....

Let me know....

 

[jamespeters01]

I thought it did get sent...oh well !

The controls should be the names of the text boxes from the original form ?? They are the names of the fields from the table.

 

[CosmoKramer]

Could you try sending it again??

sw3540@yahoo.com

 

[jamespeters01]

Am, at home now. Will have to do it when I get into work tomorrow.

The controls are the exact names from the fields from the table. Is that ok ??

 

[Wocca]

With the immediate if statements you can nest them i.e.
"
=Iif([Customer add 2] is null, Fields 1,5 and 6 ,Iif([cust add 3] is null,fields 1,2,5 and 6,iif([Cust add 4] is null,fields 1,2,3,5 and 6,)))
"

Hope this helps. Regards
Warwick

 

[CosmoKramer]

Then try replacing the Me. keyword everywhere with the Forms! object reference. Something like:

If IsNull(Forms!YourFormName![Customer add 2]) Then
.....