Blank Address Fields in Reports 1

[RachelK]

Hi,

I have an sql query :-
This query basically selects, the add1, add2, add3,add4 fields which holds a persons address sometimes they may not have data in one of the fields.

I would like the data to display the address like:-

Add1
Add2
Add3
Add4
Country
Postcode

but if the fields are null I would like the data to move up a line on the report.

I could concatonate the fields in the query by using the & problem with this is the I would like a CRLF (carrige return) so that each address appears on a new line as it is a letter do you know the best way to go about this. Thanks. Rachel

SELECT tblCourseProvider.CPNO, tblCourseProvider.NameLong, tblCourseProvider.NameShort, tblCourseProvider.StreetNo, tblCourseProvider.Postcode, tblCourseProvider.Add1, tblCourseProvider.Add2, tblCourseProvider.Add3, tblCourseProvider.Add4, tblCourseProvider.Country
FROM tblCourseProvider;

 

[hymn]

Have a look at
thread181-740569

Hope this helps
Hymn

 

[randy700]

Typed, not tested.....

Create 6 text boxes - visible = no.
txt1, txt2, txt3, txt4, txt5, txt6

Private Sub DisplayAddress()
if len(Add1) > 0 then
me.txt1.visible = true
me.txt1 = Add1
end if
ShowAdd2
ShowAdd3
ShowAdd4
ShowCountry
ShowPostCode
End Sub

Private Sub ShowAdd2()
if len(add2) = 0 then Exit Sub
if not(me.txt1.visible) then
me.txt1.visible = true
me.txt1 = add2
else
me.txt2.visible = true
me.txt2 = add2
end if
End Sub

Private Sub ShowAdd3()
if len(add3) = 0 then Exit Sub
if not(me.txt1.visible) then
me.txt1.visible = true
me.txt1 = add3
else
if not(me.txt2.visible) then
me.txt2.visible = true
me.txt2 = add3
else
me.txt3.visible = true
me.txt3 = add3
end if
end if
End Sub

Private Sub ShowAdd4()
if len(Add4) = 0 then exit sub
if not(me.txt1.visible) then
me.txt1.visible = true
me.txt1 = add4
else
if not(me.txt2.visible) then
me.txt2.visible = true
me.txt2 = add4
else
if not(me.txt3.visible) then
me.txt3.visible = true
me.txt3 = add4
else
me.txt4.visible = true
me.txt4 = add4
end if
end if
end if
End Sub

Private Sub ShowCountry()
if len(Country) = 0 then exit sub
if not(me.txt1.visible) then
me.txt1.visible = true
me.txt1 = country
else
if not(me.txt2.visible) then
me.txt2.visible = true
me.txt2 = country
else
if not(me.txt3.visible) then
me.txt3.visible = true
me.txt3 = country
else
if not(me.txt4.visible) then
me.txt4.visible = true
me.txt4 = country
else
me.txt5.visible = true
me.txt5 = country
end if
end if
end if
end if
End Sub

Private Sub ShowPostCode()
if len(PostCode) = 0 then exit sub
if not(me.txt1.visible) then
me.txt1.visible = true
me.txt1 = postcode
else
if not(me.txt2.visible) then
me.txt2.visible = true
me.txt2 = postcode
else
if not(me.txt3.visible) then
me.txt3.visible = true
me.txt3 = postcode
else
if not(me.txt4.visible) then
me.txt4.visible = true
me.txt4 = postcode
else
if not(me.txt5.visible) then
me.txt5.visible = true
me.txt5 = postcode
else
me.txt6.visible = true
me.txt6 = postcode
end if
end if
end if
end if
end if
End Sub


Randy

 

[RachelK]

Randy,

I know it has been a while since I visited this problem. I am hoping to resolve it today. Where would I put this event on the report in the form open or load etc. Cheers Rachel.

 

[RachelK]

Hi,

I have decided to keep this simple. I have one unbound text box on the report called. Add

I would like to say that if all has data then Add =
StreetNo with vbCRLF (on each line)
Add1
Add2
Add3
Add4
Country
Postcode

Else if Add1 = "" Then
StreetNo
Add2
Add3
Add4
Country
Postcode

etc.

I have a query in my report and I am using the open event not sure if this is the right one to use.

I might change this to a select case and have 6 different cases. Any help. Cheers Rachel.

 

[randy700]

I would add the code to the forms open event.

Randy