Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2011
    Posts
    11

    Unanswered: Parameter query with Checkboxes does something odd..

    Hi again,

    This one is even simpler than most problems I have with Access 2007 so I hope theres an easy solution!

    I have a table which includes checkboxbox fields and in the design view of this table, the checkbox is formatted to "Yes/No".

    I have parameter queries which refer to this checkbox field in 2 different ways.

    1) The user-entered parameter required refers to a normal text field but there is an AND criteria in the checkbox field simply set to "yes" or "no" (ie, no user-entered parameter). Everything works fine and the query returns the expected records. Good.

    2) in a seperate query, the user-entered parameter required refers to the checkbox (so is asking for y/n input) either on its own or alongside another parameter criteria in another field. The syntax for the query in the checkbox field is:

    like "*" & [Please enter y/n or leave blank to return all records:] & "*"

    So theorically, "y" or "n" should work as input instead of "Yes" or "No", right? However the only input that works is "0" or "-1". I have tried y, n, yes, no, t, f, true and false without success. Leaving blank will work though.

    I just about understand about boolean, numeric and string arguments, but this seems to be set to "yes/no" already and other queries work on "yes" or "no" so im quite stumped.

    Any help would be appreciated - I hope that Ive made clear what I am trying to achieve, please let me know if ive gabbled

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    A so-called Yes/No column actually stores boolean values. Normally it should be 0 (zero) for No/False and 1 (one) for Yes/True. However Access stores Yes/True values as -1 (minus 1) which is coherent with the Basic language (if False = 0 and True = Not False, then True = -1).

    The CheckBox is just a way of displaying the actual value of the column (it could be a ComboBox), while Yes/No is a format (it could be True/False or On/Off). None of these change the actual stored value.
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Posts
    11
    thanks, I understand the logic behid it, but I cant understand why one fixed query will return the correct records based on simple "yes" or "no" values, but when used as a paramter query, entering "yes" or "no" would not work.

    Basically, I dont want the user to have to enter "0" or "-1" in order to get the correct returns.

    Is there a way to avoid this?

  4. #4
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    You could just change the field from yes/no (Boolean) type to a text type, and check for something like:

    Code:
    WHERE ((aTable.[A Field] = "yes") OR (aTable.[A Field] = "y"))
    If you're not sure what booleans are or how to use them properly, that's probably the simplest approach to take instead.
    Looking for the perfect beer...

  5. #5
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    The Jet Engine can interpret (=translate) a set of predefined values (Yes and No are among them in this case) while nothing in the way you formulate the parameter indicates how to translate "y" to -1 and "n" to 0 (the actual expected values). What if the parameter prompt were: "[Veuillez entrer o/n ou laisser en blanc pour retourner tous les enregistrements :]"?

    You could try something like:
    Code:
    Like "*" & IIf([Please enter y/n or leave blank to return all records:]="y";-1;0) & "*"
    Have a nice day!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Another solution would consist in writing a function to translate the input parameter into a boolean value.

    In an independant module:
    Code:
    Public Function ToBoolean(ByVal Value As Variant) As Variant
    
        If IsNull(Value) Then
            ToBoolean = Null
        ElseIf IsNumeric(Value) Then
            If Value = 0 Then ToBoolean = 0 Else ToBoolean = -1
        Else
            Select Case UCase(Value)
                Case "N", "NO", "F", "FALSE", "OFF"
                    ToBoolean = 0
                Case "Y", "YES", "T", "TRUE", "ON"
                    ToBoolean = -1
                Case Else
                    ToBoolean = Null
            End Select
        End If
        
    End Function
    Then:
    Code:
    Like "*" & ToBoolean([Please enter y/n or leave blank to return all records:]) & "*"
    Have a nice day!

  7. #7
    Join Date
    Aug 2011
    Posts
    11
    Thanks for all your help guys, I'm sure I can find a solution that works for me in amongst all that golden knowledge!

    Consider my problem solved for the purposes of this thread but I am still intrigued... Call it knowledge expansion...


    The Jet Engine can interpret (=translate) a set of predefined values (Yes and No are among them in this case) while nothing in the way you formulate the parameter indicates how to translate "y" to -1 and "n" to 0 (the actual expected values). What if the parameter prompt were: "[Veuillez entrer o/n ou laisser en blanc pour retourner tous les enregistrements :]"?
    ^^ well, the value choices would be "Oui", "Vrai" or "moins un"


    I had hoped that in using the Like argument with wildcards, "y" would end up equating to "yes" as the possible answers could only be "yes" or "no". Thing is, if I typed "Yes" it still would not return any results.

    It just seems very odd to me that the fixed aspect of the query will work on "yes" as criteria, yet wont do it in the case of a user entered parameter.

    Any other query and it would work:

    Criteria: Friday
    or
    Criteria: [Please Enter Day of the Week:] and the user enters "friday", you'd expect the same results either way...

    Possible Epiphany:

    Is this simply because "yes" is a predetermined value so when entered as a Criteria, Access sees this as such but when entered as a parameter, it sees whatever is entered as a string rather than a value (so to speak)? This would mean that it is trying to match a string of "yes" to a string of "0" or "-1" hence no matches.

    I guess then what I want is for Access to store the date for a Checkbox as a "Yes" or a "No" so it is searchable by that string. Hang on, isn't it supposed to do that anyway? Or do ALL yes/no options store as "0" or "-1"??


    Oh no, I've gone crosseyed...

  8. #8
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Is this simply because "yes" is a predetermined value so when entered as a Criteria, Access sees this as such but when entered as a parameter, it sees whatever is entered as a string rather than a value (so to speak)? This would mean that it is trying to match a string of "yes" to a string of "0" or "-1" hence no matches.
    Pretty much, except 0 and -1 are numeric, not strings.

    The criteria bit of the query builder is effectively a UI that allows people with little or no experience of SQL to create SQL statements.

    The user input area that you have by default will be saving it as a string.

    You could use some vba on an AfterUpdate() on the input, along the lines of:

    Code:
    If (me.someInput.value Like ("y"*)) Then
        someLng = -1
    ElseIf (me.someInput.value Like("n"*)) Then
        someLng = 0
    Else: MsgBox "Oh no! You have not made a valid choice"
    End If
    Alternatively, you could use someBool = True and someBool = False, respectively. Calling the someLng or someBool variable in your SQL query.

    Make any sense?


    ------------------


    On a side note, does VBA not use 0 as False, and any other integer as True? Or is it exclusively -1? Seems a bit odd really...
    Last edited by kez1304; 08-19-11 at 11:14.
    Looking for the perfect beer...

  9. #9
    Join Date
    Aug 2011
    Posts
    11
    Thanks, I really appreciate both of your help.

    Yeah it kinda makes sense, I'm still very much a beginner with code - I can just about use onclick to open forms and populate fields =]

    I'll try each of the solutions offered so far and see how I get on.

    It seems like an awfully complex way round what appears to be a simple problem. Why cant "yes" mean "yes" everywhere? lol

  10. #10
    Join Date
    Aug 2011
    Posts
    11
    Ok - Here goes:

    This is the one I think will do what I need, though I admit much of it is beyond me:

    Another solution would consist in writing a function to translate the input parameter into a boolean value.

    In an independant module:
    Code:

    Public Function ToBoolean(ByVal Value As Variant) As Variant

    If IsNull(Value) Then
    ToBoolean = Null
    ElseIf IsNumeric(Value) Then
    If Value = 0 Then ToBoolean = 0 Else ToBoolean = -1
    Else
    Select Case UCase(Value)
    Case "N", "NO", "F", "FALSE", "OFF"
    ToBoolean = 0
    Case "Y", "YES", "T", "TRUE", "ON"
    ToBoolean = -1
    Case Else
    ToBoolean = Null
    End Select
    End If

    End Function

    Then:
    Code:

    Like "*" & ToBoolean([Please enter y/n or leave blank to return all records:]) & "*"
    However this returns an Undefined Function error. Is there part of this syntax I need to change for my situation or have I not put it in the right place or something?

  11. #11
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    1. Did you place the code in an independant module (i.e. a module that is not related to a form or a report and that is not a class module)?
    2. Can you compile the module?
    3. If 1 & 2 are true, what happens when you open the Immediate window and type:
    Code:
    Print ToBoolean("Y")
    and the <Enter> key?
    Have a nice day!

  12. #12
    Join Date
    Aug 2011
    Posts
    11
    1) I think so, not sure what a class module is. Insert>Module, right?
    2) Yes,
    3)Compile Error: Expected Variable or Procedure, not module.

    Think I did step 1 wrong...? =]

    EDIT:

    Quick thought, there arent any Windows components I should have to make this work are there? - I've had trouble with custom functions before...

    Edit 2:

    DOUY! Ive named the module the same as the function - rookie mistake - sorry =]
    Last edited by BitofaNoob; 08-19-11 at 12:21.

  13. #13
    Join Date
    Aug 2011
    Posts
    11
    Ok, thanks Sinndho, that works perfectly! Although it returned the first error before I renamed the module but it works fine now.

    Thanks to you too kez1304.


















    You guys are awesome

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    just wondering out aloud
    would the intrinsic vba constants VBYes & VBNo work in this case?
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Aug 2011
    Posts
    11
    and oddly - it has stopped working...??

    I havent changed anything...??


    EDIT:

    When I said it worked perfectly, I had impemented it in a query which worked. I then made a macro to open the form with the same code as the Where condition and it will only work when the prompt is left blank.

    Went back to the query to check that I copied the code correctly and now that only works if the prompt is left blank... wierd??


    Anyway, its coming up to the weekend (hooray!) so ill be leaving it for now. Thanks again for all your help guys
    Last edited by BitofaNoob; 08-19-11 at 12:45.

Posting Permissions

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