Handling of text fields in reports (Name & Address) 2

[davcom1]

Firstly, hi to all - just joined!

I'm attempting to create a fairly simple database to manage quotations sent out to enquirers.

I'm still new to Access and am having problems.

I have fields such as [Title] [FirstName] [LastName] [CompanyName] [Address1] [Address2] [County] [PostTown] [Postcode]...and so on.

I want to produce a quote letter using a REPORT but I have no idea how to handle the text. I want the report to create a neat address box that does not leave blank lines where there is a NULL value. If I try to group all of the fields I get a mess such as below...

Mr Fred Bloggs,
32 Nowhere Street, , Small Town,
, TG1 3DW

I tried to put [Address1] & ", " & [Address2] ... but if [Address2] is NULL then I get unwanted spaces and commas.

I just don't understand IF or is it IIF?! I could handle this with old fashioned 8-bit BASICs using various string handlers but not with Access!

If there's a simple answer or site that can help, I'd appreciate any info otherwise I'll have to look for a good book - again any advice is appreciated!

Thanks in advance

Davcom1

 

[JimHorton]

davcom,
IIF is quite usefull, as is NZ(), but NZ() isn't quite right for what you want. Format of iif:
Part1: Part2: Part3:
iif(Thing1 = Thing2, <--If Part1 is true do this , If Part1 is false do this)

Here's an example of IIF:

iif(isnull([address1]),&quot;&quot;,[address1]) & iif(isnull([address2]),&quot;&quot;,&quot;, &quot; & [address2])

Notice that the third section if iif--the false part, has a comma as well as address 2. Now, this of course assumes that address1 is Not null, which is usually a safe assumption. But, to really protect yourself from that (and you can use this logic for other 'grouped' text fields as well:
iif(isnull([address1]),&quot;&quot;,[address1]) & iif(isnull([address2]),&quot;&quot;, iif(isnull([address1]),&quot;&quot;,&quot;, &quot & [address2])

The above might be hard to decipher, but it works.
--Jim

 

[PaulF]

try something like this, create a Label on the Report, add TextBoxes for each of the fields to be shown in the Label, but set their Visible Property To False. Add this code to the Report's Detail On Format Event, then open the report and see if this is similar to what you are after.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
lblAddress.Caption = Trim(Me.[Title] & &quot; &quot; & Me.[FirstName] & &quot; &quot; & Me.[LastName]) _
& vbCrLf & Me.[Address1] & &quot;, &quot; & IIf(Len(Me.[Address2]) > 0, Me.[Address2] & &quot;, &quot;, &quot;&quot _
& vbCrLf & Me.[PostTown] & &quot;, &quot; & Me.[PostCode]

End Sub

 

[davcom1]

Big THANKS to Jim & Paul for that info.

I now have a better understanding of how to manipulate the data (Jim) and have my addresses nice n' tidy in a label (Paul)... I didn't realise it was possible to do this with labels! The vbCrLf command is very handy!

Next time either of you are passing thru' Plymouth, UK I'll buy you a drink!