Dsum and Like with Len

Hello,

I need to modify the below for I guess LEN on the LIKE portion of the Dsum.
I think I will need to look at the first 6 characters of [txtBankName2] instead of like * & txtBankName2 & *

=Nz(DSum("[SumOfApproved Amount In USD Equivalent]","qryLCIssued_Sum_Import-CSM_2","[Guarantor Description] Like '*" & [txtBankName2] & "*'"),0)

I think it would be best to post the db.  It opens to the form.  I need a Dsum per [txtBankName2].  I can not Dsum on the combobox because I am using a download of company data and bank names dont match so I need a like on the first few characters.  

 I supose LEN will look at only the first few characters and is the solution I am after but maybe there is a better solution if it is clear what I am after.  You can see that the Dsum is returning $0 and this is not correct.  

thank you for the help.  Please ask if something is not clear.  
DatabaseNew.accdb
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.

als315Commented:
You can try:

=Nz(DSum("[SumOfApproved Amount In USD Equivalent]","qryLCIssued_Sum_Import-CSM_2","[Guarantor Description] Like '*" & left([txtBankName2],6) & "*'"),0)

But I see Guarantor Code field in imported table. May be you can add this field to your table tblBanks and use it in dlookup?
I can also recommend also use code (BankID) in table tblBankFacilityAmts if it is possible.
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
NorieVBA ExpertCommented:
There are no matches for the records that appear in the form and I don't think Len or Like will help.

Have you considered 'cleansing' the data a bit?

For example changing all occurences of BANCO SANTANDER NEW YORK to BANCO SANTANDER USA.

You can do that by running an update query like this on table Import-CSM:

UPDATE [Import-CSM] SET [Import-CSM].[Guarantor Description] = "BANCO SANTANDER USA"
WHERE ((([Import-CSM].[Guarantor Description]) Like "*BANCO SANTANDER*"));

When I do that and the reopen the form the value returned by the DSum for the first record is $8,037,399.06.

I ran a similar query for DNB Nor Bank and the result was $73,973,658.98.

For JP Morgan I get $20,771,873.28.


0
pdvsaProject financeAuthor Commented:
als:  that was the solution.  It worked perfectly and I verified the amounts.  Thanks for the idea...I might use that but I really did not want to add another field to the db for Guarantor Code but that might be the best way instead of using LEFT like you posed.  

Imnorie:  thanks for the idea as well.   I can update the data with your suggestion.

I am not sure which way I will do it though.  This companies db that they use is an absolute disaster and I am importing data into Access to make it more manageable.  If an expert would see the db they use I think you would blow a fuse.  Many of the tables are not even linked in this primitive db they use.  it is the worst and the only thing it does well is decrease efficiency.
0
NorieVBA ExpertCommented:
You really should update the data or you'll probably run into problems like this again.

Using Len and Like etc is something you shouldn't really be doing in a DSum.

I suppose the important thing is to get something that works though.:)
0
pdvsaProject financeAuthor Commented:
Ok thanks for the tip.  You are right in that i "just need to get something working now".  
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.