Results 1 to 4 of 4
  1. #1
    MTD072 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2017
    Posts
    14

    filter criteria from a string

    Hi All



    I am trying to create a filter criteria in a query from a string, stored in a table. I'm doing it this way because the filter criteria needs to be dynamic as different users will have different skilltags.

    Example for the filter criteria would be as follows"P1" Or ("P2" Or "P3" Or "P4" Or "A1" Or "A2")This would work if it were manually pasted into the query design view and the query then run. But when I try to reference the table for it to pick this up, I get no results. (Yes, there is test data there that should provide a hit)

    Tried referencing by using the expression builder and the dlookup function, unsuccessful on both counts.

    SQL code reads as follows

    Code:
     SELECT [TPS 2].ID, [TPS 2].[Benefit Type], Left(PlainText([Issue Comments]),50) AS txfldFROM [TPS 2]WHERE ((([TPS 2].[Benefit Type])=[Session]![ssfilter]) AND (([TPS 2].[Action taken / Resolution])="Referred to helpdesk") AND (([TPS 2].[Next action])="Send to helpdesk"));
    or
    Code:
    SELECT [TPS 2].ID, [TPS 2].[Benefit Type], Left(PlainText([Issue Comments]),50) AS txfldFROM [TPS 2]WHERE ((([TPS 2].[Benefit Type])=DLookUp("[ssfilter]","session","[Session id]=" & 1)) AND (([TPS 2].[Action taken / Resolution])="Referred to helpdesk") AND (([TPS 2].[Next action])="Send to hepdesk"));
    Would appreciate a suggestion on how to make this work, or another approach that would provide this sort of functionality. Thanks in advance
    Last edited by MTD072; 12-03-2017 at 10:22 PM. Reason: formatting lost

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Doesn't work because all Access query object sees is a string of text. The Or's are not recognized as SQL keywords, just characters.

    Could use the field content to set form Filter property or as WHERE CONDITION in OpenForm/OpenReport method.

    However, in spite of what you see in the query design grid, the actual criteria syntax would have to be like:

    [Benefit Type]="P1" Or [Benefit Type]="P2" Or [Benefit Type]="P3" Or [Benefit Type]="P4" Or [Benefit Type]="A1" Or [Benefit Type]="A2"

    or

    [Benefit Type] IN ("P1","P2","P3","P4","A1","A2")


    Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.
    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
    MTD072 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2017
    Posts
    14
    Hi June7

    Thanks for your swift response.

    I'm not entirely sure what you mean by "Could use the field content to set form Filter property or as WHERE CONDITION in OpenForm/OpenReport method."

    Would love for you to clarify for me.

    Unfortunately, the data source I'm referencing was created by someone else so I have no option but to use the table names in situ. I would never use spaces in naming, personally.

    Regards

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So user could select the criteria from a combobox then in VBA use it like:

    Me.Filter = Me.cbxCriteria
    Me.FilterOn = True

    or

    DoCmd.OpenReport "reportname", , , Me.cbxCriteria
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 07-12-2016, 02:59 PM
  2. String criteria used in DLookup
    By joecamel9166 in forum Programming
    Replies: 9
    Last Post: 03-11-2016, 09:37 AM
  3. Criteria string too long
    By bomich in forum SQL Server
    Replies: 2
    Last Post: 12-06-2012, 02:42 AM
  4. Multiple Criteria in a string
    By cksm4 in forum Programming
    Replies: 3
    Last Post: 08-04-2010, 11:54 AM
  5. How to Concatenate String Criteria
    By ColPat in forum Programming
    Replies: 2
    Last Post: 06-26-2010, 08:48 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