Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to - query. (2000)

    Hello, need to build a select query with five fields, say A B C D and E and have criteria for all combos of is null and/or is not null, is there a smarter way of doing it opposed to many rows of and/or criteria?

    Thanks Darren.

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to - query. (2000)

    My approach would be to build the SQL for the query within a form (see image). It's still a bit of a laborious process I'm afraid, but keeps user-input to a minimum and keeps the SQL itself fairly simple. Is this enough to get you started or would you like some help with the code?
    Waggers
    If at first you do succeed, you've probably missed something.

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to - query. (2000)

    Cheers Waggers, a little help with the code with be appreciated.

    Thanks Darren.

  4. #4
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to - query. (2000)

    No worries. Attached is a database with a table (myTable, with fields A to E), a query (myQuery) and a form (Form1).
    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>I never was one for naming conventions!
    There's no data in myTable, but Form1 will update the design of myQuery according to the options selected.

    Here's the code I've used. Obviously you'll need to change the field and object names as appropriate.
    <div style="width: 100%; background-color: #FFFFFF;">Private Sub Command41_Click()
    Dim SQL As String
    SQL = "SELECT myTable.* FROM myTable WHERE "
    Select Case FrameA
    Case 1 'is Null
    SQL = SQL & "A Is Null AND "
    Case 2 'Is Not Null
    SQL = SQL & "A Is Not Null AND "
    End Select ' (No criteria needed for field A if case 3 - all values)
    Select Case FrameB
    Case 1 'is Null
    SQL = SQL & "B Is Null AND "
    Case 2 'Is Not Null
    SQL = SQL & "B Is Not Null AND "
    End Select ' (No criteria needed for field B if case 3 - all values)
    Select Case FrameC
    Case 1 'is Null
    SQL = SQL & "C Is Null AND "
    Case 2 'Is Not Null
    SQL = SQL & "C Is Not Null AND "
    End Select ' (No criteria needed for field C if case 3 - all values)
    Select Case FrameD
    Case 1 'is Null
    SQL = SQL & "D Is Null AND "
    Case 2 'Is Not Null
    SQL = SQL & "D Is Not Null AND "
    End Select ' (No criteria needed for field D if case 3 - all values)
    Select Case FrameE
    Case 1 'is Null
    SQL = SQL & "E Is Null AND "
    Case 2 'Is Not Null
    SQL = SQL & "E Is Not Null AND "
    End Select ' (No criteria needed for field E if case 3 - all values)

    'Remove redundant "AND " or "WHERE "
    If Right(SQL, 4) = "AND " Then
    SQL = Left(SQL, Len(SQL) - 4)
    Else
    SQL = Left(SQL, Len(SQL) - 6)
    End If

    'Update query SQL and open query
    CurrentDb.QueryDefs("myQuery").SQL = SQL
    DoCmd.OpenQuery "myQuery"
    End Sub</div hiblock>
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to - query. (2000)

    Blimey, I thank you.

    That's fantastic.

    Darren.

Posting Permissions

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