Form ref in criteria

  • Thread starter Thread starter Joss
  • Start date Start date
J

Joss

I have a query that references an unbound option box in a
form (frmStartUp) in the criteria line (below). The
form's option box is named Frame29 and when I click on
the first option the query disregards my selection and
ends up pulling 0 records from the query . . . not
everything but "B99". What am I doing wrong?

IIf([Forms]![frmStartUp]![Frame29]=1,<>"B99","B99")
 
I was able to get a query to give me the results you a looking for like this:

1) Remove the current criteria line.
2) In a new column, paste the following line (should be one line):

Exp1:IIf([Forms]![frmStartUp].[frame29]=1,((Not
([table_name].[field_name])="B99")),([table_name].[field_name])="B99")

Then
3) Change [table_name] to your table name
4) Change [field_name] to the field name that has B99 in it
5) In the Criteria row, add: True
6) The SHOW check box should NOT be checked
7) In the Frame29 OnClick event add: Me.Requery
8) In the frmStartUp Form_Open event add: Me.Requery

So you are creating a calculated field that will result in

Not([field] = B99) or [field] = B99.

Each row in the query will then have a calculated field [Exp1] that is
either TRUE or FALSE. Setting the criteria to TRUE selects only the records
with Exp1 = TRUE.

HTH
Steve
 
You can't put the operator "<>" in the IIf function.

Try changing your query as follows:

1. Remove your IIf from the criteria row of your FieldX.

2. In an empty Column of your Query, create a Calculated Field:

Expr1: [Forms]![frmStartUp]![Frame29]

3. In the *first* criteria row of FieldX and Expr1, use criteria:

<> "B99"
and
=1

respectively.

4. Similarly, put in the *second* criteria row, use

= "B99"

and

<> 1

respectively.

5. Now try running your Query.

Just to explain, you are in effect, set your criteria to:

(FieldX <> "B99" AND [Forms]![frmStartUp]![Frame29] = 1) OR
(FieldX = "B99" AND [Forms]![frmStartUp]![Frame29] <> 1)

which is , I believe, what you want.
 
Van T. Dinh,
I am still having some problems with your solution. In
an attempt to troubleshoot I ran the query with your
suggested expression and with no criteria - just to see
what would come up in Expr1. Instead of getting the
number "1", I got a character that resembles a square.
What does this mean?
Brian
-----Original Message-----
You can't put the operator "<>" in the IIf function.

Try changing your query as follows:

1. Remove your IIf from the criteria row of your FieldX.

2. In an empty Column of your Query, create a Calculated Field:

Expr1: [Forms]![frmStartUp]![Frame29]

3. In the *first* criteria row of FieldX and Expr1, use criteria:

<> "B99"
and
=1

respectively.

4. Similarly, put in the *second* criteria row, use

= "B99"

and

<> 1

respectively.

5. Now try running your Query.

Just to explain, you are in effect, set your criteria to:

(FieldX <> "B99" AND [Forms]![frmStartUp]![Frame29] = 1) OR
(FieldX = "B99" AND [Forms]![frmStartUp]![Frame29] <> 1)

which is , I believe, what you want.

--
HTH
Van T. Dinh
MVP (Access)


I have a query that references an unbound option box in a
form (frmStartUp) in the criteria line (below). The
form's option box is named Frame29 and when I click on
the first option the query disregards my selection and
ends up pulling 0 records from the query . . . not
everything but "B99". What am I doing wrong?

IIf([Forms]![frmStartUp]![Frame29]=1,<>"B99","B99")
Click to expand...


.
Click to expand...
 
My guess is that it is an unprintable character.

Why you got it rather than 1 or sone other values from the OptionGroup? I
don't know but check the follwing:

* Is the frmStartUp open?
* Does the OptionGroup [Frame29] have one of the options selected?

If you still can't fix it, post the relevant Table Structure, the set-up of
the Form and the SQL String of your Query.
 
Back
Top