RE: [MS_AccessPros] Dcount not working

Art,
Do yourself a favor and lookup the various date functions and formats. Some important functions are:
DatePart(), Month(), Year(), DateSerial(), Format(), DateAdd(), and others.

Then open the debug window (press Ctrl+G) and try them out.
? Format(Date(),"yyyyq")
? DatePart("q",Date())
? your fun tests go here....

=DCount("ApplicationID","qryWaitingListCurrentMonth","Format(StatusDate,"yyyyq") = Format(Date(),"yyyyq")")

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: dbalorenzini@yahoo.com
Date: Sun, 28 Aug 2011 21:11:47 -0700
Subject: Re: [MS_AccessPros] Dcount not working

This was the winner for the previous month:

=DCount("ApplicationID","qryWaitingListCurrentMonth"," StatusDate BETWEEN DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)")

Can you send an example of the quarter expression?

With warm regards,

Arthur Lorenzini| SQL Server/Access Developer l alorenzin@live.com
Office: 605-338-0947| Mobile: 605-857-9137 | Fax: 605-338-0947

1316 E. 7th Street
Sioux Falls, SD 57103
SQL Server Development
Database Adminstration Services
Microsoft Access Development
Grant Writing TA Services
IT Assessment Services
Software Application Training

From: Duane Hookom <duanehookom@hotmail.com>
To: ms_access_professionals@yahoogroups.com
Sent: Sunday, August 28, 2011 9:39 PM
Subject: RE: [MS_AccessPros] Dcount not working
The previous month is available by subtracting 1 from the current month. You can use Format(DateExpression,"yyyyq") to compare year and quarter.Duane HookomMS Access MVPTo: MS_Access_Professionals@yahoogroups.comFrom: dbalorenzini@yahoo.comDate: Sun, 28 Aug 2011 12:19:12 -0700Subject: Re: [MS_AccessPros] Dcount not working The original code was something I found on the internet I think it came from Access Junkies or somewhere.This work though. Thanks.=DCount("ApplicationID","qryWaitingListCurrentMonth","Year(StatusDate) = Year(Date()) AND Month(StatusDate) = Month(Date())")How would I find the previous month's count and also I would need to do it by quarters?With warm regards,Arthur Lorenzini| SQL Server/Access Developer l alorenzin@live.comOffice: 605-338-0947| Mobile: 605-857-9137 | Fax: 605-338-0947 1316 E. 7th Street Sioux Falls, SD 57103 SQL Server DevelopmentDatabase Adminstration ServicesMicrosoft Access
Development Grant Writing TA ServicesIT Assessment ServicesSoftware Application Training From: Duane Hookom <duanehookom@hotmail.com>To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>Sent: Sunday, August 28, 2011 2:09 PMSubject: RE: [MS_AccessPros] Dcount not workingI don't understand what you are attempting to do with the date stuff. Is StatusDate an actual Date field? I would try:=DCount("ApplicationID","qryWaitingListCurrentMonth","Year(StatusDate) = Year(Date()) AND Month(StatusDate) = Month(Date())")or=DCount("ApplicationID","qryWaitingListCurrentMonth","Format(StatusDate,'yyyymm') = Format(Date(),'yyyymm')")Duane HookomMS Access MVPTo: MS_Access_Professionals@yahoogroups.comFrom: dbalorenzini@yahoo.comDate: Sun, 28 Aug 2011 11:22:43 -0700Subject: Re: [MS_AccessPros] Dcount not working Great Catch.=DCount("ApplicationID","qryWaitingListCurrentMonth"," StatusDate BETWEEN Year(StatusDate) = Year(Date()) AND
Month(StatusDate) = Month(Date()) ")But this express should be returning records in the current month but it is returning 0. I checked the table and there are records in it for August. It should return all the records from the 1st day of the month to the last day of the month.With warm regards,ArthurLorenzini| SQL Server/Access Developer l alorenzin@live.comOffice: 605-338-0947| Mobile: 605-857-9137 | Fax: 605-338-09471316 E. 7th Street Sioux Falls, SD 57103 SQL Server DevelopmentDatabase Adminstration ServicesMicrosoft Access Development Grant Writing TA ServicesIT Assessment ServicesSoftware Application Training From: Duane Hookom <duanehookom@hotmail.com>To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>Sent: Sunday, August 28, 2011 12:23 PMSubject: RE: [MS_AccessPros] Dcount not workingThere is no field [ApplicationStatusTypeID] exposed in the SELECT section of qryWaitingListCurrentMonth. Why is this even included
in the DCount() since records in the query are already filtered?Duane HookomMS Access MVPTo: MS_Access_Professionals@yahoogroups.comFrom: dbalorenzini@yahoo.comDate: Sun, 28 Aug 2011 09:50:59 -0700Subject: Re: [MS_AccessPros] Dcount not working =DCount("ApplicationID","qryWaitingListCurrentMonth","ApplicationStatusTypeID =3 AND StatusDate BETWEEN Year(StatusDate) = Year(Date()) AND Month(StatusDate) = Month(Date()) ")THis is what I have now and it's still throwing a error. The is the syntax for qryWaitingListCurrentMonth.SELECT tblApplicationStatus.ApplicantID, tblApplicationStatus.StatusDate, tlkpApplicationStatusType.ApplicationStatusTypeName, tblApplicationStatus.ApplicationIDFROM tblApplicationStatus INNER JOIN tlkpApplicationStatusType ON tblApplicationStatus.ApplicationStatusTypeID =tlkpApplicationStatusType.ApplicationStatusTypeIDWHERE (((tblApplicationStatus.ApplicationStatusTypeID)=3));With warm regards,Arthur Lorenzini| SQL Server/Access
[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/