June 26, 2018 at 6:53 am
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.
June 26, 2018 at 7:23 am
jdasilva - Tuesday, June 26, 2018 6:53 AMSo 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.
Is this for Access or SQL Server?
What rows are you missing? Are there NULLs in those rows? Why not use simply <> instead of NOT and LIKE?
June 26, 2018 at 7:33 am
This script is built in VBA for use in access 2016.
honestly, SQL is not my first language. The base for the SQL statement was most likely an access query converted to SQL. So you think it may be better to use = or <> instead of Like and Not...Like? I will give that a try.
A quick question (since you replied back so quick) about the other LIKEs above. I cannot remember why I put them (or if it was how I found it), but are the N' s needed? I had it with the last clause, but took it out to see if that was something causing the issue. In any case, will try the <> just now and let you know how it goes. Thanks for the quick reply.
June 26, 2018 at 8:27 am
jdasilva - Tuesday, June 26, 2018 7:33 AMThis script is built in VBA for use in access 2016.honestly, SQL is not my first language. The base for the SQL statement was most likely an access query converted to SQL. So you think it may be better to use = or <> instead of Like and Not...Like? I will give that a try.
A quick question (since you replied back so quick) about the other LIKEs above. I cannot remember why I put them (or if it was how I found it), but are the N' s needed? I had it with the last clause, but took it out to see if that was something causing the issue. In any case, will try the <> just now and let you know how it goes. Thanks for the quick reply.
Here is my critique of your code based T-SQL:
SELECT DISTINCT TOP (100) PERCENT -- No reason for the TOP (100) PERCENT, you want all the data. Why is there a DISTINCT?
[sl].[LocCode]
, [sl].[AutoNumber]
, [sl].[Floor]
, [sl].[LastName]
, [sl].[ShowName]
, [sl].[FirstName]
, LEN([sl].[Extension]) AS [ExtLen]
, [sl].[Department]
, [sl].[FirstAid]
, [sl].[FireWarden]
, [sl].[SpecialStatus]
, [oc].[Address1]
, [oc].[Address2]
, [oc].[City]
, [oc].[StateProv]
, [oc].[PostalZip]
, [oc].[PhoneNo]
, [oc].[FaxNo]
, [sl].[TempFlex]
, [oc].[SharepointPhone]
, [oc].[ExtraText]
, LEFT([sl].[LastName], 1) AS [Heads]
, '' AS [Dummy]
, [sl].[Extension]
, [sl].[10DigitPhone]
, '' AS [OfficeNumber]
, '' AS [Column]
, IIF([sl].[LastName] = [sl].[ShowName], [sl].[LastName] + ' ' + [sl].[ShowName], [sl].[LastName] + ', ' + [sl].[ShowName]) AS [outText]
FROM
[dbo].[StaffListings] AS [sl] -- Added table alaises and used those in the select, on, and where clauses. Three and four part naming convention has been deprecated
INNER JOIN [dbo].[OfficeCodes] AS [oc] -- Added table alaises and used those in the select, on, and where clauses. Three and four part naming convention has been deprecated
ON [sl].[LocCode] = [oc].[LocCode]
WHERE
(
(([sl].[LocCode]) = N'TOR') -- (([sl].[LocCode]) LIKE N'TOR') -- None of these are using wildcards
AND (NOT ([sl].[LastName] = N'0ConfRoom%')) -- (NOT ([sl].[LastName] LIKE N'0ConfRoom%')) -- None of these are using wildcards
AND (NOT ([sl].[LastName] = N'0RoomExtra')) -- (NOT ([sl].[LastName] LIKE N'0RoomExtra')) -- None of these are using wildcards
AND (NOT ([sl].[LastName] = N'0PhoneExtra')) -- (NOT ([sl].[LastName] LIKE N'0PhoneExtra')) -- None of these are using wildcards
AND (NOT ([sl].[LastName] = N'0Emerg')) -- (NOT ([sl].[LastName] LIKE N'0Emerg')) -- None of these are using wildcards
AND
(
(LEN([sl].[Extension]) > 0)
OR (LEN([sl].[10DigitPhone]) > 1)
)
AND (NOT ([sl].[SpecialStatus] = '+++')) -- (NOT ([sl].[SpecialStatus] LIKE '+++')) -- Not using wildcards
)
ORDER BY
[outText]; -- What table is this column in, need to use the table alais to identify the table
June 26, 2018 at 8:43 am
jdasilva - Tuesday, June 26, 2018 7:33 AMThis script is built in VBA for use in access 2016.honestly, SQL is not my first language. The base for the SQL statement was most likely an access query converted to SQL. So you think it may be better to use = or <> instead of Like and Not...Like? I will give that a try.
A quick question (since you replied back so quick) about the other LIKEs above. I cannot remember why I put them (or if it was how I found it), but are the N' s needed? I had it with the last clause, but took it out to see if that was something causing the issue. In any case, will try the <> just now and let you know how it goes. Thanks for the quick reply.
LIKE is used for wildcards. If you're not using wildcards, there's no reason for using LIKE. In fact, it could produce unexpected implicit conversions that can cause problems.
If you are dealing with NULLs, you need to consider that you have 3-valued logic (true, false, unknown). Read a bit about it and how the truth tables differ from 2-valued logic.
Ns before a string are defining the string as unicode. You should avoid them if they're not needed because that can also cause undesired implicit conversions that would prevent the use of indexes.
June 26, 2018 at 2:29 pm
Lynn Pettis - Tuesday, June 26, 2018 8:27 AMHere is my critique of your code based T-SQL:
AND (NOT ([sl].[LastName] = N'0ConfRoom%')) -- (NOT ([sl].[LastName] LIKE N'0ConfRoom%')) -- None of these are using wildcards
I think you were a bit overzealous here Lynn. This criteria is using a wildcard.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 26, 2018 at 2:39 pm
drew.allen - Tuesday, June 26, 2018 2:29 PMLynn Pettis - Tuesday, June 26, 2018 8:27 AMHere is my critique of your code based T-SQL:
AND (NOT ([sl].[LastName] = N'0ConfRoom%')) -- (NOT ([sl].[LastName] LIKE N'0ConfRoom%')) -- None of these are using wildcardsI think you were a bit overzealous here Lynn. This criteria is using a wildcard.
Drew
Oh, is that the ACCESS wildcard? Never coded in Access.
June 26, 2018 at 2:40 pm
Lynn Pettis - Tuesday, June 26, 2018 2:39 PMdrew.allen - Tuesday, June 26, 2018 2:29 PMLynn Pettis - Tuesday, June 26, 2018 8:27 AMHere is my critique of your code based T-SQL:
AND (NOT ([sl].[LastName] = N'0ConfRoom%')) -- (NOT ([sl].[LastName] LIKE N'0ConfRoom%')) -- None of these are using wildcardsI think you were a bit overzealous here Lynn. This criteria is using a wildcard.
Drew
Oh, is that the ACCESS wildcard? Never coded in Access.
Nevermind, missed the %. Sorry, my mistake, slap my wrist.
June 27, 2018 at 7:37 am
Well, there are a few with wildcards, but not all, so knowing the difference is a good thing. Unfortunately, using <> and = gives the same results. Well, really the LIKE/= gives the proper results. The problem is with the NOT...LIKE/<>.
Now the NULL issue may be the case. For instance, there are about 17 names in the 'last name begins with A' section. I have marked 2 with the special status of +++. So in theory, if I exclude (<>) +++, then I should see about 15. I see 3. If I add something like 'test' to a few others in this section and run it again, they show up.
Ok, was testing while typing this, and the NULL check is what was missing. Funny, in many of the records , specialstatus is blank. But for some reason, some are seen as NULL and others not. I have tried entering data, and then removing it, but for some reason, some are seen as NULL, and others not. In any case, this was the fix:
If Me.typeCombo.Value = "Standard" Then
sqlSelect = sqlSelect & "AND ((dbo.StaffListings.SpecialStatus <> '+++') OR (dbo.StaffListings.SpecialStatus IS NULL))"
Else
sqlSelect = sqlSelect & "AND ((dbo.StaffListings.SpecialStatus = '+++'))"
End If
sqlSelect = sqlSelect & ")"
Thanks for fighting with this with me. I have learned a few better habits from this. Thanks!
June 27, 2018 at 7:57 am
jdasilva - Wednesday, June 27, 2018 7:37 AM
Thanks for fighting with this with me. I have learned a few better habits from this. Thanks!
Thanks for the feedback. Dealing with NULLs and three-valued logic is something that newbies struggle with. But once you understand it, it becomes very simple and obvious. It's good that you have learned something from this.
June 28, 2018 at 6:35 am
jdasilva - Wednesday, June 27, 2018 7:37 AMWell, there are a few with wildcards, but not all, so knowing the difference is a good thing. Unfortunately, using <> and = gives the same results. Well, really the LIKE/= gives the proper results. The problem is with the NOT...LIKE/<>.Now the NULL issue may be the case. For instance, there are about 17 names in the 'last name begins with A' section. I have marked 2 with the special status of +++. So in theory, if I exclude (<>) +++, then I should see about 15. I see 3. If I add something like 'test' to a few others in this section and run it again, they show up.
Ok, was testing while typing this, and the NULL check is what was missing. Funny, in many of the records , specialstatus is blank. But for some reason, some are seen as NULL and others not. I have tried entering data, and then removing it, but for some reason, some are seen as NULL, and others not. In any case, this was the fix:
If Me.typeCombo.Value = "Standard" Then
sqlSelect = sqlSelect & "AND ((dbo.StaffListings.SpecialStatus <> '+++') OR (dbo.StaffListings.SpecialStatus IS NULL))"
Else
sqlSelect = sqlSelect & "AND ((dbo.StaffListings.SpecialStatus = '+++'))"
End If
sqlSelect = sqlSelect & ")"Thanks for fighting with this with me. I have learned a few better habits from this. Thanks!
The problem is in Access Nulls aren't visually distinct from an empty string. I've used Access since 1.0 and that was probably the biggest reason I went to the "no nulls ANYWHERE" camp. 🙂 Nulls are evil. At least in SQL Server nulls are visually distinct.
June 28, 2018 at 6:59 am
Yes. The odd thing for me was that this field is almost never used (in the past), but some are NULL and some are not, even though all such entries are blank. Lesson learned, always check for NULL...
Just to finalize out. That initial flag was to get things working. Realistically, I would like the data entry person to put something more descriptive in the status. Site this flag is for those not in the office (working on site) I chose to do this :
'Seperate out SpecialStatus value
If Me.typeCombo.Value = "Standard" Then
'sqlSelect = sqlSelect & "AND ((dbo.StaffListings.SpecialStatus <> '+++') OR (dbo.StaffListings.SpecialStatus IS NULL))"
sqlSelect = sqlSelect & "AND (NOT(dbo.StaffListings.SpecialStatus LIKE N'%@%') OR (dbo.StaffListings.SpecialStatus IS NULL))"
Else
sqlSelect = sqlSelect & "AND ((dbo.StaffListings.SpecialStatus LIKE N'%@%'))"
'sqlSelect = sqlSelect & "AND ((dbo.StaffListings.SpecialStatus = '+++'))"
End If
So with this, they could put @ZZZ to indicate that the staff memeber is at the site coded by ZZZ. I don't know if I stated the idea behind this, but the reasoning is that our office is expanding, and there are quite a few people on our phone list that are not in this office. This allows me to cull them out and allow them to be printed on a separate report.
Thanks again to all.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply