Include an IF statement in Concatenated Text

  • Thread starter Thread starter Pamela
  • Start date Start date
P

Pamela

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
 
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) & " ")
 
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 said:
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

(no e-mails, please!)


Pamela said:
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
Click to expand...


.
Click to expand...
 
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 said:
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 said:
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

(no e-mails, please!)


Pamela said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
Hmm, actually, I think you're right about IIf evaluating both parts when
you're in VBA (that doesn't happen when you use IIf in a query)

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

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Pamela said:
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 said:
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

(no e-mails, please!)


Pamela said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

(no e-mails, please!)


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


.
Click to expand...
Click to expand...


.
Click to expand...
 
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 said:
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 said:
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 said:
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

:

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

(no e-mails, please!)


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


.
Click to expand...


.
Click to expand...
Click to expand...
 
<picky>
Paste that into the ControlSource of the Text6 textbox
</picky>

However, I think the issue is that VBA evaluates both sides of the IIf
statement.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Gina Whipp said:
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 said:
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 said:
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

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

:

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

(no e-mails, please!)


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


.



.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

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

:

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

(no e-mails, please!)


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


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Douglas,

Okie dokie then... *holding head down in shame*

True, that might be the issue but I looked at an old databse of mine where I
had done some similar and it works, never gave me an issue. Though... I
never used Me.Parent as part of a DLookup.

--
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

Douglas J. Steele said:
<picky>
Paste that into the ControlSource of the Text6 textbox
</picky>

However, I think the issue is that VBA evaluates both sides of the IIf
statement.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Gina Whipp said:
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 said:
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

:

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

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

:

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

(no e-mails, please!)


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


.



.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

:

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

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

:

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

(no e-mails, please!)


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


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top