Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Handiling null values in a Function

Posted on 2012-08-14
8
Medium Priority
?
526 Views
Last Modified: 2012-08-19
the following code is used to concatenate the single names into a complied list of names - the problem I am having is when there is not a name for one of the many records that are being processed.
Since this function is being used within a query I need to determine the Null value before the function is called - correct?

K


Function GetgMtgAttendee(nCID As Integer) As String

Dim strSQL As String
Dim curDB As DAO.Database
Dim rs As DAO.Recordset
Dim MyString As String

   On Error GoTo GetgMtgAttendee_Error

Set curDB = CurrentDb()
    strSQL = "SELECT MtgRecID, BNAME(AttendeeName) as Name" & _
        " FROM tblMtgAttendees" & " WHERE MtgRecID =" & nCID & ""
    Set rs = curDB.OpenRecordset(strSQL)
    Do Until rs.EOF = True
        If IsNull(rs.Fields("name")) Then
           GoTo cont:
        Else
         MyString = rs.Fields("Name") & vbCrLf & MyString
       End If
cont:
        rs.MoveNext
    Loop
        MyString = Left(MyString, Len(MyString))
   Debug.Print MyString
       
        GetgMtgAttendee = MyString

   On Error GoTo 0
   Exit Function

GetgMtgAttendee_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure GetgMtgAttendee of Module modFunctions"
End Function

Open in new window

0
Comment
Question by:Karen Schaefer
  • 6
  • 2
8 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38293944
Use the Nz(value, value if null) function

Nz(5, 0) returns a 5
Nz(NULL, 0) returns a 0
Nz(NULL, "") returns an empty string ""
0
 

Author Comment

by:Karen Schaefer
ID: 38293981
sorry little confuse where should I place this code?

K
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38294000
Around anything you want to test for NULLs.  For example...

IF Nz(nCID, 0) = 0 then
   'Likely a null, so don't run the rest of the funciton.
Else
   'The VBA you have above goes here.
End if
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:Karen Schaefer
ID: 38294014
sorry buy CID will not be null - it is the attendeeName may be null.

the relationship is 1 mtgRecd - Many Attendees - but if the user does not select any attendees I need to be able to let the code continue if there aren't any attendees assigned to a meeting.

K
0
 

Author Comment

by:Karen Schaefer
ID: 38294021
fyi, here is the code for the BNAME function:

Function BName(ID As Long)
On Error GoTo BName_Error

    BName = DLookup("LName & ', '+FName", _
        "tblContactInfo", _
        "ContactRecID = " & ID)
Debug.Print BName

    Exit Function

BName_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in" _
        & " function BName of Module modFunctions"
End Function

Open in new window

0
 

Author Comment

by:Karen Schaefer
ID: 38294074
ok not sure if the null is the issue - I am getting Data type mismatch.

I have various approaches w/o success.

k

Here is my latest attempt.

' Purpose   : Creates string of Multiple Meeting Attendees to fit in 1 cell for display only
'---------------------------------------------------------------------------------------
'
Function GetgMtgAttendee(nCID As Integer) As String

Dim strSQL As String
Dim curDB As DAO.Database
Dim rs As DAO.Recordset
Dim MyString As String
Dim n As Long

   On Error GoTo GetgMtgAttendee_Error

Set curDB = CurrentDb()
    strSQL = "SELECT MtgRecID, BNAME(AttendeeName) as Name" & _
        " FROM tblMtgAttendees" & " WHERE MtgRecID =" & nCID & ""
    Set rs = curDB.OpenRecordset(strSQL)

    Do Until rs.EOF = True
        n = DCount("AttendeeName", "tblMtgAttendees", MtgRecID = " & nCID")

        If n = 0 Then
          Call _
              MsgBox("Attendee Name is missing, please return to the Meeting detail tab to add an Attendee's Name.", _
              vbExclamation, "Data Missing")
            Exit Function
        Else
            MyString = rs.Fields("Name") & vbCrLf & MyString
       End If
        rs.MoveNext
    Loop
        MyString = Left(MyString, Len(MyString))
       
        GetgMtgAttendee = MyString

   On Error GoTo 0:

   Exit Function

GetgMtgAttendee_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure GetgMtgAttendee of Module modFunctions"
End Function

Open in new window

0
 

Accepted Solution

by:
Karen Schaefer earned 0 total points
ID: 38294159
solution found:

Modified query that called the GetgMtgAttendee function to included inthe sql statement a GetgMtgAttendee(NZ(fieldname))

thanks for the input.
0
 

Author Closing Comment

by:Karen Schaefer
ID: 38309098
figure it out on my own.

thanks.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question