NEED HELP WITH DSUM()

[calihiker]

I added additional criteria to my text box and I can't get it to work. Please Help!!!

Here's what I had before and it worked fine..

=Val(DSum("[QTY]","2000 to 2002 Gifts All Drops (Non Housefile Buyers)","[Year] = " & 2002))


Here's what I have now...

=Val(DSum("[QTY]","2000 to 2002 Gifts All Drops (Non Housefile Buyers)","[Year] = " & 2002 And [Sales/BK$]>=[txtMinimum_S/BK$].[Text]))

Any suggestions would be great, I'm not too familiar with sytax when adding more criteria for domain functions...

 

[TonyJollans]

Hi calhiker,

I think it's a quotes problem again. The criteria passed to the Dsum Function needs to be a string and the only thing you want outside quotes is something which needs substituting in the building of that string. On your working original the 2002 should really have been inside the quotes but because it is numeric it made no difference. You should really have had ..

=Val(DSum("[QTY]","2000 to 2002 Gifts All Drops (Non Housefile Buyers)",

"[Year] = 2002"

))

The same rule applies on the new improved criteria. All that needs to be outside the quotes is the [txtMinimum_S/BK$].[Text] so you want to have ..

=Val(DSum("[QTY]","2000 to 2002 Gifts All Drops (Non Housefile Buyers)","[Year] = 2002 And [Sales/BK$]>=

" & [txtMinimum_S/BK$].[Text]

))

Enjoy,
Tony

 

[calihiker]

I pasted the formula into the control source box and ran the form, but I got "#Error" in it? It didn't give me a syntax error, what would cause that?

Andre

 

[TonyJollans]

Hi Andre,

#Error is a general error so it's often worth just playing around a little and seeing what has an effect. Is [txtMinimum_S/BK$] null? Or you could try changing [txtMinimum_S/BK$].[Text] to just [txtMinimum_S/BK$]. Hard to say for sure what might be causing it.

Enjoy,
Tony

 

[calihiker]

Thanks, I took off the .text and it works fine now.

 

[calihiker]

I have a similar problem now, how do you compare a field in a query to null? I need to count how many nulls are in a field in a table..

 

[calihiker]

Using Dcount()

 

[TonyJollans]

Hi Andre,

The arguments passed to the various domain functions are elements which are effectively combined to make an SQL statement. So, (and please don't cut and paste because I have left off some quotes and continuation marks for clarity) in the first example, your DSum ..

DSum("[QTY]"
     "2000 to 2002 Gifts All Drops (Non Housefile Buyers)"
     "[Year] = 2002 And [Sales/BK$]>= [txtMinimum_S/BK$])

.. is equivalent to ..

SELECT Sum([QTY])
FROM   [2000 to 2002 Gifts All Drops (Non Housefile Buyers)] 
WHERE  [Year] = 2002 And [Sales/BK$]>= [txtMinimum_S/BK$]

Now, your DCount. What you want in SQL is ..

SELECT Count(*)
FROM   YourTable
WHERE  YourColumn Is Null

.. so, as a domain function, you use

DCount("*",
       "YourTable",
       "YourColumn Is Null")

Enjoy,
Tony