John:
If I remove the expression, I don't get the prompt for Punch.
To review, as this still isn't working and is very frustrating, my
field
names now are [Price] and [Punch] and are both currency fields.
I have reviewed my table, query, and report and everything looks ok as
far
as the new field names being correct everywhere..
What I want to do is the same, have one not show up if the other
appears.
If
there is a value in the Price field, I don't want the Punch field to
show
up,
or its label either. And vice versa.
The following code that someone on this forum suggested, and which I
modified, still doesn't work.
=IIf(IsNull([Price]),"Per Punch Value up to: " &
Format([Punch,"Currency"),"Value up to " &
Format([Price],"Currency"))
I still get the prompt for Punch when I run it, and it doesn't work.
If there IS a Price, the Price field shows up with the correct price,
but
if
there ISN'T a price, the price field still shows up with a zero value,
and
the Punch field doesn't show up at all, even hough there is a value in
it.
Sorry this is so complicated but this is the only place I rely on for
help -
thanks.
:
Don't see anything there that would cause a problem like you mention.
If you remove the expression completely, do you still get the prompt
for
Punch? If so, then the prompt is coming from some place else.
message
=IIf(IsNull([Price]),"Per Punch Value up to: " & Format([Punch
Value],"Currency"),"Value up to " & Format([Price],"Currency"))
It is all one one line and I don't see any extra characters.
Do you think if I changed the name of my field [Punch Value] to just
[Punch]
that might solve it, as previously Duane Hookum has said "BTW: Value
is
not a
good name for a field since many controls have a "value" property ?
Thanks
:
Question: Did you type the entry all on one line? IF you cut and
pasted
from the posting there may be an extraneous character in the
expression.
Try typing the entry in.This should all be one line.
=IIf(IsNull([Price]),"Per Punch Value up to: " & Format([Punch
Value],
"Currency"), "Value up to " & Format([Price],"Currency"))
message
Ok I got rid of that error msg, and it kind of works using the
below
code:
=IIf(IsNull([Price]),"Per Punch Value up to: " & Format([Punch
Value],"Currency"),"Value up to " & Format([Price],"Currency"))
However, it prompts me for a "Punch" which I can just click
through,
and
then the Price field will show up correctly, but not the Punch
value
field,
if ones exists.
:
I cut and pasted that in but it still doesn't work. I get a
error
msg
box
that starts with "Extra ) in query expression ..."
:
I'm not sure why you didn't provide this information in your
original
post.
If one or the other is always null you can try a single text
box
with a
control source like:
=IIf(IsNull([Price]) , "Per Punch Value up to: " &
Format([Punch
Value],"Currency"), "Price = " & Format([Price],"Currency")
Make sure the name is not the same as a field name.
--
Duane Hookom
MS Access MVP
in
message
I don't understand your syntax to use:
My actual Field names are [Price] and [Punch Value]:
I need one field or the other to show up.
If the Price field has a value, then the text box should say
something
like
"Price = $ 20.00", but if the Punch Value field instead has
a
value,
the
textbox should say "Per Punch Value up to: $ 20.00"
I tried taking out both the 2 existing fields and labels,
and
adding
a
text
box with the below Control Source:
Text 136 =Nz([Price], [Punch Value])
But then I get this error message:
Syntax Error (comma) in Query Expression '[ =NZ([Price],
[Punch
Value])].
And get a similar Syntax eror box tyring to use your 2nd
suggestion
:
Use a single text box with a control source like:
=Nz([Value], [Punchvalue])
You can change the label to a text box and set its control
source
to:
=IIf(IsNull([Value]),"Punch Value:","Value:")
BTW: Value is not a good name for a field since many
controls
have a
"value"
property.
--
Duane Hookom
MS Access MVP
"FrankSpokane" <
[email protected]>
wrote
in
message
I have 2 fields in a table:
Value - valueof a coupon
Punchvalue - value of this coupon if it is a special
Punch
coupon
Only one of these fields are ever filled in per record,
but
it
might be
one
or the other.
I then do a query-based report which has both fields on
it,
but
I
only
want
THE ONE FIELD TO SHOW UP THAT HAS A VALUE IN IT - NOT THE
OTHER.
Each field has a Fieldname label attached to it. I've
tried
playing
with
the
invisible feature but got nowhere.
Is there a way to do this? Thanks much.
