Dsum Question

When using Dsum, it is necessary to include the same conditions in the query qryLCIssued_Sum_Import-CSM_2?   It seems as though I do.  I ask this because within the qry I already have the condition of [Actual Status]<>"GEC" but Dsum seems to ignore this condtion within the qry which means I have to reference the same condition in Dsum?

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


oh and btw the condition syntax is not correct at least I dont think it is because I get a #name and have a question out here: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27422785.html
pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
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)
0
Dale FyeCommented:
No, it is not necessary to include conditions that are expressed in the underlying query within the DSUM() domain function call.

Can you post the SQL for "qryLCIssued_Sum_Import-CSM_2"

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
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"));

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pdvsaProject financeAuthor Commented:
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"));
0
Dale FyeCommented:
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] & "*"

0
pdvsaProject financeAuthor Commented:
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.  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.