Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    52

    Unanswered: stored functions in Access

    I currently have the function basConference which stores a variable from my search form. How do I allow for a null value? I know how to do it via click method. But I do not know the syntax



    Here's what i Have:

    Function basConference() As String

    basConference = ([Forms]![frmAttendenceSearch]![Conference])

    End Function

  2. #2
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    basConference = NZ(([Forms]![frmAttendenceSearch]![Conference]))

    stores zero in basConference of the conference field is empty.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  3. #3
    Join Date
    Mar 2004
    Posts
    52
    Storing a zero value would be fine, is there a way of storing a null value. or is there a difference.


    The basconference functions stores a string, so I assume a zero value would have no bearing in this application





    Originally posted by jpshay
    basConference = NZ(([Forms]![frmAttendenceSearch]![Conference]))

    stores zero in basConference of the conference field is empty.

  4. #4
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    The Nz function will let you store whatever you would like in the event that it returns a null.

    basConference = NZ(([Forms]![frmAttendenceSearch]![Conference]),"I am empty")

    Check out the help for Nz(). Its a handy little guy.

    Regarding nulls and zeros, yes there is a difference. Can I elaborate? Not very intelligently. A null is no value. Zero is an integer. A zero length string, "", is nothing, however it is 'more' than null.

    Perhaps this will help you.
    if isnull([Forms]![frmAttendenceSearch]![Conference]) then
      basConference = Null
    else
      basConference = ([Forms]![frmAttendenceSearch]![Conference])
    end if

    Resubmit for others to answer if this isn't helpful.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If you are trying to pass a null value to a function then you will need to define the parameter as a variant.

    In your example:

    Function basConference(varValue as Variant) As String

    basConference = Nz(varvalue,"")

    End Function

    This will let you pass in a null value and send back an empty string.

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    ax2ron,

    the basConference function is defined as


    function basConference() as String


    Strings, by definition, cannot contain NULLs.


    if you want it to permit NULLs, change the definition to


    function basConference() as Variant


    a Variant, by definition, can contain NULLs.

  7. #7
    Join Date
    Mar 2004
    Posts
    52
    Didn't know that, thanks for the tip.

    I have a question for you, if you delcare a null value. Will it effect a loop statment? It my case it is. When I leave one of my variables null, it fails to execute the movefirst function as detailed in this code:



    Private Sub Command14_Click()

    Dim MyDb As Database
    Dim MyRS As Recordset
    Dim strSQL As String
    Dim Address As String


    strSQL = "SELECT email FROM qryAttendance WHERE [type of conference]='" & basConference() & "' and [Times Attended]>=" & basAttendance() & ";"

    Debug.Print strSQL

    Set MyDb = CurrentDb
    Set MyRS = MyDb.OpenRecordset(strSQL)

    MyRS.MoveFirst

    Do Until MyRS.EOF
    Email_who = MyRS.Fields(0) & ";" & Email_who
    MyRS.MoveNext
    Loop

    Email_who = Left(Email_who, Len(Email_who) - 1)

    Set MyolApp = CreateObject("Outlook.Application")
    Set MyItem = MyolApp.CreateItem(olMailItem)
    Set MyRecipient = MyItem.Recipients.add(Email_who)

    MyItem.Subject = "Your Title"

    Msg = Chr(13) & "Hi," & Chr(13) & Chr(13)

    MyItem.Body = "This is a test" & Chr(13) & Chr(13) & "Thanks"
    MyItem.Display

    MyRS.Close

    End Sub








    Originally posted by PracticalProgram
    ax2ron,

    the basConference function is defined as


    function basConference() as String


    Strings, by definition, cannot contain NULLs.


    if you want it to permit NULLs, change the definition to


    function basConference() as Variant


    a Variant, by definition, can contain NULLs.

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I think the problem is with the SQL. No two nulls are equal. If you have are looking for those records where the conference is null then you can't use "=". You have to use "Is Null" instead. So you are going to have to check whether or not eaither is null and use either "=" or "Is Null" depending on the situation. This might help

    Dim strWhere as string

    strWhere = IIf(IsNull(basConference), "[type of conference] Is Null", [type of conference]='" & basConference() & "'")

    strWhere = strWhere & "AND " & IIf(IsNull(basAttendance),"[Times Attended] Is Null",[Times Attended]>=" & basAttendance())

    strSQL = "SELECT email FROM qryAttendance WHERE " & strWhere

  9. #9
    Join Date
    Mar 2004
    Posts
    52
    makes sense, thanks for the tip.

    I inserted the code, but I assume it contains a punctual error. Being new to VB, i am not aware of the slight nuiances of VB.

    ************************************************** *********
    strWhere = IIf(IsNull(basConference), "[type of conference] Is Null", [type of conference]='" & basConference() & "'")
    ************************************************** *********


    Originally posted by DCKunkle
    I think the problem is with the SQL. No two nulls are equal. If you have are looking for those records where the conference is null then you can't use "=". You have to use "Is Null" instead. So you are going to have to check whether or not eaither is null and use either "=" or "Is Null" depending on the situation. This might help

    Dim strWhere as string

    strWhere = IIf(IsNull(basConference), "[type of conference] Is Null", [type of conference]='" & basConference() & "'")

    strWhere = strWhere & "AND " & IIf(IsNull(basAttendance),"[Times Attended] Is Null",[Times Attended]>=" & basAttendance())

    strSQL = "SELECT email FROM qryAttendance WHERE " & strWhere

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •