C
Ceebaby via AccessMonster.com
I have a report that uses a form to supply information to the reports
underlying query. I want to be able to select an option type and select start
and end dates.
When I open the form to select the information for the report I get a run
time error 3071 saying the the expression is too complex to be evaluted etc..
When I run the query, it works fine. I dont understand why it does not run
from the form. Here is the sql for the query:
PARAMETERS Forms.[FRM ReptDateSelectDialog].CmbBIUOption Text ( 255 ), Forms.
[FRM ReptDateSelectDialog].StartDate DateTime, Forms.[FRM
ReptDateSelectDialog].EndDate DateTime;
SELECT TBLEmptyPropertyDetails.BIUStatus, TBLEmptyPropertyDetails.BIUDate,
TBLEmptyPropertyDetails.[E-homesID], TBLEmptyPropertyDetails.OwnerID,
TBLEmptyPropertyDetails.BackInUse, [FlatNumber] & " " & [BuildingName] & " "
& [PropertyNumber] & " " & [Road] & " " & [Area] AS Address,
TBLEmptyPropertyDetails.UnitsGained, TBLEmptyPropertyDetails.Bedspaces,
TBLEmptyPropertyDetails.timeEmptyDays, TBLEmptyPropertyDetails.CaseOfficer,
TBLOwnerOptions.[RSL Lease], TBLOwnerOptions.[Grant-Loan], TBLOwnerOptions.
CurrentOwner, TBLEmptyPropertyDetails.[Date inputted],
TBLEmptyPropertyDetails.CaseStatus, TBLEmptyPropertyDetails.UnitBIUbyLA,
TBLEmptyPropertyDetails.[Property type]
FROM TblNewOwnersDetails INNER JOIN (TBLEmptyPropertyDetails INNER JOIN
TBLOwnerOptions ON TBLEmptyPropertyDetails.[E-homesID] = TBLOwnerOptions.[E-
homesID]) ON TblNewOwnersDetails.OwnerID = TBLOwnerOptions.OwnerID
WHERE (((TBLEmptyPropertyDetails.BIUStatus)="[Forms]![FRM
ReptDateSelectDialog]![CmbBIUOption]") AND ((TBLEmptyPropertyDetails.BIUDate)
Between [Forms]![FRM ReptDateSelectDialog]![StartDate] And [Forms]![FRM
ReptDateSelectDialog]![EndDate]) AND ((TBLEmptyPropertyDetails.BackInUse)=Yes)
AND ((TBLOwnerOptions.CurrentOwner)=Yes));
I have tried encasing the field Forms]![FRM ReptDateSelectDialog]!
[CmbBIUOption in quotes, and have also tried using the like [Forms]![FRM
ReptDateSelectDialog]![CmbBIUOption] expression and still get the same error
code when running the report from the form. Both expressions work when
running the query.
What am I doing wrong? its driving me crazy. Any help most appreciated.
underlying query. I want to be able to select an option type and select start
and end dates.
When I open the form to select the information for the report I get a run
time error 3071 saying the the expression is too complex to be evaluted etc..
When I run the query, it works fine. I dont understand why it does not run
from the form. Here is the sql for the query:
PARAMETERS Forms.[FRM ReptDateSelectDialog].CmbBIUOption Text ( 255 ), Forms.
[FRM ReptDateSelectDialog].StartDate DateTime, Forms.[FRM
ReptDateSelectDialog].EndDate DateTime;
SELECT TBLEmptyPropertyDetails.BIUStatus, TBLEmptyPropertyDetails.BIUDate,
TBLEmptyPropertyDetails.[E-homesID], TBLEmptyPropertyDetails.OwnerID,
TBLEmptyPropertyDetails.BackInUse, [FlatNumber] & " " & [BuildingName] & " "
& [PropertyNumber] & " " & [Road] & " " & [Area] AS Address,
TBLEmptyPropertyDetails.UnitsGained, TBLEmptyPropertyDetails.Bedspaces,
TBLEmptyPropertyDetails.timeEmptyDays, TBLEmptyPropertyDetails.CaseOfficer,
TBLOwnerOptions.[RSL Lease], TBLOwnerOptions.[Grant-Loan], TBLOwnerOptions.
CurrentOwner, TBLEmptyPropertyDetails.[Date inputted],
TBLEmptyPropertyDetails.CaseStatus, TBLEmptyPropertyDetails.UnitBIUbyLA,
TBLEmptyPropertyDetails.[Property type]
FROM TblNewOwnersDetails INNER JOIN (TBLEmptyPropertyDetails INNER JOIN
TBLOwnerOptions ON TBLEmptyPropertyDetails.[E-homesID] = TBLOwnerOptions.[E-
homesID]) ON TblNewOwnersDetails.OwnerID = TBLOwnerOptions.OwnerID
WHERE (((TBLEmptyPropertyDetails.BIUStatus)="[Forms]![FRM
ReptDateSelectDialog]![CmbBIUOption]") AND ((TBLEmptyPropertyDetails.BIUDate)
Between [Forms]![FRM ReptDateSelectDialog]![StartDate] And [Forms]![FRM
ReptDateSelectDialog]![EndDate]) AND ((TBLEmptyPropertyDetails.BackInUse)=Yes)
AND ((TBLOwnerOptions.CurrentOwner)=Yes));
I have tried encasing the field Forms]![FRM ReptDateSelectDialog]!
[CmbBIUOption in quotes, and have also tried using the like [Forms]![FRM
ReptDateSelectDialog]![CmbBIUOption] expression and still get the same error
code when running the report from the form. Both expressions work when
running the query.
What am I doing wrong? its driving me crazy. Any help most appreciated.