Count two fields based on Date in a Query 2

[Comp2008]

I'm trying to Count data in a Query Combining two different fields based on the “Date” the product was sold, and for some reason I get a wrong data. When I use only this expression, It works fine:
Code:
Count(IIf([BDC Info Table].[Sold Date]>=#11/1/2008#,0)) AS [CountNovSold]

But when I want to Count another field based on the same Date.
Code:
Count(IIf([BDC Info Table].[Status]="Sold Not Set",0) And (IIf([BDC Info Table].[Sold Date]>=#11/1/2008#,0))) AS [CountOfSold Not Set]


It gives me a wrong data. Apparently, It doesn’t recognize the exact Date.
Any Help would be greatly appreciated

 

[SkipVought]

Hi,

Check your IIF statements. The FORM of the statment is...

IIF([i]expression,TRUE Result,FALSE Result[/i])

You fail to include FALSE results.

Try this...

IIf([BDC Info Table].[Status]="Sold Not Set",0, IIf([BDC Info Table].[Sold Date]>=#11/1/2008#,0,[b][unspecified result][/b]))

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Comp2008]

Your logic makes sense. I tried It and still doesn’t give me an exact data. The syntax that I’m using for Ms Access in a Query is:
Code:
Count(IIf([BDC Info Table].[Status]="Sold Not Set",0,IIf([BDC Info Table].[Sold Date]>=#11/1/2008#,0))) AS [CountOfSoldNotSet]

Am I doing something wrong?

Comp2008

 

[dhookom]

Count will count all non-null values. If you want to count the number of records where Status = "Sold Not Set" and the Sold Date >=11/1/2008 then, you could use:

Abs(Sum( [BDC Info Table].[Status]="Sold Not Set" And [BDC Info Table].[Sold Date]>=#11/1/2008# )) AS [CountOfSold Not Set]

Duane
Hook'D on Access
MS Access MVP

 

[Comp2008]

Thank you dhookom It works perfectly. In fact, I used that syntax for other fields too and I get the right totals.
I appreciate it

Comp2008

 

[Comp2008]

Following the same line of thinking, I’m trying to subtract two expressions using the code I got so far.
(“New” – “Sold Not Set”) Based on the same [Sold Date] range for both fields.
Code:
Abs(Sum([BDC Info Table].[Pre-Owned or New]="New" And [BDC Info Table].[Sold Date]<=#10/31/2008#) - Abs(Sum([BDC Info Table].[Status]="Sold Not Set" And [BDC Info Table].[Sold Date]<#10/31/2008#))) AS [CountOfNewSold]

But, It doesn’t retrieve the right data. Is something wrong with this code?
Thank you in advance
Comp2008

 

[dhookom]

I don't see anything wrong with your expression or results. Actually I don't even see your data or results.

Duane
Hook'D on Access
MS Access MVP

 

[Comp2008]

Thank you for your input. I just wanted to make sure if my expression was correct. After you assured me that it was okay, I looked the error on different direction and I found it. Thanks you again. I really appreciate it.

Comp2008