Solved Need Help Ms access with Dsum (1 Viewer)

theinviter

Registered User.
Local time
Today, 09:22
Joined
Aug 14, 2014
Messages
168
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
 

Attachments

  • Database2.accdb
    4.2 MB · Views: 152

June7

AWF VIP
Local time
Today, 08:22
Joined
Mar 9, 2014
Messages
4,381
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] & "'")
 
Last edited:

theinviter

Registered User.
Local time
Today, 09:22
Joined
Aug 14, 2014
Messages
168
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

AWF VIP
Local time
Today, 08:22
Joined
Mar 9, 2014
Messages
4,381
Simple change. Open query and copy/paste my suggested expression. Same for textbox on form.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:22
Joined
Feb 19, 2002
Messages
36,340
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:22
Joined
Feb 19, 2002
Messages
36,340
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.
 

Users who are viewing this thread

Top Bottom