Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    34

    Unanswered: How to search for records that have ALL instances of something in a column

    this has been driving me nuts all day

    table looks like this:
    ID1 ID2 Date Letter
    1 123456 1/1/05 I
    1 123458 1/2/05 Z
    1 123445 1/5/05 Z
    2 123444 1/1/05 Z
    2 123105 1/2/05 Z
    2 125795 1/5/05 Z
    I basically want to run a query that finds all records for ID1 that have all the same letters in the letter field. So in the example above, let's say I want to find an ID that has ONLY all Z's in the letter field (no I's, or anything else). I got a regular query to show me a raw output like the one above, I just don't know how to refine it enough to only show ID's that will ONLY have all Z's in the Letter field. Anyone have any suggestions?

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Assuming you have a TextBox on your Form where you enter the desired letter named myFormTextBox:

    In the OnClick event of a search button you might have:
    Code:
    Dim SQLstrg As String
    SQLstrg = "Select * FROM [myTableName] WHERE [myTableName].[Letter] = '" & Me.myFormTextBox & "'"
    Me.ControlSource = SQLstrg
    Me.Requery
    or, you could set up a Filter like so......
    Note: the Form Control Source should be the table name.
    (again in the OnClick event of a search button)
    Code:
    Me.Filter = "[Letter] = '" & Me.myFormTextBox & "'"
    Me.FilterOn = True
    Me.Requery

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Oct 2005
    Posts
    34
    Quote Originally Posted by CyberLynx
    Assuming you have a TextBox on your Form where you enter the desired letter named myFormTextBox:

    In the OnClick event of a search button you might have:
    Code:
    Dim SQLstrg As String
    SQLstrg = "Select * FROM [myTableName] WHERE [myTableName].[Letter] = '" & Me.myFormTextBox & "'"
    Me.ControlSource = SQLstrg
    Me.Requery
    or, you could set up a Filter like so......
    Note: the Form Control Source should be the table name.
    (again in the OnClick event of a search button)
    Code:
    Me.Filter = "[Letter] = '" & Me.myFormTextBox & "'"
    Me.FilterOn = True
    Me.Requery

    .
    it's actually not a form, just a regular table query.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's possible I misunderstand but:

    Code:
     
    SELECT ID1, ID2, Date, Letter
    FROM MyTable AS A
    WHERE NOT EXISTS(SELECT NULL FROM MyTable AS B WHERE NZ(Letter, '') <> 'Z' AND B.ID1 = A.ID1)
    Based on the above data it would return the 3 ID1 = 2 records. The right lines?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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