Expression help - query looks to subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with a subform. In the main form(main), I have a tab that sums
the items for each record(So far so good). QueryCount sums up 5 different
itmes and displays the count total as I want it to. Here're where I've got
problems. I need the query to look at field [x] in subform Detail. If the
value of x = something, then the value of the summed fields in
QueryCount(SUM1,2,3,4,SUM5) is multiplied by -1.

IF(forms![main].[x] = "something", * -1 )

My syntax with expressions is terrible. Can anyone help?
 
Try this (assumes that Detail (bad choice of name, by the way, as Detail is
a reserved name in ACCESS) is the name of the subform control, the actual
control that holds the subform object, and that x is the name of a control
on that subform):

IF(forms![main]![Detail]![x] = "something", * -1 )
 
Thanks for the help Ken.

I tired what you suggested, however when I try to run the query it comes
back with
"Undefined Function "IF" in expression". I modified to IIF and this seemed
to correct that problem, however, I'm getting parameter prompts for the
values of my subform.

Can you clarify If I got this correct, it should be

IIF(forms![mainformname]![subformcontrolname]![control] = "something", * -1)

I'm not clear on "is the name of the subform control, the actual control
that holds the subform object" This would be the subform name as the control
resides on the subform?

Thanks,
JD

Ken Snell said:
Try this (assumes that Detail (bad choice of name, by the way, as Detail is
a reserved name in ACCESS) is the name of the subform control, the actual
control that holds the subform object, and that x is the name of a control
on that subform):

IF(forms![main]![Detail]![x] = "something", * -1 )

--

Ken Snell
<MS ACCESS MVP>


JD said:
I have a form with a subform. In the main form(main), I have a tab that
sums
the items for each record(So far so good). QueryCount sums up 5 different
itmes and displays the count total as I want it to. Here're where I've
got
problems. I need the query to look at field [x] in subform Detail. If the
value of x = something, then the value of the summed fields in
QueryCount(SUM1,2,3,4,SUM5) is multiplied by -1.

IF(forms![main].[x] = "something", * -1 )

My syntax with expressions is terrible. Can anyone help?
Click to expand...
Click to expand...
 
Yes, IIf is the correct usage. I pasted from your original message, sorry.

To get the correct name of subformcontrolname, open the main form in design
view, click on the very top edge of the subform object, open the Properties
window (icon on toolbar), and click on Other tab. You'll see the name of the
subform control in the Name box.

--

Ken Snell
<MS ACCESS MVP>


JD said:
Thanks for the help Ken.

I tired what you suggested, however when I try to run the query it comes
back with
"Undefined Function "IF" in expression". I modified to IIF and this
seemed
to correct that problem, however, I'm getting parameter prompts for the
values of my subform.

Can you clarify If I got this correct, it should be

IIF(forms![mainformname]![subformcontrolname]![control] = "something",
* -1)

I'm not clear on "is the name of the subform control, the actual control
that holds the subform object" This would be the subform name as the
control
resides on the subform?

Thanks,
JD

Ken Snell said:
Try this (assumes that Detail (bad choice of name, by the way, as Detail
is
a reserved name in ACCESS) is the name of the subform control, the actual
control that holds the subform object, and that x is the name of a
control
on that subform):

IF(forms![main]![Detail]![x] = "something", * -1 )

--

Ken Snell
<MS ACCESS MVP>


JD said:
I have a form with a subform. In the main form(main), I have a tab that
sums
the items for each record(So far so good). QueryCount sums up 5
different
itmes and displays the count total as I want it to. Here're where I've
got
problems. I need the query to look at field [x] in subform Detail. If
the
value of x = something, then the value of the summed fields in
QueryCount(SUM1,2,3,4,SUM5) is multiplied by -1.

IF(forms![main].[x] = "something", * -1 )

My syntax with expressions is terrible. Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
That resolved things somewhat, however, there is still something wrong. The
exact expression I used is below. Note Access puts the * -1 in quotes.
Prompts no longer appear when the form loads, but no values
populate anymore. Ran the query from design view and I get error 3071.
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

IIf([forms]![frmMain]![frm_Main_detail_subform]![combo117]="test","* -1 ")

Everytime I try to get rid of the "" around the multiply by -1, they come
back. Tried ' ' as well. No joy. Not sure whats up.


