Select query with 3 parameter problem

[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.

 

[OfficeDev18 via AccessMonster.com]

Ceebaby,

You need to treat that string as a parameter. Change

WHERE (((TBLEmptyPropertyDetails.BIUStatus)="[Forms]![FRM
ReptDateSelectDialog]![CmbBIUOption]")

to

WHERE (((TBLEmptyPropertyDetails.BIUStatus)= [[Forms]![FRM
ReptDateSelectDialog]![CmbBIUOption]")]

All you need to do is change the double-quotes to left and right brackets.
Since the field is defined in the query's PARAMETER statement, it must be
bracketed.

Hope this helps,

Sam

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.

 

[Ceebaby via AccessMonster.com]

Thanks for the response - it worked I could not see the wood for the trees.

Ceebaby,

You need to treat that string as a parameter. Change

WHERE (((TBLEmptyPropertyDetails.BIUStatus)="[Forms]![FRM
ReptDateSelectDialog]![CmbBIUOption]")

to

WHERE (((TBLEmptyPropertyDetails.BIUStatus)= [[Forms]![FRM
ReptDateSelectDialog]![CmbBIUOption]")]

All you need to do is change the double-quotes to left and right brackets.
Since the field is defined in the query's PARAMETER statement, it must be
bracketed.

Hope this helps,

Sam

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

[quoted text clipped - 35 lines]

What am I doing wrong? its driving me crazy. Any help most appreciated.