Dsum using Multiple Criteria

[BHRIR]

This is being used in an update query
I am getting the sum if OLID = OLID
we recentltly added the the AND criterea and it is not working
Still getting the sum if OLID = OLID

This is the old Update to Statement that worked fine:
IIf(IsNull(DSum("[WorkLabor]","OrderLaborLine"," [OLID] =" & [OLID])),0,DSum("[WorkLabor]","OrderLaborLine"," [OLID] =" & [OLID]))

This is new one that gives same result as old:
IIf(IsNull(DSum("[WorkLabor]","OrderLaborLine"," [OLID] =" & [OLID] And [OrderLaborLine]![Additional]=No And [OrderLaborLine]![WorkChange]=No)),0,DSum("[WorkLabor]","OrderLaborLine"," [OLID] =" & [OLID] And [OrderLaborLine]![Additional]=No And [OrderLaborLine]![WorkChange]=No))

We beleive the "AND" critereas are not being acknowledged
Could someone clean this up for us so all the criterea is used to determined the sum?

 

[pr2-eugin]

Re: Dsum using Multiple Criterea

Hello BHRIR, A few comments..
1. DSum is a very powerful statement, if used right.. You have given a great workload on the statement and you have made it work twice using IIF statement, IIF(IsNull(DSum),0, DSum).. If it is not Null then the Workload on this is a bit more than normal..
2. We are here to help you find your way and learn; so someday you will understand what the function actually does instead of us actually working for you.. That does not mean we will not provide the code.. Next time it would be good if you request rather than (kinda) demand ??

Could someone clean this up for us so all the criterea is used to determined the sum?

statement like..

Can somebody help me to find any error in the code above?

That being said, you can use Nz() function to determine NULL Values in a statement and place a default value if it is NULL.. I would suggest you look into that.. The string is broken here.. AND is not concatenated back.. What is the Data Type of the Fields [Additional] and [WorkChange]?? If they are Yes/No Type better compare with constants True/-1 or False/0.. rather than using Yes/No..

Post back if you find something hard to follow..

 

[BHRIR]

Re: Dsum using Multiple Criterea

I apologize if our post seemed like a demand rather than a cry for help.
I think some are better at trying to find solutions than others; based on your reply, I will plead guilty as others.
This post came after reviewing many threads and our lack of experience. We are a small company just saving money developing a program to fit our needs. Some of it is fun and some of it is not.
It would also seem twice as difficult for someone seeking help in a forum to also be criticized for their improper posting for help.

That being said, I have changed the statement to read as this:

Nz(DSum("[WorkLabor]","OrderLaborLine"," [OID] =" & [Order]![OID] And [OrderLaborLine]![Additional]=0 And [OrderLaborLine]![WorkChange]=0))

When you select run:
You are prompted to add parameter values for both [Additional] and [WorkChange]
Ignoring this or entering “0” you get the total for ALL WorkLabor in the table OrderLaborLine
Remove the additional criteria, [Additional] and [WorkChange], and you get the total for the ALL WorkLabor in the table OrderLaborLine where OID = OID.

If the there is a question of what we are trying to do, I hope that below will explain:
In the table OrderLineLabor
There is work that was originally ordered under Hourly, Contract or Service Call
When the Order becomes active, there are many times that either a work change is implemented or additional work is added to the Order. We would like separate totals for original work, additional work and work change.
The statement above is only supposed to sum work that is not additional or change work.
In the table OrderLineLabor, each record is one of the selections; hourly, contract, service call, additional or work change. If one is selected as yes all the others are no

We would appreciate help with this to avoid making additional tables to do what we believe an update query can do.

 

[nanscombe]

You end the string after '[OID] =' to use the value in [Order]![OID] but you don't place the rest of the parameter in a string. Try ...

Nz(DSum("[WorkLabor]","OrderLaborLine"," [OID] =" & [Order]![OID] & " And [OrderLaborLine]![Additional]=0 And [OrderLaborLine]![WorkChange]=0"))