Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012

    Unanswered: "Not Like" Criteria

    I have been working on a database for the last couple of days, and I think I have ripped the last of my hair out trying to get the Not Like function to work in Access 2010.

    I have 2 tables, one that holds all of the data from the last 3 months, and then another 1, that holds the data from a specific month (November)

    I now want to run a query that will show me the rest of the data that will show me October and December data, but not November.

    When I try and use the Not Like function it will just give me the same data that I already have and I can not find a way of getting the rest of the information.

    Hope someone can help me!!!!!!

    Thank you!!

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    What's the SQL expression you use?
    Have a nice day!

  3. #3
    Join Date
    Dec 2012
    I have put all the fields into the bottom part of the Query Design builder, and entered, Not Like [MSE Data from date range.Sequence No] under the MSE All Data ciriteria field

  4. #4
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Maybe I'm misinterpreting your scenario, here, but two points seem appropriate:

    First off, this design is most unusual! Why do you have November's data in the three-month Table and in a separate Table? Assuming that both Tables have identical Fields, the three-month Table should suffice.

    Secondly, does your Record have a Field defined as a DateTime Datatype, as opposed to, say, a Text Field that has 'November' as a Value for the Records of that month? If so, the Criteria for that Field, to exclude November, would be


    The Access Gnomes will take that Criteria and create a WHERE Clause in the underlying SQL Statement that will read

    WHERE ((DatePart("m",[YourDateField])<>11))

    If you Save your Query, Close it, and re-open it, you will find that they've actually created a Calculated Field with the name as DatePart("m",[YourDateField]) and its Criteria as <> 11, with the Field set to not display when the Query is Run.

    Also, is this a one-shot deal, or will you be repeating this, down the road, excluding a different month? If so, you'd probably need to add a Parameter to the Query.

    BTW, you'd only use Like (or Not Like, in this case) if you had a Field whose Value would be the month's name, and some people were entering "November," while others were simply entering "Nov."

    Linq ;0)>
    Last edited by Missinglinq; 12-13-12 at 13:16.
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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