Ken Snell said:
Yes, IIf is the correct usage. I pasted from your original message, sorry.

To get the correct name of subformcontrolname, open the main form in design
view, click on the very top edge of the subform object, open the Properties
window (icon on toolbar), and click on Other tab. You'll see the name of the
subform control in the Name box.

--

Ken Snell
<MS ACCESS MVP>


JD said:
Thanks for the help Ken.

I tired what you suggested, however when I try to run the query it comes
back with
"Undefined Function "IF" in expression". I modified to IIF and this
seemed
to correct that problem, however, I'm getting parameter prompts for the
values of my subform.

Can you clarify If I got this correct, it should be

IIF(forms![mainformname]![subformcontrolname]![control] = "something",
* -1)

I'm not clear on "is the name of the subform control, the actual control
that holds the subform object" This would be the subform name as the
control
resides on the subform?

Thanks,
JD

Ken Snell said:
Try this (assumes that Detail (bad choice of name, by the way, as Detail
is
a reserved name in ACCESS) is the name of the subform control, the actual
control that holds the subform object, and that x is the name of a
control
on that subform):

IF(forms![main]![Detail]![x] = "something", * -1 )

--

Ken Snell
<MS ACCESS MVP>


I have a form with a subform. In the main form(main), I have a tab that
sums
the items for each record(So far so good). QueryCount sums up 5
different
itmes and displays the count total as I want it to. Here're where I've
got
problems. I need the query to look at field [x] in subform Detail. If
the
value of x = something, then the value of the summed fields in
QueryCount(SUM1,2,3,4,SUM5) is multiplied by -1.

IF(forms![main].[x] = "something", * -1 )

My syntax with expressions is terrible. Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
My apology... I musta been asleep when I answered before.

You cannot put a calculation operator in as the argument of the IIf
function. The IIf function will return a value only. So, if you want to
multiply a result by -1 if a criterion is true, then use this:

IIf([forms]![frmMain]![frm_Main_detail_subform]![combo117]="test",[NameOfField]
* -1,[NameOfField])
--

Ken Snell
<MS ACCESS MVP>


JD said:
That resolved things somewhat, however, there is still something wrong.
The
exact expression I used is below. Note Access puts the * -1 in quotes.
Prompts no longer appear when the form loads, but no values
populate anymore. Ran the query from design view and I get error 3071.
"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables."

IIf([forms]![frmMain]![frm_Main_detail_subform]![combo117]="test","* -1 ")

Everytime I try to get rid of the "" around the multiply by -1, they come
back. Tried ' ' as well. No joy. Not sure whats up.


Ken Snell said:
Yes, IIf is the correct usage. I pasted from your original message,
sorry.

To get the correct name of subformcontrolname, open the main form in
design
view, click on the very top edge of the subform object, open the
Properties
window (icon on toolbar), and click on Other tab. You'll see the name of
the
subform control in the Name box.

--

Ken Snell
<MS ACCESS MVP>


JD said:
Thanks for the help Ken.

I tired what you suggested, however when I try to run the query it
comes
back with
"Undefined Function "IF" in expression". I modified to IIF and this
seemed
to correct that problem, however, I'm getting parameter prompts for the
values of my subform.

Can you clarify If I got this correct, it should be

IIF(forms![mainformname]![subformcontrolname]![control] = "something",
* -1)

I'm not clear on "is the name of the subform control, the actual
control
that holds the subform object" This would be the subform name as the
control
resides on the subform?

Thanks,
JD

:

Try this (assumes that Detail (bad choice of name, by the way, as
Detail
is
a reserved name in ACCESS) is the name of the subform control, the
actual
control that holds the subform object, and that x is the name of a
control
on that subform):

IF(forms![main]![Detail]![x] = "something", * -1 )

--

Ken Snell
<MS ACCESS MVP>


I have a form with a subform. In the main form(main), I have a tab
that
sums
the items for each record(So far so good). QueryCount sums up 5
different
itmes and displays the count total as I want it to. Here're where
I've
got
problems. I need the query to look at field [x] in subform Detail.
If
the
value of x = something, then the value of the summed fields in
QueryCount(SUM1,2,3,4,SUM5) is multiplied by -1.

IF(forms![main].[x] = "something", * -1 )

My syntax with expressions is terrible. Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top