Dsum Question

try this


=Nz(DSum("[SumOfApproved Amount In USD Equivalent]","qryLCIssued_Sum_Import-CSM_2","[Guarantor Description] Like '*" & Left([txtBankName2],6) & "*'" And [Actual Status]<>'GEC'"),0)

fyed:  it is posted.  It is long.  

SELECT [Import-CSM].[Reference Number], [Import-CSM].[Approved Amount In USD Equivalent], Switch([Activity Code] Like "DH*","Onshore Houston",[Activity Code] Like "LA*","Onshore Claremont",[Activity Code] Like "GS*","Subsea",[Activity Code] Like "ES*","Engineering Offshore",[Activity Code] Like "KT*","KTI",[Activity Code] Like "MS*","Overhead Charges",[Activity Code] Like "TFP*","Offshore Fixed Platform",[Activity Code] Like "*TOF*","Offshore Finland",[Activity Code] Like "MX*","Onshore Mexico",[Activity Code] Like "DI*","What is DI???",True,"") AS MyBusUnit, Switch([Nature] Like "PB*","Performance Bond",[Nature] Like "APB*","Advance Payment GT",[Nature] Like "PC*","Parent Company GT - TUSA",[Nature] Like "Misc*","Miscellaneous",[Nature] Like "Surety*","Surety Bond",[Nature] Like "BB*","Bid Bond",[Guarantor Description] Like "*S.A.*","Head Office PCG",True,"") AS LCType, Count(Switch([Nature] Like "PB*","Performance Bond",[Nature] Like "APB*","Advance Payment GT",[Nature] Like "PC*","Parent Company GT",[Nature] Like "Misc*","Miscellaneous",[Nature] Like "Surety*","Surety Bond",[Nature] Like "BB*","Bid  Bond",True,"")) AS LCTypeCount, [Import-CSM].[Guarantor Description], [Import-CSM].[Actual Status], tblBanks.GuarantorCode

FROM tblBankFacilityAmts INNER JOIN ([Import-CSM] INNER JOIN tblBanks ON [Import-CSM].[Guarantor Code] = tblBanks.GuarantorCode) ON tblBankFacilityAmts.BankName_Facility = tblBanks.BankID

GROUP BY [Import-CSM].[Reference Number], [Import-CSM].[Approved Amount In USD Equivalent], Switch([Activity Code] Like "DH*","Onshore Houston",[Activity Code] Like "LA*","Onshore Claremont",[Activity Code] Like "GS*","Subsea",[Activity Code] Like "ES*","Engineering Offshore",[Activity Code] Like "KT*","KTI",[Activity Code] Like "MS*","Overhead Charges",[Activity Code] Like "TFP*","Offshore Fixed Platform",[Activity Code] Like "*TOF*","Offshore Finland",[Activity Code] Like "MX*","Onshore Mexico",[Activity Code] Like "DI*","What is DI???",True,""), Switch([Nature] Like "PB*","Performance Bond",[Nature] Like "APB*","Advance Payment GT",[Nature] Like "PC*","Parent Company GT - TUSA",[Nature] Like "Misc*","Miscellaneous",[Nature] Like "Surety*","Surety Bond",[Nature] Like "BB*","Bid Bond",[Guarantor Description] Like "*S.A.*","Head Office PCG",True,""), [Import-CSM].[Guarantor Description], [Import-CSM].[Actual Status], tblBanks.GuarantorCode

HAVING ((([Import-CSM].[Actual Status])<>"GEC"));

Select allOpen in new window

SELECT [Import-CSM].[Reference Number], [Import-CSM].[Approved Amount In USD Equivalent], Switch([Activity Code] Like "DH*","Onshore Houston",[Activity Code] Like "LA*","Onshore Claremont",[Activity Code] Like "GS*","Subsea",[Activity Code] Like "ES*","Engineering Offshore",[Activity Code] Like "KT*","KTI",[Activity Code] Like "MS*","Overhead Charges",[Activity Code] Like "TFP*","Offshore Fixed Platform",[Activity Code] Like "*TOF*","Offshore Finland",[Activity Code] Like "MX*","Onshore Mexico",[Activity Code] Like "DI*","What is DI???",True,"") AS MyBusUnit, Switch([Nature] Like "PB*","Performance Bond",[Nature] Like "APB*","Advance Payment GT",[Nature] Like "PC*","Parent Company GT - TUSA",[Nature] Like "Misc*","Miscellaneous",[Nature] Like "Surety*","Surety Bond",[Nature] Like "BB*","Bid Bond",[Guarantor Description] Like "*S.A.*","Head Office PCG",True,"") AS LCType, Count(Switch([Nature] Like "PB*","Performance Bond",[Nature] Like "APB*","Advance Payment GT",[Nature] Like "PC*","Parent Company GT",[Nature] Like "Misc*","Miscellaneous",[Nature] Like "Surety*","Surety Bond",[Nature] Like "BB*","Bid  Bond",True,"")) AS LCTypeCount, [Import-CSM].[Guarantor Description], [Import-CSM].[Actual Status], tblBanks.GuarantorCode
FROM tblBankFacilityAmts INNER JOIN ([Import-CSM] INNER JOIN tblBanks ON [Import-CSM].[Guarantor Code] = tblBanks.GuarantorCode) ON tblBankFacilityAmts.BankName_Facility = tblBanks.BankID

GROUP BY [Import-CSM].[Reference Number], [Import-CSM].[Approved Amount In USD Equivalent], Switch([Activity Code] Like "DH*","Onshore Houston",[Activity Code] Like "LA*","Onshore Claremont",[Activity Code] Like "GS*","Subsea",[Activity Code] Like "ES*","Engineering Offshore",[Activity Code] Like "KT*","KTI",[Activity Code] Like "MS*","Overhead Charges",[Activity Code] Like "TFP*","Offshore Fixed Platform",[Activity Code] Like "*TOF*","Offshore Finland",[Activity Code] Like "MX*","Onshore Mexico",[Activity Code] Like "DI*","What is DI???",True,""), Switch([Nature] Like "PB*","Performance Bond",[Nature] Like "APB*","Advance Payment GT",[Nature] Like "PC*","Parent Company GT - TUSA",[Nature] Like "Misc*","Miscellaneous",[Nature] Like "Surety*","Surety Bond",[Nature] Like "BB*","Bid Bond",[Guarantor Description] Like "*S.A.*","Head Office PCG",True,""), [Import-CSM].[Guarantor Description], [Import-CSM].[Actual Status], tblBanks.GuarantorCode

HAVING ((([Import-CSM].[Actual Status])<>"GEC"));

As I indicated above, you should not need to include the [Actual Status] <> "GEC" in the criteria string of your DSUM domain function call.

Although I frequently use the SWITCH function, doing so in this case could cause proplems when you add another Activity Code in the future.  If I were you, I would consider creating a new table (tbl_ActCodeLike) that contains two fields (LikeThis, UseThis) with values of:

LikeThis   UseThis
DH           Onshore Houston
LA            Onshore Claremont
GS            Subsea
ES            Engineer Offshore
KT            KTI

Then join that table to your [Import-CSM] table using a non-equi join.  This can only be done through the SQL view, but you can start out in the query design view by creating an INNER JOIN between the [Activity Code] field and the [LikeThis] field.  Then add the [UseThis] field to the query grid.  Because this is an INNER JOIN, if you ran the query immediately, it would return no records.

But if you then switch to the SQL view, you can change the join so that it looks like:

INNER JOIN [Import-CSM].[Activity Code] LIKE [tbl_ActCodeLike].[LikeThis] & "*"

fyed:  appreciate the insight.  thanks for the tip too.  I do have a table in the db now with those business units.  I am using a dl of data from their other db and trying to make sense of it by the use of that Switch.  It is an awful db and needs to be redone.  I am using Access instead.