Solved Need Help Ms access with Dsum

[theinviter]

Dear Guys
need help with D sum function.

I have a query and added extra field where i want to get the sum based on 2 text criteria (item code and company name)
i tried this :
DSum("[Link Value]","MRN","[Item Code]like""" & [Forms]![MRN Query]![Item_Code] & """ AND [Account Name] like """ & [Forms]![MRN Query]![Account_Name] & """")
but did get any result just got this number "4502" in all column.

so any help

 

[June7]

Instead of saving ItemCode and Clinic into MRN should be saving ID from Combine.

Strongly advise not to use spaces nor punctuation/special characters in naming convention.

Why are you including Combine in form RecordSource?

LIKE without wildcard can just be = sign.

Can use apostrophe instead of doubled quote marks.

Can't use Me. in textbox expressions. Me. is used only in VBA.

=DSum("[Link Value]","MRN","[Item Code]='" & [Item_Code] & "' AND [Account Name] = '" & [Account_Name] & "'")

In query:

Expr1: DSum("[Link Value]","MRN","[Item Code]='" & [MRN].[Item Code] & "' AND [Account Name] like '" & [MRN].[Account Name] & "'")

 

[theinviter]

Instead of saving ItemCode and Clinic into MRN should be saving ID from Combine.

Strongly advise not to use spaces nor punctuation/special characters in naming convention.

Why are you including Combine in form RecordSource?

LIKE without wildcard can just be = sign.

Can use apostrophe instead of doubled quote marks.

Can't use Me. in textbox expressions. Me. is used only in VBA.

=DSum("[Link Value]","MRN","[Item Code]='" & [Item_Code] & "' AND [Account Name] = '" & [Account_Name] & "'")

In query:

Expr1: DSum("[Link Value]","MRN","[Item Code]='" & [MRN].[Item Code] & "' AND [Account Name] like '" & [MRN].[Account Name] & "'")

thank for help.
can you please do the change in attached file, i tried but could not solve it.

 

[June7]

Simple change. Open query and copy/paste my suggested expression. Same for textbox on form.

 

[theinviter]

solved thank you

 

[Pat Hartman]

Just FYI, LIKE is a string operation used when you are searching for a PARTIAL STRING. It is not used with numbers or dates and is never used without a wild card. The downside of LIKE is that it frequently prevents the query engine from optimizing the query by using indexes. Therefore a query with Like in the criteria will most likely search RBAR (row by agonizing row). Index searches are way faster. So, unless you really do need to search for a partial string, do not use LIKE.

 

[June7]

Actually, I have used LIKE with numbers to solve an OP's issue.https://www.accessforums.net/showthread.php?t=85033

 

[Pat Hartman]

You're on your own when you use a string function with a numeric data type. I would recommend converting the number to a string first before using like so you at least know what you are matching. There are conversion issues but I can't find any recent links.