Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106

    Unanswered: Not in logic in MS Access 2003

    Hi,
    I am writing a function in a module in MS Access 2003. I am trying to evaluate a list using "NOT IN" logic.

    What is the syntax in VBA for MS Access 2003 that would allow me to say something like:

    IF Name not in ("Joe","Moe","Floe") THEN
    A
    END IF.

    Thanks
    Mo

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    You can do it simply with and IF statement:
    Code:
    If NOT ((strName = "Joe") OR (strName = "Moe") OR (strName = "Floe")) Then
        'Do stuff
    End If
    Or you can use a Select Case statement, your choice.
    Me.Geek = True

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Nick the Greek's advice is excellent, but I think the syntax you were looking for wasn't actually

    Not In

    but rather

    Not Like
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    NOT IN is not recognised as a VBA function or operator. The solution would consist in using the Eval function:

    Code:
    If Eval("'" & Name & "' NOT IN ( 'Joe', 'Moe', 'Floe' )") Then
    By the way, be careful when naming variables "Date", "Name", etc. as those names are either names of functions in VBA or intrinsic property names of several Access objects.
    Have a nice day!

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by Missinglinq View Post
    Nick the Greek's advice is excellent, but...
    But I'm dutch!

    Sinndho makes a good point to btw about the name, I just changed it in my post: be wary of any name on the Access Reserved Word List. A good way to avoid this is to adopt a good naming convention, I might suggest the Leszynski/Reddick Guidelines, or something similar.
    Me.Geek = True

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Leszynski/Reddick Guidelines are fine for naming variable inside a program if you so wish, however in my books they are pants when used in table and column definitions.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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