+ Reply to Thread
Results 1 to 11 of 11

SQL where error

  1. #1
    Registered User
    Join Date
    06-20-2018
    Location
    Toronto, Canada
    MS-Off Ver
    2016
    Posts
    5

    SQL where error

    So I have a bit of VBA put together to manage the output of data based on selections made from a form in Access 2016. This all seemed to work perfectly. I was asked to further streamline the output and added one more line (or variation based on selections). The SQL statement looks like this:

    SELECT DISTINCT
    TOP (100) PERCENT dbo.StaffListings.LocCode, dbo.StaffListings.AutoNumber, dbo.StaffListings.Floor, dbo.StaffListings.LastName, dbo.StaffListings.ShowName, dbo.StaffListings.FirstName, LEN(dbo.StaffListings.Extension) AS ExtLen, dbo.StaffListings.Department, dbo.StaffListings.FirstAid, dbo.StaffListings.FireWarden, dbo.StaffListings.SpecialStatus, dbo.OfficeCodes.Address1, dbo.OfficeCodes.Address2, dbo.OfficeCodes.City, dbo.OfficeCodes.StateProv, dbo.OfficeCodes.PostalZip, dbo.OfficeCodes.PhoneNo, dbo.OfficeCodes.FaxNo, dbo.StaffListings.TempFlex, dbo.OfficeCodes.SharepointPhone, dbo.OfficeCodes.ExtraText, LEFT(dbo.StaffListings.LastName, 1) AS Heads, '' AS [Dummy], dbo.StaffListings.Extension, dbo.StaffListings.[10DigitPhone], '' AS OfficeNumber, '' AS [Column], IIf(dbo.StaffListings.LastName=dbo.StaffListings.ShowName, dbo.StaffListings.LastName + ' ' + dbo.StaffListings.ShowName, dbo.StaffListings.LastName + ', ' + dbo.StaffListings.ShowName) as outText

    FROM dbo.StaffListings INNER JOIN dbo.OfficeCodes ON dbo.StaffListings.LocCode = dbo.OfficeCodes.LocCode

    WHERE (((dbo.StaffListings.LocCode) Like N'TOR') AND (NOT (dbo.StaffListings.LastName LIKE N'0ConfRoom%')) AND (NOT (dbo.StaffListings.LastName LIKE N'0RoomExtra')) AND (NOT (dbo.StaffListings.LastName LIKE N'0PhoneExtra')) AND (NOT (dbo.StaffListings.LastName LIKE N'0Emerg')) AND ((LEN(dbo.StaffListings.Extension) > 0) OR (LEN(dbo.StaffListings.[10DigitPhone]) > 1)) AND (NOT(dbo.StaffListings.SpecialStatus LIKE '+++')))
    ORDER BY outText

    The part that was most recently added is in bold above. The variations would be to remove the NOT. The problem is this strips out more data than just excluding the value of +++, and I am not sure why. I have gone through the data, and have marked only 2 names with the +++ value, but about have the names don't show when this last AND section is added. Sorry if you guys don't look at SQL here.

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2016
    Posts
    4,596

    Re: SQL where error

    Sorry for such a late reply, maybe you have solved your problem by now. Yes we do look at SQL... and no, I don't immediately see any issue with the added code, I was hoping maybe brackets, but those seem to add up quite well.
    Out of curiosity, have you tried using something like 'AAA' instead of '+++'? At least in the short term that might tell you if there is an issue with the + symbol for some reason.
    I mostly wonder about 2 scenarios however,
    1. I have found that sometimes one more criteria causes issues in queries, almost like a memory limitation just can't handle it, then I have had multiple columns show up blank, or missing records.
    2. Any chance that the missing names have the same LocCode as the records with '+++' ? The join may be getting adversely impacted by the condition somehow.

    It is hard to test something like this, since I'd have to make a database with the tables to even have a chance of testing it out.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    06-20-2018
    Location
    Toronto, Canada
    MS-Off Ver
    2016
    Posts
    5

    Re: SQL where error

    I have tried several values for the check. SSS, SITE, +++, AAA... All give the same result. The thing that really gets me is that there is only a problem when I exclude say, the value of SSS. If I want to retrieve the value of SSS I get the proper results.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,188

    Re: SQL where error

    Please Login or Register  to view this content.
    Why use LIKE operator with no wild card or pattern?

    Try below if checking for string literal...
    Please Login or Register  to view this content.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  5. #5
    Registered User
    Join Date
    06-20-2018
    Location
    Toronto, Canada
    MS-Off Ver
    2016
    Posts
    5

    Re: SQL where error

    I tried that on got the same results. I think I found the answer though. NULLs. When I also check for NULL with the exclusion of the SSS value, then I get all results. Funny that 90% of these returned values have nothing entered for specialstatus. But it appears that 40% of those have the value seen as NULL, the others don't... In any case, my check for null fixes the issue:
    Please Login or Register  to view this content.

  6. #6
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2016
    Posts
    4,596

    Re: SQL where error

    Nice inlanoche Glad you got it resolved, and nice troubleshooting. Still weird though, since NULL is clearly NOT +++

  7. #7
    Registered User
    Join Date
    06-20-2018
    Location
    Toronto, Canada
    MS-Off Ver
    2016
    Posts
    5

    Re: SQL where error

    I agree, but no one said that coding is logical...

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,188

    Re: SQL where error

    That's how MS Access comparison operators work/evaluate, unfortunately (i.e. doesn't return TRUE/FALSE, but null).

    From support.office.com
    Note: In all cases, if either the first value or the second value is null, the result is then also null. Because null represents an unknown value, the result of any comparison with a null value is also unknown.
    https://support.office.com/en-us/art...2-e9223117d6bd

  9. #9
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2016
    Posts
    4,596

    Re: SQL where error

    Thanks for that CK76

  10. #10
    Registered User
    Join Date
    06-20-2018
    Location
    Toronto, Canada
    MS-Off Ver
    2016
    Posts
    5

    Re: SQL where error

    Basically, always check for NULL...

  11. #11
    Registered User
    Join Date
    07-12-2018
    Location
    Greater New Orleans, Louisiana, USA
    MS-Off Ver
    2010
    Posts
    23

    Re: SQL where error

    The problem with NULL in Access is that it is NEVER equal to anything - including another NULL. Yep, that's right: NULL <> NULL is true. Because it also involves a null, if you make this statement: bFlag = ( NULL <> NULL ) - it might not work since a null is involved in the expression. I.e. bFlag might end up null if it happened to be a variant data type. Nulls just kind of toss the proverbial monkey wrench into the works.

    So when you do a "NOT LIKE somthing-or-another" then ANYTHING that has a null in the field is NOT LIKE whatever. So that is where nulls creep in. The usual solution is to enclose the comparison field with the NZ function, as NZ([field],"") - which makes the NULL become an empty string. And THAT is not going to trip up on the string comparison.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1