DCount Question

[Larry Salvucci]

I'm trying to count specific records on my form the meet certain criterias.

I have a control on my form called "VOrigDate". This control calculates the
difference between two dates. What I want to do is count the number of
records that fall between 2 & 4 days from this control.

Here's what I have but it isn't working

=DCount([VendorID],"qryVendors","VOrigDate >1 And <4")

Do I have this correct?

 

[Rob Parker]

Try:
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate < 4")
to get days 2 or 3. If you want to include day 4 you'll need
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate <= 4")
or
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate < 5")

HTH,

Rob

 

[Larry Salvucci]

That worked, thanks. But how do I get it to only do the count for the records
that I filter on my form? I forgot to mention that part. I'm filtering my
records by month & year. The filter works fine but the DCount is still
counting my records based on my query and not what I've filtered in my form.

Try:
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate < 4")
to get days 2 or 3. If you want to include day 4 you'll need
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate <= 4")
or
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate < 5")

HTH,

Rob

I'm trying to count specific records on my form the meet certain
criterias.

I have a control on my form called "VOrigDate". This control calculates
the
difference between two dates. What I want to do is count the number of
records that fall between 2 & 4 days from this control.

Here's what I have but it isn't working

=DCount([VendorID],"qryVendors","VOrigDate >1 And <4")

Do I have this correct?

 

[Rob Parker]

I'd need more details for that, and it could be rather tricky. Where is
this dCount expression? In code, or in an unbound control on the form? And
how are you applying the filtering to the records? What exactly are you
trying to accomplish?

Rob

That worked, thanks. But how do I get it to only do the count for the
records
that I filter on my form? I forgot to mention that part. I'm filtering my
records by month & year. The filter works fine but the DCount is still
counting my records based on my query and not what I've filtered in my
form.

Try:
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate < 4")
to get days 2 or 3. If you want to include day 4 you'll need
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate <= 4")
or
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate < 5")

HTH,

Rob


message

I'm trying to count specific records on my form the meet certain
criterias.

I have a control on my form called "VOrigDate". This control calculates
the
difference between two dates. What I want to do is count the number of
records that fall between 2 & 4 days from this control.

Here's what I have but it isn't working

=DCount([VendorID],"qryVendors","VOrigDate >1 And <4")

Do I have this correct?

 

[John W. Vinson]

I'm trying to count specific records on my form the meet certain criterias.

I have a control on my form called "VOrigDate". This control calculates the
difference between two dates. What I want to do is count the number of
records that fall between 2 & 4 days from this control.

Here's what I have but it isn't working

=DCount([VendorID],"qryVendors","VOrigDate >1 And <4")

Do I have this correct?

No.

A record isn't a number, nor is it a date. It might have 255 fields of every
posible datatype. "the number of records that fall between 2 & 4 days from
this control" is absolutely meaningless to me!

What is the structure of qryVendors? Please post the SQL. What field or fields
within qryVendors do you want to compare to VOrigDate? What's the control
source of vOrigDate? What does the form have do do with it at all, rather than
counting records in a Totals query with specific criteria?

 

[Larry Salvucci]

The dcount expression is in the form's footer. What I'm trying to do is
filter my records by month and have it just give me the count of records that
have a value of 2 to 4 days in my VOrigDate control. I have a combo box in my
form's header that I'm using to filter the rercords by a particular month.

I'd need more details for that, and it could be rather tricky. Where is
this dCount expression? In code, or in an unbound control on the form? And
how are you applying the filtering to the records? What exactly are you
trying to accomplish?

Rob

That worked, thanks. But how do I get it to only do the count for the
records
that I filter on my form? I forgot to mention that part. I'm filtering my
records by month & year. The filter works fine but the DCount is still
counting my records based on my query and not what I've filtered in my
form.

Try:
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate < 4")
to get days 2 or 3. If you want to include day 4 you'll need
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate <= 4")
or
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate < 5")

HTH,

Rob


message
I'm trying to count specific records on my form the meet certain
criterias.

I have a control on my form called "VOrigDate". This control calculates
the
difference between two dates. What I want to do is count the number of
records that fall between 2 & 4 days from this control.

Here's what I have but it isn't working

=DCount([VendorID],"qryVendors","VOrigDate >1 And <4")

Do I have this correct?

 

[John W. Vinson]

The dcount expression is in the form's footer. What I'm trying to do is
filter my records by month and have it just give me the count of records that
have a value of 2 to 4 days in my VOrigDate control. I have a combo box in my
form's header that I'm using to filter the rercords by a particular month.

A Dcount function can count values of fields in a *table or query*. It cannot
count values in a Textbox, since there is really only one textbox, a display
tool and not a data storage medium. Try moving the expression into your Form's
Recordsource query instead.

 

[Rob Parker]

Hi Larry,

There's several ways to do this, but I think the easiest will be to use John
Vinson's suggestion to include the 2-to-4 days result for each record in the
form's recordsource.

In the query that is the form's recordsource, add a new calculated field (it
will be a True/False field, named 2to4), thus:
2to4: iif([VOrigDate] > 1 And [VOrigDate] < 5, True, False)

Then, in your textbox in the form's footer, all you need is:
=Abs(Sum([2to4]))

This takes advantage of the fact that True is represented internally as -1
and false as 0; summing the field and taking the absolute value is
equivalent to counting the records where 2to4 is True. This expression will
take into account any filtering applied to the form, by any method (via
combo-box entry, or right-click "Filter by" in a field, or ...).

HTH,

Rob

The dcount expression is in the form's footer. What I'm trying to do is
filter my records by month and have it just give me the count of records
that
have a value of 2 to 4 days in my VOrigDate control. I have a combo box in
my
form's header that I'm using to filter the rercords by a particular month.

I'd need more details for that, and it could be rather tricky. Where is
this dCount expression? In code, or in an unbound control on the form?
And
how are you applying the filtering to the records? What exactly are you
trying to accomplish?

Rob

message

That worked, thanks. But how do I get it to only do the count for the
records
that I filter on my form? I forgot to mention that part. I'm filtering
my
records by month & year. The filter works fine but the DCount is still
counting my records based on my query and not what I've filtered in my
form.

:

Try:
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate <
4")
to get days 2 or 3. If you want to include day 4 you'll need
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate <=
4")
or
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate <
5")

HTH,

Rob


message
I'm trying to count specific records on my form the meet certain
criterias.

I have a control on my form called "VOrigDate". This control
calculates
the
difference between two dates. What I want to do is count the number
of
records that fall between 2 & 4 days from this control.

Here's what I have but it isn't working

=DCount([VendorID],"qryVendors","VOrigDate >1 And <4")

Do I have this correct?

 

[Rob Parker]

And a follow-up:

If VOrigDate is a calculated field in your query (eg. a DateDiff involving 2
fields in the underlying table), you'll need to use the expression for that
calculated field in the 2to4 calculated field; you can only refer to fields
in the underlying table(s) in a calculated field expression, you cannot
refer directly to another calclated field.

Rob

Hi Larry,

There's several ways to do this, but I think the easiest will be to use
John Vinson's suggestion to include the 2-to-4 days result for each record
in the form's recordsource.

In the query that is the form's recordsource, add a new calculated field
(it will be a True/False field, named 2to4), thus:
2to4: iif([VOrigDate] > 1 And [VOrigDate] < 5, True, False)

Then, in your textbox in the form's footer, all you need is:
=Abs(Sum([2to4]))

This takes advantage of the fact that True is represented internally as -1
and false as 0; summing the field and taking the absolute value is
equivalent to counting the records where 2to4 is True. This expression
will take into account any filtering applied to the form, by any method
(via combo-box entry, or right-click "Filter by" in a field, or ...).

HTH,

Rob

The dcount expression is in the form's footer. What I'm trying to do is
filter my records by month and have it just give me the count of records
that
have a value of 2 to 4 days in my VOrigDate control. I have a combo box
in my
form's header that I'm using to filter the rercords by a particular
month.

I'd need more details for that, and it could be rather tricky. Where is
this dCount expression? In code, or in an unbound control on the form?
And
how are you applying the filtering to the records? What exactly are you
trying to accomplish?

Rob

message
That worked, thanks. But how do I get it to only do the count for the
records
that I filter on my form? I forgot to mention that part. I'm filtering
my
records by month & year. The filter works fine but the DCount is still
counting my records based on my query and not what I've filtered in my
form.

:

Try:
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate <
4")
to get days 2 or 3. If you want to include day 4 you'll need
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate <=
4")
or
=DCount([VendorID],"qryVendors","VOrigDate > 1 " And "VOrigDate <
5")

HTH,

Rob


message
I'm trying to count specific records on my form the meet certain
criterias.

I have a control on my form called "VOrigDate". This control
calculates
the
difference between two dates. What I want to do is count the number
of
records that fall between 2 & 4 days from this control.

Here's what I have but it isn't working

=DCount([VendorID],"qryVendors","VOrigDate >1 And <4")

Do I have this correct?