blank fields 2

[aaronjonmartin]

Probably quite an easy fix but im not sure about it.

I have a report that displays address information from a table, however not all fields always contain text. Is there a way i can set the report not to display the field if it doesnt contain any data?

Thanks in advance

Aaron

"It's so much easier to suggest solutions when you don't know too much about the problem."
Malcolm Forbes (1919 - 1990)

 

[jdegeorge]

If there's no data in the field, what's there to display...it will be blank any way.

Or, are you stacking the address fields one atop the other, and if one is blank you want it to disappear?

If so, you can create an unbound field and put something like this as the source. I'm assuming that STREET will always be populated, and the other fields will be variable. Just insert your field names as necessary.

=[STREET]+Chr(13)+Chr(10) & IIf([ADDRESS1]="","",[ADDRESS1])+Chr(13)+Chr(10) & IIf([ADDRESS2]="","",[ADDRESS2])+Chr(13)+Chr(10) & IIf([ADDRESS3]="","",[ADDRESS3])+Chr(13)+Chr(10) & IIf([ADDRESS4]="","",[ADDRESS4])

Hope this helps.

Jim DeGeorge

 

[dhookom]

Set the controls to Can Shrink: Yes. Jim's solution might not work if the fields are Null since "" is not the same as Null. Jim's mixed use of "+" and "&" might provide the correct results. I would remove all the IIf() stuff and just use the field names.

Duane
MS Access MVP

 

[jdegeorge]

Believe it or not, I never noticed that I mixed "+" and "&", but this works 100% every time. And, yest, set the control to can shrink "yes".

Jim DeGeorge

 

[aaronjonmartin]

Guys, thanks for the information

Jim, i am stacking the fields on top of one another and when i set the fields to can shrink it automatically removed the gaps made by empty/unused fields. So thanks.

Additionally i have 3 fields all on the first line (title, firstname, surname) which i obviously cant judge how small or large to set the field sizes. For example if the persons name is ray it will need a smaller field size than if the persons name is william. The problem is that if i set the fields up and the name is short (like ray) the spacing between the firstname and surname is wrong. I have tried setting the fields small and setting them to can grow but they are growing down onto the next line not along as i would like. Any ideas with this guys?

Thanks
aaron

"It's so much easier to suggest solutions when you don't know too much about the problem."
Malcolm Forbes (1919 - 1990)

 

[jdegeorge]

Try this...create an unbound field with this source:

=Trim([Title]) & " " & Trim([Firstname]) & " " & Trim([surname])

This will work. And, thanks for the star.

Jim DeGeorge

 

[aaronjonmartin]

Jim,

Thanks again for your post it worked. However for title i have it set as a combo box so you can select the title from the combo box (either Mr, Mrs, Miss, etc). So when i use your code the first name and surname come out fine but the title is the ID number of whatever title i chose so for example if i choose Mr in the combo drop down then enter Aaron in firstname and Martin in surname the report (using your code) shows 1 Aaron Martin. Any ideas on how i can get around this?

Thanks again

Aaron

"It's so much easier to suggest solutions when you don't know too much about the problem."
Malcolm Forbes (1919 - 1990)

 

[dhookom]

Aaron,
You will need to add the table containing the titles in the record source of the report. I use autonumbers as primary keys in tables most of the time. I do think it is a bit of over-kill for titles like Mr., Mrs.,... I would just use the actual title in the combo box.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[aaronjonmartin]

Thanks all again for your posts you have been most helpful. Dhookom, i have decided to leave the title combo out of the trim code (as posted by Jim) as it only contains 3 values (Mr, Mrs, Miss) and therefore is pretty much the same size all the time. I have set this up and it looks good so im very happy. Thanks again guys.

Aaron

"It's so much easier to suggest solutions when you don't know too much about the problem."
Malcolm Forbes (1919 - 1990)

 

[dhookom]

If the values are 1,2,3 you could use:
=Choose([Title],"Mr.","Mrs.","Miss&quot & " " & Trim([Firstname]) & " " & Trim([surname])


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jdegeorge]

Glad to have helped. Thanks again for the star.

Jim DeGeorge