Query parameters set by form 1

[DoctorJDM]

I have a form frmMergeData containing two unbound selector fields, SelectArea and SelectStatus, and want to pass their values to a query that is the source for an embedded subform.

Passing SelectArea, which is a text box, to the query's Area field works fine using the criterion

[Forms]![frmMergeData].[SelectArea]

The query's SQL is

SELECT tblExplorers.ExplorerArea, tblExplorers.ExplorerName, tblExplorers.Dormant
FROM tblExplorers
WHERE (((tblExplorers.ExplorerArea)=[Forms]![frmMergeData].[SelectArea]) AND ((tblExplorers.ExplorerSurname)>"0"))
ORDER BY tblExplorers.ExplorerArea;

The Dormant field is a checkbox and I'd like to pass the current SelectStatus value in the form to the query. I've set SelectStatus as a combo with values Yes or No but I can't get a criterion like below to work in the query

IIf([Forms]![frmMergeData].[SelectStatus]="No",0,-1)

Any thoughts?

 

[SoCalAccessPro]

How many columns does the Combo have, just one ?

An easy solution would be to have two columns. The first would be 0 and -1, the 2nd would be Yes and No.

Set the column count to 2, and set the width of the 1st column to 0. Now access will see the values, but dusplay the Yes/No.




Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[DoctorJDM]

Great - thanks SoCool. That did the trick.

Set
- combo's value list Row Source to 0;"Yes";-1;"No"
- Default value to 0

In the query I couldn't immediately get it to show results, cured by taking out the criteria for ExplorerArea and adding extra column with

Field : [ExplorerArea]=[Forms]![frmMergeData].[SelectArea] Or [Forms]![frmMergeData].[SelectArea] Is Null

Criteria : True