Dsum giving a wrong amount

cookitup

Registered User.
Local time
Today, 12:49
Joined
Apr 4, 2012
Messages
33
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!
 
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"
 
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!
 
It's possible it's is a zero length string rather than null. Try adding

Or [Bid Number]=''
 
That is now returning an error. I have enclosed an attachment on how the report looks, I hope that helps
 

Attachments

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.
 
=DCount("[Bid Number]","FFP/T&M","[Bid Number]=")
The other formulas are working great! this is the only one.
 
I said to try adding it:

Code:
=DCount("[Bid Number]","FFP/T&M","[Bid Number] is null Or [Bid Number]=''")
 
And you think it should be greater than that? Can you post the db here, or a representative sample?
 
Oh, and try this too:

=DCount("*","FFP/T&M","[Bid Number] is null Or [Bid Number]=''")
 
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 !
 
No problem. I forgot that when you specify a field name, it doesn't count records with Null in that field.
 
I have enclosed an attachment on how the report looks,
 

Users who are viewing this thread

Back
Top Bottom