Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have an append query to a temp table which filter postal codes based on Clients being Active or Inactive. I am recording this true/false value to a text box on a form then passing the value to the parameter in the query. The choice of value is determined by a three-value option group 1 = false, 2, = true, 3 = either true or false.

    I use a function to return the value needed to the parameter. If I select options 1 or 2 the parameter is passed properly. If I select option 3, I'm only getting one value.

    Clearly the function is not passing information that the parameter can understand.
    If, in my function, Case 1 = false and case 2 = true how do I pass the value case 3 = True Or False

    I suspect one problem is that my function and the returned value is boolean but I need three possibilities, not two. Here's the function. I'm writing in a hurry, so I can post back if there is still insufficient information. (PS I know strFilter is a goofy name for a Boolean. I adapted it from another use where it was a string. The use of null for case three was a failed attempt to get around this.)

    Private Function FilterName(intVal As Integer) As Boolean

    intVal = Me.OptAct.Value

    Dim strFilter As Boolean

    Select Case intVal
    Case 1
    Let strFilter = False
    Case 2
    Let strFilter = True
    Case 3
    Let strFilter = Null
    End Select

    FilterName = strFilter

    End Function

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I think you answered your own question.
    A boolean value by default is either TRUE or FALSE. Not NULL.
    You are using an Integer with 3 values.
    Either change the parameter to be an integer and adjust query.
    Realistically you need the filter that gets used by the query to only have a filter if true or false and no filter otherwise.
    The function is boolean so it cannot retyrn a NULL.
    What is the query that receives the function result.
    Andrew

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by AndrewKKWalker View Post
    I think you answered your own question.
    A boolean value by default is either TRUE or FALSE. Not NULL.
    You are using an Integer with 3 values.
    Either change the parameter to be an integer and adjust query.
    Realistically you need the filter that gets used by the query to only have a filter if true or false and no filter otherwise.
    The function is boolean so it cannot retyrn a NULL.
    What is the query that receives the function result.
    I was going at it from the wrong direction. Rather than trying to use a saved query and pass "True or False" to it as a parameter, I am running the query in VBA with Docmd.RunSQL and appending the appropriate WHERE condition via my function.
    Thanks for the reply.

Posting Permissions

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