Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80

    Angry Filtering Data from a specified column

    Ladies and Gentleman I am at a dilemma I am trying to find out a way to filter out certain data in a column field I have an idea on how to do this but I do not know the correct syntax to use. First here is the table and here is the code structure I would like to write.



    First Name Last Name First Match Second Match
    James Matheson 0 2
    Monroe Labonson 4 3
    Barack Obama 2 5
    Frederick Douglas 3 4
    Steve MCGowan 1 1
    John Seals 15 15
    Mike Omalley 14 15


    for i= 1 to length of column First Match
    for j=1 to length of column Second Match

    If value of the data in column First Match = 15 OR value of the data in column First Match = 1 AND value of the data in column Second Match = 15 OR value of the data in column Second Match = 1 Then

    Filter the data and append so the filtered datas are saved for both First Match and Second Match

    end if

    next
    next

    Is my method correct?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Do you want a query? What do you mean by 'filter the data' - do you want to include or exclude records that meet the conditions?

    Not really understanding the criteria. Each If statement references the same field for each parameter. Is that correct?

    Show an example of the output that would come from that sample data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    Oh I am sorry there is something wrong with the way I phrased my explanation, I am trying to filter out the data that is a 15 and 1 so that only data that have the values 0,2,3,4,5,6...14 will be shown for instance the information of john and steve will not be shown because both the first and second match fields have a 1 or 15 but the rest of data will be shown my form is a split form setup.

    I have changed the logic of the code in my original post.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You don't want records where 1 is in both fields or 15 is in both fields?

    Mike's record is okay because 15 is in only one field?

    If both fields had any other number (both 2 or both 3, etc) then still retrieve them?

    Maybe:

    SELECT *
    FROM tablename
    WHERE (((IIf([First Match]=[Second Match] And ([First Match]=1 Or [First Match]=15 Or [Second Match]=1 Or [Second Match]=15),True,False))=False));
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    It says the syntax is wrong
    SELECT *FROM "Table1" WHERE(((IIf([First Match]=[Second Match] And ([First Match]=1 Or [First Match]=15 Or [Second Match]=1 Or [Second Match]=15),True,False))=False));

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Remove the quote marks.

    Need a space between * and FROM.

    Is your table actually named Table1?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    I still get the same error and yes the table name is deffault name which is Table1 it doe not seem like the astrick.
    And should there be a semicolon at the end I thought vba didnt care for semicolons.

    SELECT * FROM Table1 WHERE(((IIf([First Match]=[Second Match] And ([First Match]=1 Or [First Match]=15 Or [Second Match]=1 Or [Second Match]=15),True,False))=False));

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The wildcard worked for me.

    Are the Match fields text type? If so, then enclose the number parameters in quote marks or change the fields to number type.

    Yes, there must be a semi-colon at the end of a SELECT statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    No they are number types I input the values as numbers. It says syntax error is it possible that this is access 2007 I am using?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Access 2007 should be able to run the query.

    However, try removing the wildcard and referencing the field names explicitly.

    I just noticed lack of space following WHERE. That might be an issue.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    I dont know what you mean by the wild card but I have input a space for where still the same issue.

  12. #12
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    I think I have another Idea of how to write this type of filtering but I need help with coming up with the right syntax:


    'Dim strArray(0 To 13) As String

    strArray(0) = 0
    strArray(1) = 2
    strArray(2) = 3
    strArray(3) = 4
    strArray(4) = 5
    strArray(5) = 6
    strArray(6) = 7
    strArray(7) = 8
    strArray(8) = 9
    strArray(9) = 10
    strArray(10) = 11
    strArray(11) = 12
    strArray(12) = 13
    strArray(13) = 14



    ' do not know how to write the while loop
    while there is still data to read in the fields First Match AND Second Match

    if ("[First Match]"=15 OR "[First Match]=1") AND ("[Second Match]=15" OR "[Second Match]=1") Then
    for i= 1 to 13
    if "[First Match]" = strArray(i) OR "[Second Match]" = strArray(i) Then

    Me.Filter = "[First Match] = """ & strArray(i) & """"
    Me.Filter = Me.Filter & " AND FirstName = """ & first & """"

    Me.Filter = Me.Filter & " AND "[Second Match]" = """ & strArray(i) & """"
    end if
    next
    end if

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The * character is a wildcard.

    Here is example function with arguments for the 2 field values and returning a True/False. The function would be called in query. No idea what you want the array for.

    Function ValidMatch(intM1 As Integer, intM2 As Integer) As Boolean
    ValidMatch = Not ((intM1 = intM2) And (intM1 = 1 Or intM1 = 15 Or intM2 = 1 Or intM2 = 15))
    End Function

    Then call the function in query:

    SELECT * FROM Table1 WHERE ValidMatch([First Match], [Second Match]) = True;

    However, based on the sample data posted, the original query I gave should work. It works for me.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    I have tried to write it this way but still received "syntax error"

    SELECT "[First Match]" And "[Second Match]" FROM Table1 WHERE (((IIf([First Match]=[Second Match] And ([First Match]=1 Or [First Match]=15 Or [Second Match]=1 Or [Second Match]=15),True,False))=False));

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Use the query Design View to help construct the query (pull fields down to the grid or select from Fields dropdown) then switch to SQL View to see the correct syntax.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-12-2013, 02:19 AM
  2. Replies: 1
    Last Post: 12-08-2011, 08:03 AM
  3. Replies: 1
    Last Post: 08-18-2011, 08:35 AM
  4. Filtering/sorting data
    By jemelton in forum Access
    Replies: 5
    Last Post: 06-09-2010, 01:47 PM
  5. Filtering data
    By billybong in forum Access
    Replies: 1
    Last Post: 11-07-2009, 12:20 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums