How do I do a count?

[Chavito21]

hi everyone,

I have a query as follow:

SELECT Est.SalesPerson, Est.TurninDt, Est.Ordernum
FROM Est
WHERE (((Est.SalesPerson)=[Forms]![SALE_REPORT_F]![INIT_IN]) AND ((Est.TurninDt)>=[Forms]![SALE_REPORT_F]![FROM_IN] And (Est.TurninDt)<=[Forms]![SALE_REPORT_F]![TO_IN]) AND ((Est.Ordernum)>0));

How can I do a count of how many record on this query. This query gime all the records all I need is count.
Can I do a Dcount so I can have the value? How can I do that?
I appreciate any help

Thanks
Chavito

 

[Golom]

Try
[blue][tt]
SELECT Count(*) As RecordCount

FROM Est

WHERE (((Est.SalesPerson)=[Forms]![SALE_REPORT_F]![INIT_IN]) AND ((Est.TurninDt)>=[Forms]![SALE_REPORT_F]![FROM_IN] And (Est.TurninDt)<=[Forms]![SALE_REPORT_F]![TO_IN]) AND ((Est.Ordernum)>0));
[/tt][/blue]

 

[Chavito21]

That works great. But How do I retrive the value and put it on a form?

 

[dhookom]

I'm not sure the data types of your fields but this should get you started:
=DCount("*","Est","SalesPerson='" & [Forms]![SALE_REPORT_F]![INIT_IN] & "' And TurnInDt >=#" ...etc...)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Chavito21]

Can you help me with the syntax, can I use between?
this is my code:

CNT1 = DCount("*", "Est", "SalesPerson='" & [Forms]![SALE_REPORT_F]![INIT_IN] & "' And TurnInDt = between #[Forms]![SALE_REPORT_F]![FROM_IN]# #[Forms]![SALE_REPORT_F]![TO_IN]# AND ordernum > 0")

 

[lespaul]

The between syntax is:

something AND somethingelse

so:

CNT1 = DCount("*", "Est", "SalesPerson='" & [Forms]![SALE_REPORT_F]![INIT_IN] & "' And TurnInDt = between #[Forms]![SALE_REPORT_F]![FROM_IN]# AND #[Forms]![SALE_REPORT_F]![TO_IN]# AND ordernum > 0")

Leslie

 

[dhookom]

I would probably try:
CNT1 = DCount("*", "Est", "SalesPerson='" & [Forms]![SALE_REPORT_F]![INIT_IN] & "' And TurnInDt between #" & [Forms]![SALE_REPORT_F]![FROM_IN] & "# AND #" & [Forms]![SALE_REPORT_F]![TO_IN] & "# AND ordernum > 0")

Duane
MS Access MVP
Find out how to get great answers faq219-2884.