Too Complex

[Ryan Schoolman]

I have an issue when I brought this query in from ms access 2000 to ms
access XP/2002 I think i pertains to the datepart function if so what is the
reference that needs to be added.


SELECT qryClientsStatHist.[Person ID], qryClientsStatHist.DetailID,
qryClientsStatHist.[Service ID], qryClientsStatHist.PaymentID,
qryClientsStatHist.Date, qryClientsStatHist.EndDate,
qryClientsStatHist.DatePaid, qryClientsStatHist.Applied
FROM qryClientsStatHist
WHERE ((([Forms]![ClientStatRepOpsForm]![txt1stDateRange]) Is Null Or
([Forms]![ClientStatRepOpsForm]![txt1stDateRange]) Is Not Null))
GROUP BY qryClientsStatHist.[Person ID], qryClientsStatHist.DetailID,
qryClientsStatHist.[Service ID], qryClientsStatHist.PaymentID,
qryClientsStatHist.Date, qryClientsStatHist.EndDate,
qryClientsStatHist.DatePaid, qryClientsStatHist.Applied
HAVING (((qryClientsStatHist.DatePaid)<DatePart("m",Date())-1 & "/01/" &
DatePart("yyyy",Date())));


--
Ryan Schoolman - Programmer & Application Architect
(e-mail address removed)

PC Legends
http://www.pclegends.com

[w] 715.839.6855
[c] 715.379.0878
[h] 715.855.9003

 

[John Vinson]

HAVING (((qryClientsStatHist.DatePaid)<DatePart("m",Date())-1 & "/01/" &
DatePart("yyyy",Date())));

You're going all around the barn to construct a text string. I think a
much simpler (and working!) criterion would be

(((qryClientsStatHist.DatePaid)<DateSerial(Year(Date()), Month(Date())
- 1, 1)))

You should also change the HAVING to WHERE; this will apply the filter
*before* doing all the totalling rather than doing all the totalling
and then throwing away most of the results.

If you have any reference MISSING it will mess up ALL the libraries;
from my boilerplate file:

Open any module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.