count only those after a certain date

  • Thread starter Thread starter Lila
  • Start date Start date
L

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
Click to expand...
wrong. When the pound sign was in the criteria, the entire form was blank.

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

Lila said:
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
Click to expand...
wrong. When the pound sign was in the criteria, the entire form was blank.

Help!
Click to expand...
 
It is Date/Time

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

Lila said:
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
Click to expand...
wrong. When the pound sign was in the criteria, the entire form was blank.

Help!
Click to expand...
Click to expand...
 
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))


Lila said:
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
Click to expand...
wrong. When the pound sign was in the criteria, the entire form was blank.

Help!
Click to expand...
 
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

John Spencer said:
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))


Lila said:
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
Click to expand...
wrong. When the pound sign was in the criteria, the entire form was blank.

Help!
Click to expand...
Click to expand...
 
Back
Top