'Invalid operation' running query, 'OR' Culprit?

[FrankYG]

Hi, When I try to run this query I get an error 'Invalid Operation'
When I remove the 'OR' criteria, it works no problem. (it works
irrespective of which OR criteria I leave in).

SELECT UNI73LIVE_SBCASE.REFVAL AS [Case No], UNI73LIVE_SBCASE.WARD,
OneLineAddress([ADDRESS]) AS [Site Address], UNI73LIVE_CNOFFICER.NAME,
UNI73LIVE_SBCASE.SBAREATM, UNI73LIVE_SBCASE.DSCRPN,
UNI73LIVE_SBCASE.RECPTD, UNI73LIVE_SBCASE.DEPOSD,
UNI73LIVE_SBCASE.SBSTAT, QryUDSLookup.GSTAT, UNI73LIVE_SBCASE.DECIDD,
QryPlots.COMMDATE, UNI73LIVE_CNOFFICER.PHONE, UNI73LIVE_SBCASE.SATYPE,
UNI73LIVE_SBCASE.TYPMAT, Date()-[DECIDD] AS [TIME], Date() AS [Todays
Date], PeriodChecker(Date()-[DECIDD]) AS range,
LetType(Date()-[DECIDD]) AS Letter
FROM (QryUDSLookup INNER JOIN (UNI73LIVE_SBCASE INNER JOIN
UNI73LIVE_CNOFFICER ON UNI73LIVE_SBCASE.OFFICR =
UNI73LIVE_CNOFFICER.OFFCODE) ON QryUDSLookup.MDKEYVAL =
UNI73LIVE_SBCASE.KEYVAL) LEFT JOIN QryPlots ON UNI73LIVE_SBCASE.REFVAL
= QryPlots.REFVAL
WHERE (((Date()-[DECIDD])>=182 And (Date()-[DECIDD])<=210)) OR
(((Date()-[DECIDD])>=872 And (Date()-[DECIDD])<=900))
ORDER BY UNI73LIVE_SBCASE.RECPTD;

I'm trying to pull out records which have been live for a certain
period of time (between 182 and 210 days, or, between 872 and 900 days)
run then through a couple of functions 'periodchecker' and 'Letype'
which should give me some bullet text to pull into a Word Letter
template. The functions are below;

Public Function PeriodChecker(intDays As Integer) As String
Select Case intDays
Case Is <= 182
PeriodChecker = "Less than 6 months have"
Case Is <= 210
PeriodChecker = "Over 6 months have"
Case Is <= 872
PeriodChecker = "Over 2 years have"
Case Is <= 900
PeriodChecker = "Nearly 3 years have "
Case Else
PeriodChecker = "Letter not required"
End Select
End Function

Public Function LetType(intDays As Integer) As String
Select Case intDays
Case Is <= 182
LetType = "Less than 6 Months"
Case Is <= 210
LetType = "6M"
Case Is <= 872
LetType = "2Y"
Case Is <= 900
LetType = "30M"
Case Else
LetType = "not required"
End Select
End Function

Checking the available references, I've got;
Visual Basic for Applications
MS Access 10.0 Object Library
OLE Automation
MS ActiveX Data Objects 2.1 Library
SB Reports (custom functions)
MS DAO 3.6 Object Library
MS Scripting Runtime

I think that should be suffiecient.
Can anyone suggest what might be causing the Invalid Operation error?
or why using the OR criteria is causing a problem?

Thanks for any help on this one!

Regards,

 

[Douglas J Steele]

What happens if you try

WHERE ((Date()-[DECIDD]) Between 182 And 210) OR
((Date()-[DECIDD]) Between 872 And 900)

 

[FrankYG]

I must have made a mistake pasting in the original code, or copied from
a test query. The code is;

