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.
Bookmarks