As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options

exclusions in Access queries?

Dr. FrenchensteinDr. Frenchenstein Registered User regular
Is there a way to exclude 1 digit in a string using criteria? like, if i have all numbers from 30000 to 49999 in a population. I want to pull from 40000 to 49999 but NOT any records from 45000 to 45999. i could definitely do this using multiple criteria lines, but i want to keep it to one (i have to filter this data a lot, i'm already using 2 queries, and i'm trying to trim the criteria as much as possible)

I tried "like '4[^5]*'" but that didn't work.

Posts

  • Options
    Dr. FrenchensteinDr. Frenchenstein Registered User regular
    edited April 2013
    nevermind... i'm a dumbass. i can just put an or statement in two between statements to get it on one line. still feels like there should be a cleaner way to do it though...

    also, how many of those "nested" 'or' statements does access allow?

    Dr. Frenchenstein on
  • Options
    schussschuss Registered User regular
    As many as your database will allow in a SQL string, but note that the performance will degrade significantly. If you have intelligence in your numbers (IE, first 2 numbers mean something), you should split it out into a separate field via either a computed item in a view or via the database. That way you can just limit that field instead of dealing with substrings/weird IN/NOT IN lists.

  • Options
    Dr. FrenchensteinDr. Frenchenstein Registered User regular
    it's GL account strings, so i have them separated out into their specific fields, but that's about as granular as i can get. it's not even 10k records, so performance isn't really an issue.

    well, i guess i could trim the 'account' field down to it's individual 5 digits, but ugh.

    i hit a doozy of an exlusion as well. i need (lets assume i have numbers from 10000 to 99999 in my table) to pull 'between 53000 and 57999' but not anything where the 3rd digit is a 5. some google links say i can use 'between 53[^5]* and 57[^5]*' but that doesn't seem to work.

  • Options
    schussschuss Registered User regular
    You should be able to use a Substr(Account, 3, 1) != 5, but it depends on the database. It might be Mid instead of substring.

Sign In or Register to comment.