SELECT UNI73LIVE_SBCASE.REFVAL AS [Case No], UNI73LIVE_SBCASE.WARD,
OneLineAddress([ADDRESS]) AS [Site Address], UNI73LIVE_CNOFFICER.NAME,
UNI73LIVE_SBCASE.SBAREATM, UNI73LIVE_SBCASE.DSCRPN,
UNI73LIVE_SBCASE.RECPTD, UNI73LIVE_SBCASE.DEPOSD,
UNI73LIVE_SBCASE.SBSTAT, QryUDSLookup.GSTAT, UNI73LIVE_SBCASE.DECIDD,
QryPlots.COMMDATE, UNI73LIVE_CNOFFICER.PHONE, UNI73LIVE_SBCASE.SATYPE,
UNI73LIVE_SBCASE.TYPMAT, Date()-[DECIDD] AS [TIME], Date() AS TDate,
PeriodChecker(Date()-[DECIDD]) AS range, LetType(Date()-[DECIDD]) AS
Letter
FROM (QryUDSLookup INNER JOIN (UNI73LIVE_SBCASE INNER JOIN
UNI73LIVE_CNOFFICER ON UNI73LIVE_SBCASE.OFFICR =
UNI73LIVE_CNOFFICER.OFFCODE) ON QryUDSLookup.MDKEYVAL =
UNI73LIVE_SBCASE.KEYVAL) LEFT JOIN QryPlots ON UNI73LIVE_SBCASE.REFVAL
= QryPlots.REFVAL
WHERE (((QryUDSLookup.GSTAT)="Q99") AND ((UNI73LIVE_SBCASE.DECIDD) Is
Not Null) AND ((QryPlots.COMMDATE) Is Null) AND
((UNI73LIVE_SBCASE.SATYPE)<>"LC") AND ((Date()-[DECIDD])>=182 And
(Date()-[DECIDD])<=210)) OR (((QryUDSLookup.GSTAT)="Q99") AND
((UNI73LIVE_SBCASE.DECIDD) Is Not Null) AND ((QryPlots.COMMDATE) Is
Null) AND ((UNI73LIVE_SBCASE.SATYPE)<>"LC") AND ((Date()-[DECIDD])>=872
And (Date()-[DECIDD])<=900))
ORDER BY UNI73LIVE_SBCASE.RECPTD;

I just tried this;
SELECT UNI73LIVE_SBCASE.REFVAL AS [Case No], UNI73LIVE_SBCASE.WARD,
OneLineAddress([ADDRESS]) AS [Site Address], UNI73LIVE_CNOFFICER.NAME,
UNI73LIVE_SBCASE.SBAREATM, UNI73LIVE_SBCASE.DSCRPN,
UNI73LIVE_SBCASE.RECPTD, UNI73LIVE_SBCASE.DEPOSD,
UNI73LIVE_SBCASE.SBSTAT, QryUDSLookup.GSTAT, UNI73LIVE_SBCASE.DECIDD,
QryPlots.COMMDATE, UNI73LIVE_CNOFFICER.PHONE, UNI73LIVE_SBCASE.SATYPE,
UNI73LIVE_SBCASE.TYPMAT, Date()-[DECIDD] AS [TIME], Date() AS TDate,
PeriodChecker(Date()-[DECIDD]) AS range, LetType(Date()-[DECIDD]) AS
Letter
FROM (QryUDSLookup INNER JOIN (UNI73LIVE_SBCASE INNER JOIN
UNI73LIVE_CNOFFICER ON UNI73LIVE_SBCASE.OFFICR =
UNI73LIVE_CNOFFICER.OFFCODE) ON QryUDSLookup.MDKEYVAL =
UNI73LIVE_SBCASE.KEYVAL) LEFT JOIN QryPlots ON UNI73LIVE_SBCASE.REFVAL
= QryPlots.REFVAL
WHERE ((Date()-[DECIDD]) Between 182 And 210) OR
((Date()-[DECIDD]) Between 872 And 900)
ORDER BY UNI73LIVE_SBCASE.RECPTD;

Which gave the same error. So, every other criteria stripped out and it
still fails! Not good :-/

 

[FrankYG]

I read this from John Spencer (MVP);

SQL is more sensitive about the joining fields. IF you are joining on
text
fields, make sure that the fields are defined the same including the
allowable
field lengths.

I think I've narrowed the problem to the query QryUDSLookup.There is a
field within this query which I have had to trim - "GSTAT:
Left(Trim([VAL1]),3)" I think that changes the original field (VAL1)
from a memo field to a text field (correct me if thats wrong!) so that
I can use this field (GSTAT) to join to other tables. If I remove the
entire query (QryUDSLookup) from the query then it works. but I don't
know why!?

Any thoughts?!

Thanks.