Too Complex of a Query

  • Thread starter Thread starter Ryan Schoolman
  • Start date Start date
R

Ryan Schoolman

Here is a query that I brought in from ms access 2000 and opened the
database in access 2002/XP and it gives me to too complex to evaluate error
message. I think this error pertains to the datepart function if so what is
the reference that I need to make sure that is 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
 
1. Firstly, I think the criteria:

WHERE
((
([Forms]![ClientStatRepOpsForm]![txt1stDateRange])
Is Null
Or
([Forms]![ClientStatRepOpsForm]![txt1stDateRange])
Is Not Null
))

is ALWAYS evaluated to True so I am not sure why you
included the criteria in the first place. If it is always
true, you don't need it. Thus remove it to make the Query
simpler.

2. The aggregate condition:

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

requires Access / Jet to do a fair bit of type-casting
from Integer to Text, (then concatenating Strings), then
type-casting from String to Date (and I wouldn't rely on
automatic type-casting from String to Date).

If my logic is correct, you want the first of the
preceding month. In this case, use:

DateSerial(Year(Date()), Month(Date())-1, 1)

(no automatic type-casting involved except for the ones
done by inbuilt functions).

You can also use aliases to make your SQL String looks
simpler, try:

SELECT Q.[Person ID], Q.DetailID, Q.[Service ID],
Q.PaymentID, Q.[Date], Q.EndDate, Q.DatePaid, Q.Applied
FROM qryClientsStatHist As Q
GROUP BY Q.[Person ID], Q.DetailID, Q.[Service ID],
Q.PaymentID, Q.[Date], Q.EndDate, Q.DatePaid, Q.Applied
HAVING
((
(Q.DatePaid)<DateSerial(Year(Date()),Month(Date())-1, 1)
));


Notes:

1. Since you have only 1 datasource, you don't rely need
the datasource qualifiers anyway.

2. I have enclosed you Field name "Date" in square
brackets to avoid the *reserved word* "Date". "Date" is
actually a bad Field name. Change it if you can or you
have to make sure the square brackets are used with it in
VBA code all the times.

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
Here is a query that I brought in from ms access 2000 and opened the
database in access 2002/XP and it gives me to too complex to evaluate error
message. I think this error pertains to the datepart function if so what is
the reference that I need to make sure that is 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


.
Click to expand...
 
Thanks for the help ;-)


Van T. Dinh said:
1. Firstly, I think the criteria:

WHERE
((
([Forms]![ClientStatRepOpsForm]![txt1stDateRange])
Is Null
Or
([Forms]![ClientStatRepOpsForm]![txt1stDateRange])
Is Not Null
))

is ALWAYS evaluated to True so I am not sure why you
included the criteria in the first place. If it is always
true, you don't need it. Thus remove it to make the Query
simpler.

2. The aggregate condition:

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

requires Access / Jet to do a fair bit of type-casting
from Integer to Text, (then concatenating Strings), then
type-casting from String to Date (and I wouldn't rely on
automatic type-casting from String to Date).

If my logic is correct, you want the first of the
preceding month. In this case, use:

DateSerial(Year(Date()), Month(Date())-1, 1)

(no automatic type-casting involved except for the ones
done by inbuilt functions).

You can also use aliases to make your SQL String looks
simpler, try:

SELECT Q.[Person ID], Q.DetailID, Q.[Service ID],
Q.PaymentID, Q.[Date], Q.EndDate, Q.DatePaid, Q.Applied
FROM qryClientsStatHist As Q
GROUP BY Q.[Person ID], Q.DetailID, Q.[Service ID],
Q.PaymentID, Q.[Date], Q.EndDate, Q.DatePaid, Q.Applied
HAVING
((
(Q.DatePaid)<DateSerial(Year(Date()),Month(Date())-1, 1)
));


Notes:

1. Since you have only 1 datasource, you don't rely need
the datasource qualifiers anyway.

2. I have enclosed you Field name "Date" in square
brackets to avoid the *reserved word* "Date". "Date" is
actually a bad Field name. Change it if you can or you
have to make sure the square brackets are used with it in
VBA code all the times.

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
Here is a query that I brought in from ms access 2000 and opened the
database in access 2002/XP and it gives me to too complex to evaluate error
message. I think this error pertains to the datepart function if so what is
the reference that I need to make sure that is 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


.
Click to expand...
Click to expand...
 
Back
Top