Expression too complex to Evaluate

[cbh35711]

All,



I am having a query problem. The query will pull from a form where the user input dates. Below is the date part of the where statement

and RA.PDModifiedDate >="#" & Format(Forms![Reconciliation Report]!Start_date,"mm\/dd\/yyyy") & "#" 
And RA.PDModifiedDate <="#" & Format(Forms![Reconciliation Report]!End_date,"mm\/dd\/yyyy") & "#"

This code has worked correctly in previous query. The one i'm using it in now is much more complex.

SELECT a.SystemID, a.PD, a.oid, b.[proposed pd grade]
FROM FAMAS_bbvaAuditLog AS RA, [Current Log] AS Log, (select systemid, max (OID) as max_oid
from[FAMAS_bbvaauditlog]  
group by systemid)  AS max_id
WHERE a.systemid = b.systemid
and max_id.systemid = a.systemid
and max_id.max_oid = a.oid
and a.PD IN ("CCC+", "CCC", "CCC-", "CC+", "CC", "CC-", "D1", "D2")
and a.PDStatus = "Approved" 
and (a.FacilityStatus = "Booked" OR a.FacilityStatus = "Booked-Pending" OR a.FacilityStatus = "Approved")
and a.LGD is not null
and a.pd <> b.[proposed pd grade]
and a.PDModifiedDate >="#" & Format(Forms![Reconciliation Report]!Start_date,"mm\/dd\/yyyy") & "#" 
And a.PDModifiedDate <="#" & Format(Forms![Reconciliation Report]!End_date,"mm\/dd\/yyyy") & "#"
GROUP BY a.SystemID, a.PD, a.oid, b.[proposed pd grade];

It works perfectly, before i add the date range code.

Your assistance is greatly appreciated.

Chris

[June7]

Why using Format function?
I would use BETWEEN AND, it is inclusive for the range end dates.

"a.PDModifiedDate BETWEEN #" & Forms!Reconciliation Report]!Start_date & "# AND #" & Forms![Reconciliation Report]!End_date & "#"

[cbh35711]

June,

I used format because it was the only way i could it to work with the same one. I appreciate the Between and AND. I will try that. Do you think that is the cause of the error?

Thanks,

Chris

[cbh35711]

So with a couple tweaks I used BETWEEN and AND for both queries. It worked perfectly in the first query, so I know it's not the date specifically. When i replace it in the second query, it still has the same error.

SELECT a.SystemID, a.PD, a.oid, log.[proposed pd grade]FROM FAMAS AS A, [Current Log] AS Log, (select systemid, max (OID) as max_oid
from[FAMAS]  
group by systemid)  AS max_id
WHERE a.systemid = Log.systemid
and max_id.systemid = a.systemid
and max_id.max_oid = a.oid
and a.PD IN ("CCC+", "CCC", "CCC-", "CC+", "CC", "CC-", "D1", "D2")
and a.PDStatus = "Approved" 
and (a.FacilityStatus = "Booked" OR a.FacilityStatus = "Booked-Pending" OR a.FacilityStatus = "Approved")
and a.LGD is not null
and a.pd <> log.[proposed pd grade]
and a.PDModifiedDate BETWEEN "#" & Forms![Reconciliation Report]!Start_date & "#" AND "#" & Forms![Reconciliation Report]!End_date & "#"
GROUP BY a.SystemID, a.PD, a.oid, log.[proposed pd grade];

Any help would be appreciated.

Chris

[cbh35711]

June,

I removed all of the # nonsense and it worked find there after.

Thanks for your help,

Chris