Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Help with Filtering and Sorting multiple Check Boxes

rroth642rroth642 Member Posts: 5
I have 2 selection boxes, that when either are checked, I want them to generate information from that record onto a report. My SQL code is working for filtering one selection box, but I am having difficulties wording the code to also filer for the second arguement.

My code reads as follows:

WHERE ([Contacts].[PrimaryCandidate]<>No)
ORDER BY[Contacts].[PrimaryCandidate], [Contacts].[LastName], [Contacts].[AlternativeCandidate], [Contacts].[LastName];

My problem is how to add another:
WHERE ([Contacts].[AlternativeCandidate]<>No)
arguement or how to best word it embedded in the above code.

Please Help!

Comments

  • dokken2dokken2 Member Posts: 532
    : I have 2 selection boxes, that when either are checked, I want them to generate information from that record onto a report. My SQL code is working for filtering one selection box, but I am having difficulties wording the code to also filer for the second arguement.
    :
    : My code reads as follows:
    :
    : WHERE ([Contacts].[PrimaryCandidate]<>No)
    : ORDER BY[Contacts].[PrimaryCandidate], [Contacts].[LastName], [Contacts].[AlternativeCandidate], [Contacts].[LastName];
    :
    : My problem is how to add another:
    : WHERE ([Contacts].[AlternativeCandidate]<>No)
    : arguement or how to best word it embedded in the above code.
    :
    : Please Help!
    :

    with 2 checkboxes you can have 4 possible selections,
    True/True, T/F, F/T, F/F

    one way is to handle each possible case with IF statements.

    [code]
    'YOUR SELECT STATEMENT
    Sql = "SELECT ... FROM ..."

    'BOTH CHECKBOX = FALSE
    If Me.Check1.Value = False And Me.Check2.Value = False Then _
    sWHERE = " "

    'CHECK1 = TRUE, CHECK2 = FALSE
    If Me.Check2.Value = True And Me.Check2.Value = False Then _
    sWHERE = " WHERE ([Contacts].[PrimaryCandidate]<>No) "

    'CHECK1 = FALSE, CHECK2 = TRUE
    If Me.Check2.Value = False And Me.Check2.Value = True Then _
    sWHERE = " WHERE ([Contacts].[AlternativeCandidate]<>No) "

    'BOTH CHECKBOX = TRUE
    If Me.Check2.Value = True And Me.Check2.Value = True Then _
    sWHERE = " WHERE ( ([Contacts].[PrimaryCandidate]<>No) And ([Contacts].[AlternativeCandidate]<>No) ) "

    'YOUR ORDERBY STATEMENT
    sORDERBY = "ORDER BY ..."

    Sql = sSELECT & sWHERE & sORDERBY
    [/code]


    you may want to make your WHERE clause more explicit, [any may need to tweak the logic to get your results] ie:

    [code]
    'BOTH CHECKBOX = FALSE
    If Me.Check1.Value = False And Me.Check2.Value = False Then _
    sWHERE = " WHERE ( ([Contacts].[PrimaryCandidate]<>Yes) " _
    " And ([Contacts].[AlternativeCandidate]<>Yes) )"

    'CHECK1 = TRUE, CHECK2 = FALSE
    If Me.Check2.Value = True And Me.Check2.Value = False Then _
    sWHERE = " WHERE ( ([Contacts].[PrimaryCandidate]<>No) " _
    " And ([Contacts].[AlternativeCandidate]<>Yes) )"
    [/code]

  • rroth642rroth642 Member Posts: 5
    : : I have 2 selection boxes, that when either are checked, I want them to generate information from that record onto a report. My SQL code is working for filtering one selection box, but I am having difficulties wording the code to also filer for the second arguement.
    : :
    : : My code reads as follows:
    : :
    : : WHERE ([Contacts].[PrimaryCandidate]<>No)
    : : ORDER BY[Contacts].[PrimaryCandidate], [Contacts].[LastName], [Contacts].[AlternativeCandidate], [Contacts].[LastName];
    : :
    : : My problem is how to add another:
    : : WHERE ([Contacts].[AlternativeCandidate]<>No)
    : : arguement or how to best word it embedded in the above code.
    : :
    : : Please Help!
    : :
    :
    : with 2 checkboxes you can have 4 possible selections,
    : True/True, T/F, F/T, F/F
    :
    : one way is to handle each possible case with IF statements.
    :
    : [code]
    : 'YOUR SELECT STATEMENT
    : Sql = "SELECT ... FROM ..."
    :
    : 'BOTH CHECKBOX = FALSE
    : If Me.Check1.Value = False And Me.Check2.Value = False Then _
    : sWHERE = " "
    :
    : 'CHECK1 = TRUE, CHECK2 = FALSE
    : If Me.Check2.Value = True And Me.Check2.Value = False Then _
    : sWHERE = " WHERE ([Contacts].[PrimaryCandidate]<>No) "
    :
    : 'CHECK1 = FALSE, CHECK2 = TRUE
    : If Me.Check2.Value = False And Me.Check2.Value = True Then _
    : sWHERE = " WHERE ([Contacts].[AlternativeCandidate]<>No) "
    :
    : 'BOTH CHECKBOX = TRUE
    : If Me.Check2.Value = True And Me.Check2.Value = True Then _
    : sWHERE = " WHERE ( ([Contacts].[PrimaryCandidate]<>No) And ([Contacts].[AlternativeCandidate]<>No) ) "
    :
    : 'YOUR ORDERBY STATEMENT
    : sORDERBY = "ORDER BY ..."
    :
    : Sql = sSELECT & sWHERE & sORDERBY
    : [/code]
    :
    :
    : you may want to make your WHERE clause more explicit, [any may need to tweak the logic to get your results] ie:
    :
    : [code]
    : 'BOTH CHECKBOX = FALSE
    : If Me.Check1.Value = False And Me.Check2.Value = False Then _
    : sWHERE = " WHERE ( ([Contacts].[PrimaryCandidate]<>Yes) " _
    : " And ([Contacts].[AlternativeCandidate]<>Yes) )"
    :
    : 'CHECK1 = TRUE, CHECK2 = FALSE
    : If Me.Check2.Value = True And Me.Check2.Value = False Then _
    : sWHERE = " WHERE ( ([Contacts].[PrimaryCandidate]<>No) " _
    : " And ([Contacts].[AlternativeCandidate]<>Yes) )"
    : [/code]
    :
    :

    Forgive my inexperience, I've tried entering the code exactly as you have it. Also reading it as sWhere = "Where as being the prompt telling me that you mean SQL code starts after the "... Could you give a little more direction on how to implement your code or read what you've entered? If it is easier to communicate via email, my email is robert.roth@navy.mil.

    Thanks,
    Rob

  • dokken2dokken2 Member Posts: 532
    : : : I have 2 selection boxes, that when either are checked, I want them to generate information from that record onto a report. My SQL code is working for filtering one selection box, but I am having difficulties wording the code to also filer for the second arguement.
    : : :
    : : : My code reads as follows:
    : : :
    : : : WHERE ([Contacts].[PrimaryCandidate]<>No)
    : : : ORDER BY[Contacts].[PrimaryCandidate], [Contacts].[LastName], [Contacts].[AlternativeCandidate], [Contacts].[LastName];
    : : :
    : : : My problem is how to add another:
    : : : WHERE ([Contacts].[AlternativeCandidate]<>No)
    : : : arguement or how to best word it embedded in the above code.
    : : :
    : : : Please Help!
    : : :
    : :
    : : with 2 checkboxes you can have 4 possible selections,
    : : True/True, T/F, F/T, F/F
    : :
    : : one way is to handle each possible case with IF statements.
    : :
    : : [code]
    : : 'YOUR SELECT STATEMENT
    : : Sql = "SELECT ... FROM ..."
    : :
    : : 'BOTH CHECKBOX = FALSE
    : : If Me.Check1.Value = False And Me.Check2.Value = False Then _
    : : sWHERE = " "
    : :
    : : 'CHECK1 = TRUE, CHECK2 = FALSE
    : : If Me.Check2.Value = True And Me.Check2.Value = False Then _
    : : sWHERE = " WHERE ([Contacts].[PrimaryCandidate]<>No) "
    : :
    : : 'CHECK1 = FALSE, CHECK2 = TRUE
    : : If Me.Check2.Value = False And Me.Check2.Value = True Then _
    : : sWHERE = " WHERE ([Contacts].[AlternativeCandidate]<>No) "
    : :
    : : 'BOTH CHECKBOX = TRUE
    : : If Me.Check2.Value = True And Me.Check2.Value = True Then _
    : : sWHERE = " WHERE ( ([Contacts].[PrimaryCandidate]<>No) And ([Contacts].[AlternativeCandidate]<>No) ) "
    : :
    : : 'YOUR ORDERBY STATEMENT
    : : sORDERBY = "ORDER BY ..."
    : :
    : : Sql = sSELECT & sWHERE & sORDERBY
    : : [/code]
    : :
    : :
    : : you may want to make your WHERE clause more explicit, [any may need to tweak the logic to get your results] ie:
    : :
    : : [code]
    : : 'BOTH CHECKBOX = FALSE
    : : If Me.Check1.Value = False And Me.Check2.Value = False Then _
    : : sWHERE = " WHERE ( ([Contacts].[PrimaryCandidate]<>Yes) " _
    : : " And ([Contacts].[AlternativeCandidate]<>Yes) )"
    : :
    : : 'CHECK1 = TRUE, CHECK2 = FALSE
    : : If Me.Check2.Value = True And Me.Check2.Value = False Then _
    : : sWHERE = " WHERE ( ([Contacts].[PrimaryCandidate]<>No) " _
    : : " And ([Contacts].[AlternativeCandidate]<>Yes) )"
    : : [/code]
    : :
    : :
    :
    : Forgive my inexperience, I've tried entering the code exactly as you have it. Also reading it as sWhere = "Where as being the prompt telling me that you mean SQL code starts after the "... Could you give a little more direction on how to implement your code or read what you've entered? If it is easier to communicate via email, my email is robert.roth@navy.mil.
    :
    : Thanks,
    : Rob
    :
    :
    you should substitute your Sql statement, ie: sql = "SELECT * FROM Table"
    same for sORDERBY, ie: sORDERBY = "ORDERBY Firstname, Lastname"

    the idea is to dynamically build the Sql statement which has 4 parts-
    SELECT fieldname
    FROM table
    WHERE ( some logic to restrict records )
    ORDERBY fields to sort

    you build the "complete" sql statement by concatenating strings for each part (SELECT, WHERE, etc) into one string.

    an easy way is to build your queries for each case you need to handle in the query builder, switch the query to "Sql view" and use the statement in your IF THEN statement selection logic-

    IF CHECK1.VALUE = True And CHECK2.VALUE = True Then
    Sql = "SELECT * FROM Table1 WHERE (first = 'john') And (last = 'doe')"

    IF CHECK1.VALUE = False And CHECK2.VALUE = True Then
    Sql = "SELECT * FROM Table1 WHERE (first = 'bill') And (last = 'doe')"

    IF CHECK1.VALUE = False And CHECK2.VALUE = False Then
    Sql = "SELECT * FROM Table1 WHERE (first <> 'john') And (last <> 'doe')"

Sign In or Register to comment.