Solved DSUM with multiple criteria....

[TajikBoy]

Hi Guys,

Trying to sum fields based on a ID from a form, and a text field, fixed - my version is below - what am I doing wrong?

=DSum([total],[qrybudget],"[ProjectName] = " & [me].[ID] & " And ctrBudgetType = 'Seed'")

ProjectName is a number field, and ctrBudgetType is a text.....

Thanks in advance,

 

[arnelgp]

=DSum("total","qrybudget","[ProjectName] = " & [me].[ID] & " And ctrBudgetType = 'Seed'")

 

[TajikBoy]

=DSum("total","qrybudget","[ProjectName] = " & [me].[ID] & " And ctrBudgetType = 'Seed'")

Thanks but no sugar yet...Filed comes up as #Name?

 

[TajikBoy]

Thanks but no sugar yet...Filed comes up as #Name?

By the way, Total is a calculated field in the query, would that make any difference?

 

[arnelgp]

that's not a problem.
check each spelling if it does exists on qryBudget.

 

[TajikBoy]

I can't see any misspelled field names, and query is working as it should be - Dsum should give total of 2 seeds, maybe one day

 

[arnelgp]

maybe add square brackets.

=DSum("[total]","[qrybudget]","[ProjectName] = " & [me].[ID] & " And [ctrBudgetType] = 'Seed'")

 

[TajikBoy]

maybe add square brackets.

=DSum("[total]","[qrybudget]","[ProjectName] = " & [me].[ID] & " And [ctrBudgetType] = 'Seed'")

Nope, still no sugar....still #Name?

 

[arnelgp]

maybe rename the Textbox to another name, ie, txtValue (by just prefixing with txt)

 

[MajP]

Instead of the Dsum can you just make an aggregate query
Group by project name, sum of total, where type = seed. Then link your query by project. If you want to only show seeds do an inner join, it you want all then do an outer join.

 

[Gasman]

Put the criteria into a string variable and Debug.Print that to see what you produce.

 

[TajikBoy]

Thanks guys, as I didn't get anywhere with multiple criteria, I built in the fixed element into the query and Dsum'ed with the projectName - now it works, not the ideal way but problem solved for now

 

[Isaac]

Either you're typing/pasting it wrong, or your object names are misspelled, because the original response from Arnel is correct.