Dsum giving a wrong amount

[cookitup]

I am trying to sum the amount of Jobs started that are quoted vs the jobs that are not quoted. I am using the following code:
=DSum("[Quote Amount]","FFP/T&M","[Bid Number]> 'Not Null' ")
This returns an amount for the "Not Quoted", But it returns a number that is $7214 off from the correct amount.
The "Quoted" appears to return a correct amount! this is the code I am using for it:
=DSum("[Quote Amount]","FFP/T&M","[Bid Number]And 'Null' ")
Someone please help me figure out why I am getting a wrong amount! it seems that the syntax is correct, and should be simple!

 

[pbaldy]

If you're saying that the Bid Number field would be Null or not, the correct tests would be

"[Bid Number] Is Null"

"[Bid Number] Is Not Null"

 

[cookitup]

I Have a new problem with a different field.
I am trying to count the number of bids that do not have a bid number. Here id the syntax i have been using:
=DCount("[Bid Number]","FFP/T&M","[Bid Number] is null")
it is returnig a zero count. Bid number is a text field. Please help!

 

[pbaldy]

It's possible it's is a zero length string rather than null. Try adding

Or [Bid Number]=''

 

[cookitup]

That is now returning an error. I have enclosed an attachment on how the report looks, I hope that helps

 

[pbaldy]

What does the entire formula look like now? The others worked okay? I was thinking the table name might need to be bracketed due to the inadvisable symbols in it, but if the others worked then that's not it.

 

[cookitup]

=DCount("[Bid Number]","FFP/T&M","[Bid Number]=")
The other formulas are working great! this is the only one.

 

[pbaldy]

I said to try adding it:

=DCount("[Bid Number]","FFP/T&M","[Bid Number] is null Or [Bid Number]=''")

 

[cookitup]

It is now returning a zero

 

[pbaldy]

And you think it should be greater than that? Can you post the db here, or a representative sample?

 

[pbaldy]

Oh, and try this too:

=DCount("*","FFP/T&M","[Bid Number] is null Or [Bid Number]=''")

 

[cookitup]

Paul, That did it, Thank you so much for all your help. As you can tell, I am very new at this! I really appreciate it !

 

[pbaldy]

No problem. I forgot that when you specify a field name, it doesn't count records with Null in that field.

 

[kaiwei]

I have enclosed an attachment on how the report looks,