Results 1 to 7 of 7

Thread: Query Problem

  1. #1
    Join Date
    Mar 2012
    Posts
    22

    Unanswered: Query Problem

    I have been trying every possible command in my query to have it NOT bring out certain information. I have tried Not Like, Not, I have even gone as far as trying to have it bring out only one type of information like ="ACT" but none of them do anything. It is first on the criteria line, and the other criteria lines are working fine. Any other command lines would be appreciated!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dunno
    as I don't use the query designer
    can we see the SQL that is generated (switch to to 'SQL' view then copy and paste the SQL here
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2012
    Posts
    22
    Here is the whole WHERE clause:

    WHERE (((Obligations.[PMT DUE DT])>Now()-32 And (Obligations.[PMT DUE DT])<Now()+365)) OR ((([Lease Basic].[EXP DATE]) Is Null)) OR ((([Lease Basic].[EXP DATE])>Now()-32 And ([Lease Basic].[EXP DATE])<Now()+365)) OR ((([Lease Basic].STAT) Not Like "SOLD")) OR ((([Lease Basic].STAT) Not Like "INA"))

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    like usually expects at least one wildcard matching character. you will probably be better using the not equals <> comparator or
    Code:
    [Lease Basic].STAT) <> "SOLD" or [Lease Basic].STAT) <> "INA")
    OR
    Code:
    [Lease Basic].STAT) not in ( "SOLD","INA")
    although your date code works what you should be doing is using the date functions to dio date column manipulations. consider using dateadd() or datediff()
    consider using the between logical operator ie

    Code:
    Obligations.[PMT DUE DT] between Now()-32 And Now()+365
    I suspect the reason for your problem is that yyou are using an or for the two Lease Basic.Stat tests. by definition both are mutually exclusive. so becuase you are suing NOT then every row will return true from that compariosn

    Code:
    OR ((([Lease Basic].STAT) Not Like "SOLD")) AND ((([Lease Basic].STAT) Not Like "INA"))
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2012
    Posts
    22
    Ok I just tried it with the :
    OR ((([Lease Basic].STAT) Not In ("sold","ina")))

    and also I tried the not like "Sold" and "INA" which then returned the exact same result. The column shows items with the Sold or INA in it. Am I missing something?

    Also if I was to change to datediff() does that require that the date has to be in a specific format? i.e general, long, short??

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by gumbi17 View Post
    Here is the whole WHERE clause:

    WHERE (((Obligations.[PMT DUE DT])>Now()-32 And (Obligations.[PMT DUE DT])<Now()+365)) OR ((([Lease Basic].[EXP DATE]) Is Null)) OR ((([Lease Basic].[EXP DATE])>Now()-32 And ([Lease Basic].[EXP DATE])<Now()+365)) OR ((([Lease Basic].STAT) Not Like "SOLD")) OR ((([Lease Basic].STAT) Not Like "INA"))
    I think you simply need to add some parens to isolate the complete AND and OR clauses, as follows:
    Code:
    WHERE ((((Obligations.[PMT DUE DT])>Now()-32 And (Obligations.[PMT DUE DT])<Now()+365))) OR (((([Lease Basic].[EXP DATE]) Is Null))) OR (((([Lease Basic].[EXP DATE])>Now()-32 And ([Lease Basic].[EXP DATE])<Now()+365))) AND (((([Lease Basic].STAT) Not Like "SOLD*")) AND ((([Lease Basic].STAT) Not Like "INA*")))
    I would also make two other changes:

    1) Instead of the Now() function you use 4 times, use the Date() function. The Now() function is specifically for using both the date and time of this moment, such as in a timestamp. You're using only the date component, so use the Date() function. It's more efficient.

    2) The clause
    Code:
    ((((Obligations.[PMT DUE DT])>Now()-32 And (Obligations.[PMT DUE DT])<Now()+365)))
    could be made simpler by using the Between operator instead of ">" and "<", as healdem suggested. Here's what it would look like:
    Code:
    (((Obligations.[PMT DUE DT]) Between Date()-32 And Date()+365))
    Ditto for the expiration date.

    Anything in red was added/changed by me, although some of it was already mentioned by healdem. Good luck.

    Sam
    Last edited by Sam Landy; 06-11-12 at 23:16. Reason: Had some more time to think

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you seem to be moving the goalposts on this
    I suspect you will be better off expressing your logic in English
    then having described that, start to translate that into VBA
    use brackets to group together as required

    in an ideal world you don't want to repeat terms or 'phrases'
    if the date bracketing applies to more than one other clause then
    (mydate between avalue and anothervalue AND (Acolumn not in 'INA', 'SOLD' or not isnull(InvoiceDate))
    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
  •