Not Like….Not In (‘…’) (VB.NET, Access 2K3)

Topic

New Reply

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!!!

Reply | Quote

Replies

With LIKE, you should always use wildcards. Try

WHERE tblAllBids.BidDate>=Date() AND tblAllBids.ProjStat Not Like '%NO BID%'

(The % character is the standard SQL wildcard for “any number of characters”, Access uses * for this)

Reply | Quote

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!!

Reply | Quote

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 for instructions.

Reply | Quote

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

Reply | Quote

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 tblAllBids.ProjStat 'NO BID'. If the former, I think we will need to double-check the DB.

Reply | Quote

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!

Reply | Quote

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:

WHERE BidDate>=Date() AND (ProjStat Not Like '%NO BID%' OR ProjStat Is Null)

Note the use of parentheses, they are necessary since AND has priority over OR.

Reply | Quote

Thanks!

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

Works like a gem.

Reply | Quote

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

Reply | Quote