none
Problems with Filtering, ANSI 92 and Wildcards RRS feed

  • Question

  • Hi all, Please help!  I have been going crazy with wildcards and filtering with the datasheet I have in a split form. The database is set up with ANSI 92 for SQL Syntax.

    In the datasheet there is one column that the user has characters in like #, (, ).  We want to do a filter on them.  The filter works fine if you highlight existing data, e.g. #18) and right click to choose CONTAINS in the filter.  In the dropdown, the filter shows as:

    CONTAINS "#18)"

    However, when we just try to select CONTAINS from the drop down and type in the characters to filter on, we can not get any results no matter what wildcard we use when filtering the parenthesis.  Besides trying to use the parenthesis in the CONTAINS, I've tried "#18?" and "#18[?]" but neither one works.  Anyone else have this problem?

    My user is very difficult.  She is not going to be happy until I come up with a solution.  Also, she is going to use FIND/REPLACE frequently and we can't get that to work either.

    I really, really need help.  I've been developing in Access for 16 years and this is one of the most frustrating problems I've found.

    TIA


    • Edited by Dale Corey Wednesday, August 31, 2016 7:38 PM
    Wednesday, August 31, 2016 3:24 PM

Answers

  • In the Find dialog, you can use [#]18[)] and the option to match "Any Part of Field" to find #18).

    In right-click > Text Filters > Contains, you can use [#]18 to filter on values that contain #18, but unfortunately I haven't found any way to make Access accept a ).

    On the other hand, Advanced Filter does accept

    Alike "%#18)%"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by David_JunFeng Sunday, September 18, 2016 2:06 PM
    Wednesday, August 31, 2016 3:49 PM
  • >>>Also, on a much more difficult level, I am trying to use REPLACE in a text box on the Find/Replace form that will give the user the right characters to put in the FIND/REPLACE dialog.  I can't believe how difficult this has been.

    According to your description, as far as I know that Access supports two sets of wildcard characters because it supports two standards for Structured Query Language — ANSI-89 and ANSI-92. As a rule, you use the ANSI-89 wildcards when you run queries and find-and-replace operations against Access databases — .mdb and .accdb files. You use the ANSI-92 wildcards when you run queries against Access projects — Access files connected to Microsoft SQL Server databases. Access projects use the ANSI-92 standard because SQL Server uses that standard.

    Find and Replace dialog box support ANSI-89 with Access database (.mdb and .accdb files).

    For more information, click here to refer about Access wildcard character reference

    If you have any feedback for Access, please feel free to submit them to User Voice:

    https://access.uservoice.com/

    Thanks for your understanding.
    • Proposed as answer by David_JunFeng Thursday, September 8, 2016 1:17 AM
    • Marked as answer by David_JunFeng Thursday, September 8, 2016 1:17 AM
    Thursday, September 1, 2016 5:44 AM

All replies

  • In the Find dialog, you can use [#]18[)] and the option to match "Any Part of Field" to find #18).

    In right-click > Text Filters > Contains, you can use [#]18 to filter on values that contain #18, but unfortunately I haven't found any way to make Access accept a ).

    On the other hand, Advanced Filter does accept

    Alike "%#18)%"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by David_JunFeng Sunday, September 18, 2016 2:06 PM
    Wednesday, August 31, 2016 3:49 PM
  • Thank you Hans,

    I thought I was going out of my mind with the filter.  For the parenthesis, I will just have the user find an concurrence of a parenthesis in the data and right click and choose CONTAINS.  That seems to work for now.  

    Also, on a much more difficult level, I am trying to use REPLACE in a text box on the Find/Replace form that will give the user the right characters to put in the FIND/REPLACE dialog.  I can't believe how difficult this has been.

    I really, really appreciate your response.

    Sincerely,

    Dale

    Wednesday, August 31, 2016 7:32 PM
  • >>>Also, on a much more difficult level, I am trying to use REPLACE in a text box on the Find/Replace form that will give the user the right characters to put in the FIND/REPLACE dialog.  I can't believe how difficult this has been.

    According to your description, as far as I know that Access supports two sets of wildcard characters because it supports two standards for Structured Query Language — ANSI-89 and ANSI-92. As a rule, you use the ANSI-89 wildcards when you run queries and find-and-replace operations against Access databases — .mdb and .accdb files. You use the ANSI-92 wildcards when you run queries against Access projects — Access files connected to Microsoft SQL Server databases. Access projects use the ANSI-92 standard because SQL Server uses that standard.

    Find and Replace dialog box support ANSI-89 with Access database (.mdb and .accdb files).

    For more information, click here to refer about Access wildcard character reference

    If you have any feedback for Access, please feel free to submit them to User Voice:

    https://access.uservoice.com/

    Thanks for your understanding.
    • Proposed as answer by David_JunFeng Thursday, September 8, 2016 1:17 AM
    • Marked as answer by David_JunFeng Thursday, September 8, 2016 1:17 AM
    Thursday, September 1, 2016 5:44 AM