Dsum and Like with Len

Posted on 2011-10-30
Last Modified: 2012-05-12

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.  
Question by:pdvsa
    LVL 39

    Accepted Solution

    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.
    LVL 33

    Assisted Solution

    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.


    Author Closing Comment

    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.
    LVL 33

    Expert Comment

    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.:)

    Author Comment

    Ok thanks for the tip.  You are right in that i "just need to get something working now".  

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now