Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    4

    Unanswered: Multiple fields with table!value=x may or may not be in the query output.

    I have an Access 2003 SP2 .db that has value (either =x or blank) stored in 4 separate fields (ie. Field1, Field2,Field4). The problem I have is Im using a form to let the user place an x(the value already stored in the field) in each text box. Im having trouble with the selection criteria. They could select only one field or any possible combination of the 4. Ive tried so many changes I cant remember the original sql I tried to use. If they place an x in Field1, I need every record in Field1 that has an x, whether or not they select another Field(n). If they place an x in Field2 & Field4 the query should only put out records that have an x in both fields, it doesnt matter if there is an x in Field1 or Field3.

    In other words all selection criteria must be met, but the null fields(from the form) should have no effect on the output. I keep getting hung up on ifthenelse logic, but cant seem to make it work. I know it must be something simple Im missing, but I cannot seem to wrap my mind around it. Any help would be greatly appreciated.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Code:
    Dim strCriteria As String
    If chkField1 = True Then
       strCriteria = "Field1 = 'x'"
    ElseIf chkField2 = True AND chkField4 = True Then
       strCriteria = "Field2 = 'x' AND chkField4 = 'x'"
    Else
       'What happens if the other two aren't met?
    End If
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2006
    Posts
    4
    Thanks, but I don't think that will do it. Below is a small sample of the Input

    _____Field1__Field2__Field3__Field4
    Rec1___x______x
    Rec2___x____________x______x
    Rec3___x
    Rec4__________x_____x

    In the above example(FYI this goes on for well over 1000 recs) below would be sample output with selections.

    -if an x is placed in Form!Field1 Rec1,2,3 will be on the report
    -if an x is placed in Form!Field2 Rec1,4 will be on the report
    -if an x is placed in Form!Field1 & Form!Field2 Rec1 will be on the report
    -if an x is placed in Form!Field2 & Form!Field3 Rec4 will be on the report
    -if an x is placed in Form!Field2, Form!Field3 & Form!Field4 none of the above sample will be on the report.

    Hopefully this helps clarify what I need to do. I'm trying to crowbar an exsisting database because of time constraints. Thanks for the prompt reply.
    Last edited by EricJS; 05-01-06 at 17:24.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    -if an x is placed in Form!Field2, Form!Field3 & Form!Field4 none of the above sample will be on the report.
    I'm confused... why wouldn't rec3 be returned if this is true:

    -if an x is placed in Form!Field1 Rec1,2,3 will be on the report
    Do you have special logic behind selecting the first field? Maybe we could understand better if you included the actual business logic for the actual problem you're addressing? I don't understand why your criteria is what it is...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    May 2006
    Posts
    4

    Why it doesn't fit

    Rec3 doesn't fit all of the critera.

    If we add Rec5 this is what the table would 'look' like.

    _____Field1__Field2__Field3__Field4
    Rec1___x______x
    Rec2___x____________x______x
    Rec3___x
    Rec4__________x_____x
    Rec5__________x_____x______x

    Rec5 would fit the below example.
    -if an x is placed in Form!Field2, Form!Field3 & Form!Field4 none of the above sample will be on the report.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I think Teddy was on the right track; you need to build the WHERE clause dynamically. Try this:

    Code:
    Dim strCriteria As String
    
    strCriteria = "1=1"
    
    If chkField1 = True Then
       strCriteria = strCriteria & " AND Field1 = 'x'"
    End If
    
    If chkField2 = True Then
       strCriteria = strCriteria & " AND Field2 = 'x'"
    End If
    ...
    What you do with it depends on what you're trying to do; build a query, a filter, a wherecondition, etc.
    Paul

Posting Permissions

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