Include an IF statement in Concatenated Text

Pamela,

No pain, no gain! Glad you got it working!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Pamela" wrote in message
...
Thanks so much for your help on this. I ended up expanding my brain to
try
to come up with other ways to accomplish this and ended up with doing it
in
my query. I think it probably is easier and now all I need in this
expression (which is already SO long that I'm getting the "Too many line
extensions" error) is the Dlookup portion. It took some time and
tweaking,
but that's what it takes to learn sometimes.

Thanks again!!!

"Gina Whipp" wrote:

Pamela,

Let's try this, copy paste the below in the *Text6* textbox...

=IIf(Not IsNull(Me.Parent!ShopName),DLookup("Expr1", "qryShopReturn",
"ShopID =" & [ShopName]), "The Owner has not yet chosen a repair shop.")

Do not include Me.[Text6] part, just in case you were thinking I fogot
that.
--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Pamela" wrote in message
...
Hi Gina,

I'm getting an error on that IIf statement suggestion you made that
"Argument is not optional." I'll give you a brief rundown (maybe too
much
info but better than not enough).

I have a main data entry form "frmInput" which has subforms nested
inside
each other in the following order: sfrmInspection, sfrmLocation,
frmDamage
and sfrmEstimate. My combo box ShopName is on subform 4 and is
populated
by
ltblShop where ShopID is AutoNumber (pk) and the bound field but the
ShopName
itself is displayed in the cbo.

I can't say I understand the difference between Null and empty string -
but
I'd like it to be that if the field is left blank either because it
tabbed
past or not even clicked on that it will come up with the same answer.
The
code that I originally pasted is only a part of a very long code and
luckily,
the only part I'm having trouble with that this point. The entire
purpose
of
this project is to take the data entered and compile it into about a 15
sentence summary of the data which is then copied and pasted into
another
application that is why I may forget an paste additional spacing
characters
or line continuation (& _) symbols. Sorry for that confusion. I hope
this
helps you help me! : )
Thanks so much and I'm sorry for re-posting. I really appreciate your
help!
Pamela

"Gina Whipp" wrote:

Pamela,

Before answering your post I'm going to make a suggestion... PLEASE
stop
reposting the same question over and over again. Stay with the same
thread.
We are volunteers and REanswering the same question takes time away
from
other Posters. Your post is not abandoned or forgotten but we
sometimes
can't get to it as fast as you can post it. Example being after I
post
this
answer I'm going to fix something to eat so I might not answer right
away
but I'll be back OR someone will be back, so hang in there...

Now, let's move on...

Your DLookup... DLookup("Expr1", "qryShopReturn", "ShopID = " &
Me.ShopName) & " ")

1. Is ShopID Text or numeric?
2. Is ShopName Null or Empty? It might be better to test for both

Me.[Text6] = _
IIf (IsNull(Me.Parent!ShopName) or Me.Parent!ShopName = "", _
"The owner has not yet chosen a repair shop.", _
DLookup("Expr1", "qryShopReturn", "ShopID = " & Me.ShopName) &
"")

3. Not sure why you have... & " "... so I closed that up in my
example
but not sure if both fields are text or one text and one numeric which
then
that would be another issue.

4. Is ShopName a combo box? If yes, which is the Bound Column.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Pamela" wrote in message
...
Thanks for your input. I used your code but am now getting a syntax
error
that there's a missing operator in query expression 'ShopID ='.
From
this
it almost seems that it isn't reading the True statement in our
expression
which should disregard that part of the expression altogether. Any
other
ideas how to fix this?

In reviewing your response , I noticed that I had my references to
the
control off a bit. I believe I have corrected them now in the
context
of
the
new code you supplied. The control ShopName is on the same subform
as
the
text box on which the code is running so I think Me.ShopName should
work.
Thanks so much for your help on this!
Pamela

"Douglas J. Steele" wrote:

Errors that jump out at me are

1) You cannot use = when comparing to Null
2) You cannot use an If statement in that manner.

Try:

Me.[Text6] = _
IIf (IsNull(Me.Parent!ShopName), _
"The owner has not yet chosen a repair shop.", _
DLookup("Expr1", "qryShopReturn", "ShopID = " & Me.ShopName) &
"
")


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Pamela" wrote in message
...
I have a text box that I'm using, although hidden from the user,
to
Dlookup
and Concatenate text from all the various entries on my
form/subforms.
Is
there a way to include an If...Then statement in this? It seems
that
if a
DLookup will work then an If should also. Here's a part of my
code:
Me.[Text6] = _
If (Me.Parent!ShopName = Null) Then "The owner has not yet chosen
a
repair
shop." _
Else DLookup("Expr1", "qryShopReturn", "ShopID = " &
Me.ShopName)
&
"
"
The error I'm getting even upon entering this code is "Expected:
expression".
Thanks for any help!
Pamela


.



.



.