Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not Like....Not In ('...') (VB.NET, Access 2K3)

    Hello!

    For the life of me I can't figure out how to get this particular issue resolved. I'm wishing to check a column "ProjStat", and if contains "NO BID", for that record or records to be excluded. These are pulling from an Access 2K3 database, and going through ASP.NET (hince the single quotes). Here is the WHERE arguement in the SQL statement:

    WHERE (((tblAllBids.BidDate)>=Date()) AND ((tblAllBids.ProjStat) NOT LIKE ('NO BID')))

    With everything from AND.... omitted, this returns 10 records. 2 of these records contain "NO BID" in the ProjStat field. With the AND ((tblAllBids.ProjStat.... included, it returns 0 records!!!

    I've also tried ...AND ((tblAllBids.ProjStat) NOT IN ('NO BID')))

    This produces the same results - 0 records.

    Any ideas? I'm really stumped on this one.

    Thanks much!!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not Like....Not In ('...') (VB.NET, Access 2K3)

    With LIKE, you should always use wildcards. Try
    <code>
    WHERE tblAllBids.BidDate>=Date() AND tblAllBids.ProjStat Not Like '%NO BID%'
    </code>
    (The % character is the standard SQL wildcard for "any number of characters", Access uses * for this)

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Like....Not In ('...') (VB.NET, Access 2K3)

    Thanks for the speedy reply!

    Looks like this returns the same result - 0 records. I know the '%' is correct, because = I use it in another part of the SQL. Maybe I've over-looked something, so here is the complete SQL statement:

    strSQL = "SELECT tblAllBids.ID AS ID1, tblAllBids.ProjName, tblAllBids.BidDate, tblAllBids.ProjLoc, tblAllBids.Engineer, " & _
    "tblAllBids.EngCont, tblAllBids.Owner, tblAllBids.OwnerCont, IIf([ProjEst] Like 'Will%','W.T.',IIf([ProjEst] " & _
    "Like 'Bob%','B.G.',IIf([ProjEst] Like 'Doug%','D.M.',IIf([ProjEst] Like 'Brian%','B.C.',IIf([ProjEst] " & _
    "Like 'Mitch%','M.B.',IIf([ProjEst] Like 'Cyle%','C.B.','')))))) AS ProEst, IIf([ExcEst] " & _
    "Like 'Kody%','K.S.',IIf([ExcEst] Like 'Mike%','M.M.',IIf([ExcEst] Like 'Doug%','D.M.',IIf([ExcEst] " & _
    "Like 'Ron%','R.M.','')))) AS ExEst, IIf([UtilEst] Like 'Norm%','N.D.',IIf([UtilEst] " & _
    "Like 'Kevin%','K.B.',IIf([UtilEst] Like 'Trav%','T.F.',''))) AS UtiEst, IIf([ConcEst] " & _
    "Like 'Will%','W.T.',IIf([ConcEst] Like 'Bob%','B.G.',IIf([ConcEst] Like 'Doug%','D.M.',IIf([ConcEst] " & _
    "Like 'Brian%','B.C.',IIf([ConcEst] Like 'Mitch%','M.B.',''))))) AS ConEst, tblAllBids.TurnTot, " & _
    "tblAllBids.ExcTot, tblAllBids.UtilTot, tblAllBids.ConcTot, tblAllBids.ProjStat, tblAllBids.ID FROM tblAllBids " & _
    "WHERE tblAllBids.BidDate>=Date() AND tblAllBids.ProjStat NOT LIKE '%NO BID%'"

    Thanks again!!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not Like....Not In ('...') (VB.NET, Access 2K3)

    The SQL looks OK. Could you attach a stripped down copy of the Access database with just enough left to demonstrate the problem? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Like....Not In ('...') (VB.NET, Access 2K3)

    Don't forget that the Access-specific functionality (such as IIF) is not supported in OLEDB (aka ADO.NET).

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Not Like....Not In ('...') (VB.NET, Access 2K3)

    Hi,
    Do the fields in question contain "NO BID" or are they equal to "NO BID"? If the latter, you should just be able to use <code>tblAllBids.ProjStat <> 'NO BID'</code>. If the former, I think we will need to double-check the DB.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Like....Not In ('...') (VB.NET, Access 2K3)

    Thank you all for your help!!

    Still no dice...tried <> 'NO BID'. (The field will contain "NO BID" exactly). The field could also be null, if that makes a difference. IIF works fine...I only get 0 records when I add the 'NO BID' business to the SQL. Attached is a .zip with the DB and the full code to the page.

    You guys are the best, thanks so much!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not Like....Not In ('...') (VB.NET, Access 2K3)

    In the records for which ProjStat is not equal to "NO BID", ProjStat is null (empty, blank). Null is not included in conditions such as <>"NO BID" or Not Like "%NO BID%"; you must specify it separately:
    <code>
    WHERE BidDate>=Date() AND (ProjStat Not Like '%NO BID%' OR ProjStat Is Null)
    </code>
    Note the use of parentheses, they are necessary since AND has priority over OR.

  9. #9
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Like....Not In ('...') (VB.NET, Access 2K3)

    Thanks!

    That fixes her. You guys always come through, and for that I am eternally grateful!!!

    Works like a gem.

  10. #10
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Like....Not In ('...') (VB.NET, Access 2K3)

    ... and also there's all of us who read the answers and are silently enlighteded as well! I add my thanks to yours.

Posting Permissions

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