Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

Dsum and Like with Len


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.  
  • 2
  • 2
2 Solutions
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.
NorieData ProcessorCommented:
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.

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.
NorieData ProcessorCommented:
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.:)
pdvsaProject financeAuthor Commented:
Ok thanks for the tip.  You are right in that i "just need to get something working now".  

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now