Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 6th, 2005, 08:31 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: , , United Kingdom.
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default Concatenation of fields

Hi all,

I have a table which holds lines of an address, 5 in total. These lines are stored in seperate fileds, Address1, Address2, Address3 Address4 and Address5. I would like to concatenate these fields to display in a report so that the address appears as a block. e.g.

House Number and Street
An Area
Town/City
A County
PostCode


I can imagine you saying why not use 5 text controls bound to each field. Well, the problem is not all address lines may have a value but the last line will always have a value so i would end up with a gap between say line 3 and line 5 e.g.

House Number and Street
An Area
A Town

PostCode


This is not the desired result i want on my report. I would like the missing line to be removed like below.

House Number and Street
An Area
A Town
PostCode


I have tried making a composite filed in my query that joins these 5 fileds together after checking for values in the fileds using chr(13) between each field, but when the report runs it just shows the sqaures between the different fields. It doesnt interpret the Chr value as a Carriage return. Does anybody have a way to get around this I am trying to avoid coding the report.

Jon
 
Old April 6th, 2005, 01:53 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Code the report.

mmcdonal
 
Old April 6th, 2005, 01:57 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

By that I mean, put code in the Detail section's On Format event that looks for values in each of these fields, and then puts them in text boxes as required.

Check for:
House Number and Street
An Area
Town/City
A County
PostCode

Then have 5 text boxes.
If any of the field values are Null, then skip that data but don't skip the box.
This will get you what you want, with one caveat:

House Number and Street
An Area
Null
Null
PostCode

May result in:

House Number and Street
An Area
PostCode
...
...

So you would have empty (invisible) text boxes when there was no data. But this shouldn't effect your formatting.

I am sure there are posters who can create the text boxes with code as needed and drop these two lines in this example, but that is even more code.


mmcdonal
 
Old April 6th, 2005, 02:19 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry to keep posting...

As a practical matter, develop your report, and then make these 5 fields invisble, and then relate all your coding to these 5 invisible fields.

This way you don't have to make any calls to tables to check for the values, just to the current record on the report.

You are going to have to build the stack by checking for Nulls, though. A little time consuming but can be done.



mmcdonal
 
Old April 6th, 2005, 02:27 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry to keep posting again.

This script works to do what you want in a single text box. Perhaps in can be adapted with modifications. You need to check for null in each field and then assign either "" or the actual value to a variable. I preloaded the variables assuming Null values in two fields.

'=====

stHouse = "123 Any Street"
stArea = ""
stCity = ""
stCounty = "Howard"
stZip = "21043"

If stHouse <> "" Then
    stMessage = stHouse
End If

If stArea <> "" Then
    stMessage = stMessage & VbCrLf & stArea
End If

If stCity <> "" Then
    stMessage = stMessage & VbCrLf & stCity
End If

If stCounty <> "" Then
    stMessage = stMessage & VbCrLf & stCounty
End If

If stZip <> "" Then
    stMessage = stMessage & VbCrLf & stZip
End If

WScript.Echo stMessage
'=====

The output was:

123 Any Street
Howard
21043

HTH


mmcdonal
 
Old April 6th, 2005, 02:28 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Shoot, this also assumes that stHouse will never be null.

Not enough work for me here this afternoon. I hope you can use some of this.

mmcdonal
 
Old April 7th, 2005, 01:55 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: , , United Kingdom.
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for that,

I used -

    strAdd1 = Me![cl_Address1]
    strAdd2 = Me![cl_Address2]
    strAdd3 = Me![cl_Address3]
    strAdd4 = Me![cl_Address4]
    strAdd5 = Me![cl_Address5]
    strPostCode = Me![cl_Postcode]

    If strAdd1 <> "" Then
        txtClAddress1 = txtClAddress1 & strAdd1 & vbCrLf
    End If
    If strAdd2 <> "" Then
        txtClAddress1 = txtClAddress1 & strAdd2 & vbCrLf
    End If
    If strAdd3 <> "" Then
        txtClAddress1 = txtClAddress1 & strAdd3 & vbCrLf
    End If
    If strAdd4 <> "" Then
        txtClAddress1 = txtClAddress1 & strAdd4 & vbCrLf
    End If
    If strAdd5 <> "" Then
        txtClAddress1 = txtClAddress1 & strAdd5 & vbCrLf
    End If
    If strPostCode <> "" Then
        txtClAddress1 = txtClAddress1 & strPostCode & vbCrLf
    End If

This does what I want it to. Thanks again for the direction.

Jon
 
Old April 7th, 2005, 03:39 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

If you're putting them into a report in that order and there is no other field next to them, why not just set their Can Shrink properties to YES? In that way, if they're blank, they'll shrink down to nothing. No coding necessary.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old April 8th, 2005, 01:32 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: , , United Kingdom.
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Greg,

The fields are laid out vertically, one under the other. thanks for the input though. I was hoping to add a field into a query that concatenates the fields into one adding a line feed character in between. Looks like I will have to stick with coding the report.

Jon




Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenation question, please savoym VBScript 12 May 5th, 2009 02:30 PM
Performance in String concatenation rushman XSLT 4 September 27th, 2007 11:02 AM
Aggregate Concatenation 12th_Man SQL Language 4 January 10th, 2006 03:26 PM
string concatenation nulogix PHP How-To 1 June 24th, 2004 06:17 AM
string concatenation matt_99 Access VBA 2 January 17th, 2004 09:55 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.