Coding for Multiple Conditions in Domain Aggregate Functions

[bhujanga]

I'm trying to make a control on a form dependent on a DSUM value and cannot seem to get the code right for multiple conditions.
If I code this:
=DSum("[grams Active]","Extractions","[Retired] = -1")
It works fine.

If I code this:
=DSum("[grams Active]","Extractions","[Extraction Type] = 'Med (Clean)'")
It also works fine.

But if I try to use both conditions like this:
=DSum("[grams Active]","Extractions","[Extraction Type] = 'THC-Med (Clean)' and [Retired] = -1")
It returns no value.
It doesn't report any kind of problem, the control just goes blank. (There are qualifying records and none that have nulls in the [grams active] field.)
Is there something wrong with this syntax? I can't find any examples on line that have "and"s or "or"s in them.
Thanks.

 

[dhookom]

'THC-Med (Clean)' is not the same as 'Med (Clean)'


Duane
Hook'D on Access
MS Access MVP

 

[bhujanga]

True, but that's not the problem. I just inadvertently mixed that up when I was putting together the question (I'm trying to do a similar thing for various Extraction Types. In the code I have tried taking the whole statement and deleting one half or the other and regardless of which half I delete it then works. I've also tried it with other conditions, and no matter what I do as soon as I try combining conditions it fails, so it would seem something is amiss with that syntax.
Thanks.

 

[dhookom]

Try create a query that mimics your DLookup() with SQL like:

SELECT Sum([grams Active]) As TheSum FROM Extractions WHERE [Extraction Type] = "THC-Med (Clean)" and [Retired] = -1

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Try

([Extraction Type] = 'THC-Med (Clean)') AND ([Retired] = -1)

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[bhujanga]

Thanks for your help. Oddly, I tried putting the parenthesis into one of the fields and upon doing that all of the other ones suddenly populated also. Then I took the parenthesis back out and it all continued to work with the original syntax. Very illogical.