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]
Tidak ada komentar:
Posting Komentar