UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Module Returns Error When Not All Data Is Supplied, Access 2016    
 
   
dflak
post Dec 30 2017, 11:03 PM
Post#1


Utter Access VIP
Posts: 6,146
Joined: 22-June 04
From: North Carolina


I have a function that concatenates all the parts of an address. I tried using this function in a report and get the #Type! error, whatever that is and when I try to use it in a query, I get the very informative #Error error. The function works ONLY when every argument is not null, but produces the errors when ANY of the arguments are null. When I use the test it function it works no matter how many of the optional arguments I supply. What am I missing?
CODE
Public Function MakeAddr(MyName As String, Optional MyLine1 As String, Optional MyLine2 As String, Optional MyCity As String, Optional MyState As String, Optional MyZip As String) As String
Dim MyAddr As String

MyAddr = MyName

If Not IsNull(MyLine1) Then
    MyAddr = MyAddr & Chr(10) & Trim(MyLine1)
End If

If Not IsNull(MyLine2) Then
    MyAddr = MyAddr & Chr(10) & Trim(MyLine2)
End If

If Not IsNull(MyCity) Then
    MyAddr = MyAddr & Chr(10) & Trim(MyCity) & ", " & MyState & " " & MyZip
End If

MakeAddr = Trim(MyAddr)

End Function

Sub testit()
MsgBox MakeAddr("Name", "Line1", "Line2", "City", "State", "Zip")
End Sub

Try running rpt_master or qry_master.
Attached File(s)
Attached File  Contact_Project.zip ( 90.7K )Number of downloads: 4
 

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
RJD
post Dec 31 2017, 12:12 AM
Post#2


UtterAccess VIP
Posts: 8,358
Joined: 25-October 10
From: Gulf South USA


Hi Dan: You really don't need the UDF at all. Just put this in the query ...

addr_name & (NL() + addr_line1) & (NL() + addr_line2) & (NL() + ((addr_city+ ", ") & (addr_state + " ") & addr_zip)) AS AddressBlock

This assumes there will always be a name (otherwise the first line will be null). Otherwise, any other field should handle a null.

Note that I have added a NL() function (stands for New Line) - which you will find in the db revision. This avoids having to enter the two required Chr commands at each place.

Check out the query revision.

...or you could just use this approach in the report itself if you wanted to.

HTH
Joe
Attached File(s)
Attached File  Contact_Project_Rev1.zip ( 65.61K )Number of downloads: 0
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
dflak
post Dec 31 2017, 07:40 PM
Post#3


Utter Access VIP
Posts: 6,146
Joined: 22-June 04
From: North Carolina


I'll try that. What I don't want is blank rows when there is no data. The above doesn't look like it will do that. I could probably nest a bunch of iifs but I thought VB would actually be easier.

I'm still interested in why it works when all the data is provided. And works when the test program calls it - I've passed a variable number of arguments and null string arguments to the test program. So why does it fail when I pass null arguments from Access?

This is a prelude to a much bigger requirement. The database shows that I have a number of subordinate tables linked to the main table. For example, one of these tales is phones. People may have none or many phones associated with the main record. I have to develop another function that will loop through the records associated in the subordinate table that are linked to the main table and concatenate them with linefeeds or return the null string if there are no subordinate records.

My ultimate objective is to use these functions in a query that yields: name+address|names|phones|comments and this will be the source for an MS-Word Mailmerge which produces a telephone-like directory in two columns with first entry on the page and last entry on the page at the top of each sheet. I have something similar to this with a much simpler database that assumes that people can have a maximum of three phones and provide this as fodder to the Mailmerge: name|phone1|phone2|phone3.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
RJD
post Dec 31 2017, 08:52 PM
Post#4


UtterAccess VIP
Posts: 8,358
Joined: 25-October 10
From: Gulf South USA


Please do test my approach. It reliably eliminates lines when the component is null. No blank lines. I use this approach routinely, in queries, reports, directories, labels, mail merge, etc.

HTH
Joe

Oh, and Happy New Year!

from phone

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
dflak
post Dec 31 2017, 10:06 PM
Post#5


Utter Access VIP
Posts: 6,146
Joined: 22-June 04
From: North Carolina


I figured it out.

CODE
Public Function MakeAddr(MyName As String, Optional MyLine1 As String, Optional MyLine2 As String, Optional MyCity As String, Optional MyState As String, Optional MyZip As String) As String

Doesn't work because NULL is not the same thing as the NULL string. VBA does not recognize NULL as a string - I'm not sure what kind of data type NULL is, but Variant seems to cover it. Most of my work is done in Excel, I can't say if I've seen this happen there.

Changing the argument type to Variant makes it work. I'm glad I figured this out since I will be using similar logic in other functions

So that's one less thing to worry about this year.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
RJD
post Jan 1 2018, 11:13 AM
Post#6


UtterAccess VIP
Posts: 8,358
Joined: 25-October 10
From: Gulf South USA


Hi Dan: Glad to hear you got that solved. I would, however, encourage you to at least test the approach I suggested. It avoids using a UDF to get the name/address block, and works without flaw in queries, forms, and reports in all of my applications to which it is applied. Using the + together with the NL() function eliminates the blank lines when the subject field is Null. And you can apply this to any of the components of any block of information you encounter.

Continued success with your application ... and have a very Happy New Year! fundrink.gif

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st June 2018 - 07:36 AM