Newbie problem with Forms and Functions

[Paxton]

Hi all,
Access 2000: I've created a custom function to display an address on a
form, suppressing blank lines. The code for the function is as
follows:

Function FormatAddress(line1 As String, line2 As String, line3 As
String, line4 As String, line5 As String, line6 As String) As String
Dim strOut As String

If Len(line1) > 0 Then strOut = line1 & vbCrLf
If Len(line2) > 0 Then strOut = strOut & line2 & vbCrLf
If Len(line3) > 0 Then strOut = strOut & line3 & vbCrLf
If Len(line4) > 0 Then strOut = strOut & line4 & vbCrLf
If Len(line5) > 0 Then strOut = strOut & line5 & vbCrLf
If Len(line6) > 0 Then strOut = strOut & line6
FormatAddress = strOut

End Function

I've saved this as module 1 in Modules.

On the form, I have put this as the value in a text box:

=FormatAddress([Address1],[Address2],[Address3],[Town],[CountyState],[Postcode])

but when I run the form, I get #Error where the address should be. Can
anyone see where I've gone wrong?

TIA
Paxton

 

[Dirk Goldgar]

Hi all,
Access 2000: I've created a custom function to display an address on a
form, suppressing blank lines. The code for the function is as
follows:

Function FormatAddress(line1 As String, line2 As String, line3 As
String, line4 As String, line5 As String, line6 As String) As String
Dim strOut As String

If Len(line1) > 0 Then strOut = line1 & vbCrLf
If Len(line2) > 0 Then strOut = strOut & line2 & vbCrLf
If Len(line3) > 0 Then strOut = strOut & line3 & vbCrLf
If Len(line4) > 0 Then strOut = strOut & line4 & vbCrLf
If Len(line5) > 0 Then strOut = strOut & line5 & vbCrLf
If Len(line6) > 0 Then strOut = strOut & line6
FormatAddress = strOut

End Function

I've saved this as module 1 in Modules.

On the form, I have put this as the value in a text box:

=FormatAddress([Address1],[Address2],[Address3],[Town],[CountyState],[Po
stcode])

but when I run the form, I get #Error where the address should be.
Can
anyone see where I've gone wrong?

TIA
Paxton

Most likely, some of those fields have values that are Null, not just
zero-length strings, and thus can't be passed to the function's String
parameters. Make your function more flexible by rewriting it like this:

'----- start of revised code -----
Function FormatAddress( _
line1 As Variant, _
line2 As Variant, _
line3 As Variant, _
line4 As Variant, _
line5 As Variant, _
line6 As Variant) _
As String

Dim strOut As String

If Len(line1 & "") > 0 Then strOut = line1 & vbCrLf
If Len(line2 & "") > 0 Then strOut = strOut & line2 & vbCrLf
If Len(line3 & "") > 0 Then strOut = strOut & line3 & vbCrLf
If Len(line4 & "") > 0 Then strOut = strOut & line4 & vbCrLf
If Len(line5 & "") > 0 Then strOut = strOut & line5 & vbCrLf
If Len(line6 & "") > 0 Then strOut = strOut & line6

FormatAddress = strOut

End Function
'----- end of revised code -----

Changing the parameter types to Variant allows Null values to be passed,
and concatenation of each argument with "" converts a Null value to a
zero-length string; i.e., the operation {Null & ""} yields "" as a
result.

 

[Paxton]

I got an error when applying your revised code:

Runtime Error 2427: You entered an expression that has no value

The debugger highlighted this bit of the function:
If Len(line1 & "") > 0 Then

What's all that about?

Paxton

 

[Dirk Goldgar]

I got an error when applying your revised code:

Runtime Error 2427: You entered an expression that has no value

The debugger highlighted this bit of the function:
If Len(line1 & "") > 0 Then

What's all that about?

Paxton

Works fine for me. Where did you call it from? Did the form contain
any records?

 

[Paxton]

Yes, the form contains records. The form is based on a query. When I
run the query, it returns data in the Address fields - not all of them
- only Address1 and Town are required fields.

If I replace the label (I think I said textbox earlier? It's a label)
on the report that contains the expression with separate labels for
each individual field, the report runs fine without any errors. But of
course, there are blank lines in some addresses.

Paxton

 

[Dirk Goldgar]

Yes, the form contains records. The form is based on a query. When I
run the query, it returns data in the Address fields - not all of them
- only Address1 and Town are required fields.

If I replace the label (I think I said textbox earlier? It's a label)
on the report that contains the expression with separate labels for
each individual field, the report runs fine without any errors. But
of course, there are blank lines in some addresses.

I'm confused. Are we talking about a form or a report. Where are you
putting this expression? Labels don't have controlsources, and you
can't put an expression in the caption of a label. If you are calling
this function from code, you'd better post the whole procedure where
you're doing it.

 

[Paxton]

I'm confused. Are we talking about a form or a report. Where are you
putting this expression? Labels don't have controlsources, and you
can't put an expression in the caption of a label. If you are calling
this function from code, you'd better post the whole procedure where
you're doing it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Yes, sorry for causing confusion. I've uncovered the problem. I was
trying to use a Label instead of a Text Box. And just to clarify, I
have now put the expression as the control source on the datatab of a
text box, and it works fine now, and yes, it's on a report not a form.

The real problem is that I'm a complete newbie when it comes to reports
AND forms in Access.

Thanks for your help

Paxton

 

[Dirk Goldgar]

Yes, sorry for causing confusion. I've uncovered the problem. I was
trying to use a Label instead of a Text Box. And just to clarify, I
have now put the expression as the control source on the datatab of a
text box, and it works fine now, and yes, it's on a report not a form.

Ah, good.

The real problem is that I'm a complete newbie when it comes to
reports AND forms in Access.

Don't worry, that won't last. Good luck to you.