count only those after a certain date

[Lila]

I am trying to count how many properties sold after a certain date. So far I
have the following in the "field" area of the query

sold: Count(IIf([Info_SaleDate]>#10/31/2003#,[Info_SaleDate],0))

....but it returns the total number of fields, not just the ones sold after
the given date.

Then I tried just putting the field name in the field area Info_SaleDate and
putting the date in the criteria

10/31/2003, but it returned some inexplicable number that was obviously

wrong. When the pound sign was in the criteria, the entire form was blank.

Help!

 

[Guest]

Is your [Info_SaleDate] field a datatype of datetime or a text field? Open
the table in design view to check.

I am trying to count how many properties sold after a certain date. So far I
have the following in the "field" area of the query

sold: Count(IIf([Info_SaleDate]>#10/31/2003#,[Info_SaleDate],0))

....but it returns the total number of fields, not just the ones sold after
the given date.

Then I tried just putting the field name in the field area Info_SaleDate and
putting the date in the criteria

10/31/2003, but it returned some inexplicable number that was obviously

wrong. When the pound sign was in the criteria, the entire form was blank.

Help!

 

[Lila]

It is Date/Time

Is your [Info_SaleDate] field a datatype of datetime or a text field? Open
the table in design view to check.

I am trying to count how many properties sold after a certain date. So far I
have the following in the "field" area of the query

sold: Count(IIf([Info_SaleDate]>#10/31/2003#,[Info_SaleDate],0))

....but it returns the total number of fields, not just the ones sold after
the given date.

Then I tried just putting the field name in the field area Info_SaleDate and
putting the date in the criteria

10/31/2003, but it returned some inexplicable number that was obviously

wrong. When the pound sign was in the criteria, the entire form was blank.

Help!

 

[John Spencer]

Count counts the presence of a value. It doesn't count nulls so, try this

Sold: Count(IIf([Info_SaleDate]>#10/31/2003#,[Info_SaleDate],NULL))

I am trying to count how many properties sold after a certain date. So far
I
have the following in the "field" area of the query

sold: Count(IIf([Info_SaleDate]>#10/31/2003#,[Info_SaleDate],0))

...but it returns the total number of fields, not just the ones sold after
the given date.

Then I tried just putting the field name in the field area Info_SaleDate
and
putting the date in the criteria

10/31/2003, but it returned some inexplicable number that was obviously

wrong. When the pound sign was in the criteria, the entire form was blank.

Help!

 

[Lila]

Perfect! I was trying to do that with the 0... I even tried "", but for some
reason I didn't think of NULL. Too much Excel I guess.

Lila

Count counts the presence of a value. It doesn't count nulls so, try this

Sold: Count(IIf([Info_SaleDate]>#10/31/2003#,[Info_SaleDate],NULL))

I am trying to count how many properties sold after a certain date. So far
I
have the following in the "field" area of the query

sold: Count(IIf([Info_SaleDate]>#10/31/2003#,[Info_SaleDate],0))

...but it returns the total number of fields, not just the ones sold after
the given date.

Then I tried just putting the field name in the field area Info_SaleDate
and
putting the date in the criteria

10/31/2003, but it returned some inexplicable number that was obviously

wrong. When the pound sign was in the criteria, the entire form was blank.

Help!