MS Access Expression typed incorrectly or too complex

K

Ken Lewis

The MS Access query below requires a date input. It runs perfectly with the dates 9/30/2009 or 12/31/2009, but fails with the subject error on dates of 10/31/2009 or 11/30/2009.

Can the error be caused by data in the tables themselves and any suggestions on how to adjust the query to avoid potential problem dates?

Thanks

SELECT tRecordsInventory.SCMContact, tRecordsInventory.BusUnitOwner, tDepartment.DepartmentNo, tRecordsInventory.PartyName, tRecordsInventory.Title, tRecordsInventory.DocScope, tRecordsInventory.ContractNumber, tRecordsInventory.ExpirationDate AS [Expiration-Date], tRecordsInventory.GeographicCoverage, tRecordsInventory.ReviewInterval

FROM tContractType RIGHT JOIN ((tStatus RIGHT JOIN (tAreaHeld RIGHT JOIN tRecordsInventory ON tAreaHeld.ID=tRecordsInventory.AreaHeld_id) ON tStatus.ID=tRecordsInventory.Status_id) LEFT JOIN tDepartment ON tRecordsInventory.Department_id=tDepartment.ID) ON tContractType.ID=tRecordsInventory.ContractType_id

WHERE (((tRecordsInventory.ExpirationDate)>(Forms!frmReports!ReportDate1)) And ((tRecordsInventory.ReviewInterval)>0) And ((tAreaHeld.AreaHeld)<>"Museum" And (tAreaHeld.AreaHeld)<>"HBS Facilities") And ((tStatus.StatusDescription)<>"Expired/Terminated" And (tStatus.StatusDescription)<>"Active") And ((DateAdd("d",CInt([tRecordsInventory.ReviewInterval])+15,Forms!frmReports!ReportDate1))>tRecordsInventory.ExpirationDate) And ((IsNull([expirationdate]))=False))

ORDER BY CInt(tDepartment.DepartmentNo);


EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx
 
J

John W. Vinson

The MS Access query below requires a date input. It runs perfectly with the dates 9/30/2009 or 12/31/2009, but fails with the subject error on dates of 10/31/2009 or 11/30/2009.
Click to expand...

I've found it helpful to explicitly declare the parameter when I see such
errors:

PARAMETERS Forms!frmReports!ReportDate1 DateTime;
SELECT tRecordsInventory.SCMContact, tRecordsInventory.BusUnitOwner,
tDepartment.DepartmentNo, tRecordsInventory.PartyName,
tRecordsInventory.Title, tRecordsInventory.DocScope,
tRecordsInventory.ContractNumber, tRecordsInventory.ExpirationDate AS
[Expiration-Date], tRecordsInventory.GeographicCoverage,
tRecordsInventory.ReviewInterval

FROM tContractType RIGHT JOIN ((tStatus RIGHT JOIN (tAreaHeld RIGHT JOIN
tRecordsInventory ON tAreaHeld.ID=tRecordsInventory.AreaHeld_id) ON
tStatus.ID=tRecordsInventory.Status_id) LEFT JOIN tDepartment ON
tRecordsInventory.Department_id=tDepartment.ID) ON
tContractType.ID=tRecordsInventory.ContractType_id

WHERE (((tRecordsInventory.ExpirationDate)>(Forms!frmReports!ReportDate1)) And
((tRecordsInventory.ReviewInterval)>0) And ((tAreaHeld.AreaHeld)<>"Museum" And
(tAreaHeld.AreaHeld)<>"HBS Facilities") And
((tStatus.StatusDescription)<>"Expired/Terminated" And
(tStatus.StatusDescription)<>"Active") And
((DateAdd("d",CInt([tRecordsInventory.ReviewInterval])+15,Forms!frmReports!ReportDate1))>tRecordsInventory.ExpirationDate)
And ((IsNull([expirationdate]))=False))

ORDER BY CInt(tDepartment.DepartmentNo);
 
K

Ken Lewis

Thanks!

That did the trick.



John W. Vinson wrote:

Re: MS Access Expression typed incorrectly or too complex
01-Oct-09

On Thu, 01 Oct 2009 15:24:53 -0700, Ken Lewis wrote

I've found it helpful to explicitly declare the parameter when I see suc
errors

PARAMETERS Forms!frmReports!ReportDate1 DateTime
SELECT tRecordsInventory.SCMContact, tRecordsInventory.BusUnitOwner
tDepartment.DepartmentNo, tRecordsInventory.PartyName
tRecordsInventory.Title, tRecordsInventory.DocScope
tRecordsInventory.ContractNumber, tRecordsInventory.ExpirationDate A
[Expiration-Date], tRecordsInventory.GeographicCoverage
tRecordsInventory.ReviewInterva

FROM tContractType RIGHT JOIN ((tStatus RIGHT JOIN (tAreaHeld RIGHT JOI
tRecordsInventory ON tAreaHeld.ID=tRecordsInventory.AreaHeld_id) O
tStatus.ID=tRecordsInventory.Status_id) LEFT JOIN tDepartment O
tRecordsInventory.Department_id=tDepartment.ID) O
tContractType.ID=tRecordsInventory.ContractType_i

WHERE (((tRecordsInventory.ExpirationDate)>(Forms!frmReports!ReportDate1)) An
((tRecordsInventory.ReviewInterval)>0) And ((tAreaHeld.AreaHeld)<>"Museum" An
(tAreaHeld.AreaHeld)<>"HBS Facilities") An
((tStatus.StatusDescription)<>"Expired/Terminated" An
(tStatus.StatusDescription)<>"Active") An
((DateAdd("d",CInt([tRecordsInventory.ReviewInterval])+15,Forms!frmReports!ReportDate1))>tRecordsInventory.ExpirationDate
And ((IsNull([expirationdate]))=False)

ORDER BY CInt(tDepartment.DepartmentNo)
--

John W. Vinson [MVP]

EggHeadCafe - Software Developer Portal of Choice
Run the command or application in the Security context of specified user
http://www.eggheadcafe.com/tutorial...2-cd97ef257c99/run-the-command-or-applic.aspx
 
Top