EXPRESSIONS IN DATE RANGE 2

[netrusher]

Below are two expressions that I am using to get totals of records in two fields on my query. They work just fine.

Total Workers: DCount("[WorkerNameLast]","1-StationVerifyTbl","[ClockNbr]<>'*'")

Primary Verified: DCount("[WorkerNameLast]","1-StationVerifyTbl","[StaPrimary]=True")

These give totals of records from a table.

What I want to do now is limit these totals to a date range such as
">7/1/05 and <7/31/05"

I have added the Date field to the query and it will only show me the records for those dates but the totals from the two expressions above always show me the totals for the whole table. How can I restrict the two expressions to only giving me totals for a date range?

All help is appreciated.

 

[dhookom]

Total Workers: DCount("[WorkerNameLast]","1-StationVerifyTbl","[ClockNbr]<>'*' AND [DateField]>#7/1/2005# AND [DateField]<#7/31/2005#")


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Another way:
Total Workers: DCount("[WorkerNameLast]", "1-StationVerifyTbl", "[ClockNbr]<>'*' AND [DateField] Between #2005-07-01# And #2005-07-31#")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netrusher]

To take this a litte further. Is there anyway to have this to automatically have this for current month instead of putting in a date range? Also, is there a way to have the date range entered on a form and run the Query?

 

[ZmrAbdulla]

Change the last part of the SQL to (Between....)

Between DateAdd("d",-(Day(Date())-1),Date()) And DateAdd("d",-1,CDate("01/" & Format(DateAdd("m",1,Date()),"mm/yy")))

________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.

 

[dhookom]

There some very good examples of using controls on forms for query and report filtering at Martin Green's site

http://www.fontstuff.com/access/index.htm.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

have this for current month
Total Workers: DCount("[WorkerNameLast]", "1-StationVerifyTbl", "[ClockNbr]<>'*' AND Format([DateField],'yyyy-mm')=Format(Date(),'yyyy-mm')")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886