Ajax -- thank you for the additional feedback. I certainly do NOT ignore someone's recommendation. If I overlooked one of yours, I probably was overwhelmed w/ the design.
Now, I had to mimic the existing/working process for a similar scenario. Here how, I'm not querying on a field that needs to obtain a value from the listbox. Instead, I need to add the listbox value onto a field with an alias.
Right now, this process results in another eye-brow raiser.
Below is the correct SQL (before VBA rewrote it):
Code:
SELECT [CFT] & " | " & [CFT_Description] AS CFTConc, T99_Lookup_RankTitle.SortIDGroupOther, NCode_Group([N_Code]) AS NCode_Group, T01_Billets.RA_BIN, T01_Billets.RA_Billet_Title, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD
FROM (T01_StaffMembers LEFT JOIN T99_Lookup_RankTitle ON T01_StaffMembers.All_RankTitle = T99_Lookup_RankTitle.RankTitle) RIGHT JOIN (T11_CrossFunctionalTeam INNER JOIN (T01_Organization RIGHT JOIN ((T01_Billets LEFT JOIN T00_JunctionTable_OBS ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) INNER JOIN T00_JunctionTable_BCFT ON T01_Billets.BilletIDpk = T00_JunctionTable_BCFT.BilletIDfk) ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) ON T11_CrossFunctionalTeam.CFTIDpk = T00_JunctionTable_BCFT.CFTIDfk) ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk
GROUP BY [CFT] & " | " & [CFT_Description], T99_Lookup_RankTitle.SortIDGroupOther, NCode_Group([N_Code]), T01_Billets.RA_BIN, T01_Billets.RA_Billet_Title, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD
HAVING (((NCode_Group([N_Code]))="N1"))
ORDER BY [CFT] & " | " & [CFT_Description], T99_Lookup_RankTitle.SortIDGroupOther, T01_StaffMembers.All_LastName;
The SQL in the VBA looks like this:
Code:
'Build the new SQL statement incorporating the string
strSQL = "SELECT [CFT] & ' | ' & [CFT_Description] AS CFTConc, T99_Lookup_RankTitle.SortIDGroupOther, NCode_Group([N_Code]) AS NCode_Group, " & _
"T01_Billets.RA_BIN, T01_Billets.RA_Billet_Title, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD " & _
"FROM (T01_StaffMembers LEFT JOIN T99_Lookup_RankTitle ON T01_StaffMembers.All_RankTitle = T99_Lookup_RankTitle.RankTitle) " & _
"RIGHT JOIN (T11_CrossFunctionalTeam INNER JOIN (T01_Organization RIGHT JOIN ((T01_Billets LEFT JOIN T00_JunctionTable_OBS " & _
"ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) INNER JOIN T00_JunctionTable_BCFT ON T01_Billets.BilletIDpk = T00_JunctionTable_BCFT.BilletIDfk) " & _
"ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) ON T11_CrossFunctionalTeam.CFTIDpk = T00_JunctionTable_BCFT.CFTIDfk) " & _
"ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk GROUP BY [CFT] & ' | ' & [CFT_Description], T99_Lookup_RankTitle.SortIDGroupOther, " & _
"NCode_Group([N_Code]), T01_Billets.RA_BIN, T01_Billets.RA_Billet_Title, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD " & _
"" & "HAVING " & strCriteria & " " & _
"ORDER BY [CFT] & ' | ' & [CFT_Description], T99_Lookup_RankTitle.SortIDGroupOther, T01_StaffMembers.All_LastName;"
And it results in the following output:
Code:
SELECT [CFT] & ' | ' & [CFT_Description] AS CFTConc, T99_Lookup_RankTitle.SortIDGroupOther, NCode_Group([N_Code]) AS NCode_Group, T01_Billets.RA_BIN, T01_Billets.RA_Billet_Title, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD
FROM (T01_StaffMembers LEFT JOIN T99_Lookup_RankTitle ON T01_StaffMembers.All_RankTitle = T99_Lookup_RankTitle.RankTitle) RIGHT JOIN (T11_CrossFunctionalTeam INNER JOIN (T01_Organization RIGHT JOIN ((T01_Billets LEFT JOIN T00_JunctionTable_OBS ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) INNER JOIN T00_JunctionTable_BCFT ON T01_Billets.BilletIDpk = T00_JunctionTable_BCFT.BilletIDfk) ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) ON T11_CrossFunctionalTeam.CFTIDpk = T00_JunctionTable_BCFT.CFTIDfk) ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk
GROUP BY [CFT] & ' | ' & [CFT_Description], T99_Lookup_RankTitle.SortIDGroupOther, NCode_Group([N_Code]), T01_Billets.RA_BIN, T01_Billets.RA_Billet_Title, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD
HAVING ((([Q201_CFT_Participation_Report_Ncode_Gonzales_RPT].[NCode_Group])='N01'))
ORDER BY [CFT] & ' | ' & [CFT_Description], T99_Lookup_RankTitle.SortIDGroupOther, T01_StaffMembers.All_LastName;
Please see attached JPG for details. How should the strSQL be rewritten so that the output yields the original SQL (top image)?