selecting or ommitting fields in report

[Cosette]

Happy new Year all,

I have a report with the following fields:
[title][firstname][lastname][secondtitle][secondfirstname][secondlastname]

I have an unbound text box that reads:
=[title]&" "&[firstname]&" "&[lastname]&"_ and "&[secondtitle]&" "[secondfirstname]&" "&[secondlastname]
I have nameed this box txtCustomerName

Now, on the on format event, I wrote an IF statement to get rid of the second info if it is null;

If me.secondlastname.value Isnull THEN
Me.txtCustomername.value =[title]&" "&[firstname]&" "&_[lastname]&"

Needless to say, this is not working. I don't know what is the correct syntax for expressing only the first customer. Any help is greatly apreciated.

David

 

[dhookom]

I don't think you need code.
=[title]&" "&[firstname]&" "&[lastname]+ "_ and " &[secondtitle]&" "[secondfirstname]&" "&[secondlastname]
Notice the "+" rather than "&".

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Cosette]

Thank you Duane,

I tried the code but I have a problem. When there is no second name, the "and" still appears. Did you mean to write undersocre in front of 'and'? I tried with and without and I get '_and' or 'and'.

How is your grandson?

David

 

[dhookom]

You are the one that had the underscore. I should have placed a + on the other side of the And. Try this:
=[title]&" "&[firstname]&" "&[lastname] + "_ and " +[secondtitle]&" "[secondfirstname]&" "&[secondlastname]

If [SecondTitle] is null then the "_ and" will not show.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Cosette]

Duane,

I apologize for taking so long to reply, and for not having noticed the underscore in my original post. Thank you very much for the tip, it is very helpful and it works.

David

 

[Cosette]

Good evening all,

In a report, I wish a txtbox and a label to be ommited if another texct box is blank. What is the correct syntax to indicate no data for text.

I have tried:

Me.txtbox1.value is null or Me.txtbox1.value ="" but neither seem to work. Is value for string accurate? Is that where the problem is?

Thanks for the help.

David

 

[dhookom]

You can run code in the section's On Format event
If Len(Me.txtbox1 & "") = 0 Then
Me.txtOther.Visible = False
Me.lblOther.Visible = False
Else
Me.txtOther.Visible = True
Me.lblOther.Visible = True
End If

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Cosette]

Thank you Duane. That was the missing link. So I guess when one deals with a string, one must define the size of the string? Is that why isnull won't work here.

Thanks again

 

[dhookom]

If the field is truly Null, you should be able to use:

If IsNull(Me.txtbox1) Then
   Me.txtOther.Visible = False
   Me.lblOther.Visible = False
 Else
   Me.txtOther.Visible = True
   Me.lblOther.Visible = True
End If

or maybe:

   Me.txtOther.Visible = Not IsNull(Me.txtBox1)
   Me.lblOther.Visible = Not IsNull(Me.txtBox1)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Cosette]

Thank you Duane, I had the format wrong. It now works thanbks to your help.

David