If-Then statements in .Body (1 Viewer)

chuckcoleman

Registered User.
Local time
Today, 12:07
Joined
Aug 20, 2010
Messages
302
Hi, I'm feeling kind of dumb this morning. I have vba code that ultimately sends and email. The .Body of the vba has line continuation characters because of the length of the message that will be in the body of the email. I need an if-then statement to include some text or not. I'm getting various messages that something is wrong in my code. Here is what I say:

vba stuff _
& "This is to acknowledge that we " _
& "received your payment of " & Format([Amount], "Currency") _
If Me.CheckNbrX Is Not Null Then
" using check number " & Me.CheckNbrX & ". "
Else "."
End If
& " Your payment was for your property" blah, blah blah

Depending on what I try, I typically get Expected End of Statement or Expected Expression

I think the problem is that Access doesn't know what to do with the text strings. If I used MsgBox as part of this maybe that would work, but I can't. I'm creating the body of the email.

What am I doing wrong?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:07
Joined
Feb 28, 2001
Messages
23,151
vba stuff _
& "This is to acknowledge that we " _
& "received your payment of " & Format([Amount], "Currency") _ ' <<----
If Me.CheckNbrX Is Not Null Then
" using check number " & Me.CheckNbrX & ". "
Else "."
End If
& " Your payment was for your property" blah, blah blah '<<----


Where I put the arrow, you have a continuation mark but the next line is your IF statement. So it wants to put the IF statement into the string (literally) except that since it is not quoted, it tries to evaluate it and gets confused. THEN the next arrow tries to concatenate more - but because there is a line intervening in this string, you have to restart the context.

I suspect you should take the approach:

Code:
body = bunch of stuff
body = body & vbcrlf & "This is to acknowledge that we " _
     "received your payment of ...."
IF Not IsNull( Me.CheckNbrX ) THEN
    body = body & " using check number...." 
ELSE
    body = body & "."
END IF
body = body & " Your payment was for ..."

... add whatever else you need.

Then when you build the letter, the body portion is now contained in the BODY string and you are good to go.
 
Last edited:

Eugene-LS

Registered User.
Local time
Today, 20:07
Joined
Dec 7, 2018
Messages
354
Try:

Code:
Dim sVal As String
  If Me.CheckNbrX Is Not Null Then
        sVal =" using check number " & Me.CheckNbrX & ".  "
   Else
         sVal = "."
   End If

vba stuff _
& "This is to acknowledge that we " _
& "received your payment of " & Format([Amount], "Currency") _
& sVal  _
& " Your payment was for your property" blah, blah blah
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:07
Joined
Sep 21, 2011
Messages
10,853
Break it down into smaller, easily handled chunks.

.Body = blah blah blah
.Body = .Body & bloh bloh bloh
.Body = .Body & blah blah blah
 

chuckcoleman

Registered User.
Local time
Today, 12:07
Joined
Aug 20, 2010
Messages
302
Try:

Code:
Dim sVal As String
  If Me.CheckNbrX Is Not Null Then
        sVal =" using check number " & Me.CheckNbrX & ".  "
   Else
         sVal = "."
   End If

vba stuff _
& "This is to acknowledge that we " _
& "received your payment of " & Format([Amount], "Currency") _
& sVal  _
& " Your payment was for your property" blah, blah blah
After I sent my question, I tried declaring CheckIsBlank with it's corresponding If-Then statement. That worked perfectly by eliminating the If-Then within the .Body.

Thank you Eugene and Doc Man for your help!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:07
Joined
Feb 28, 2001
Messages
23,151
No problem, glad to contribute.
 

Cronk

Registered User.
Local time
Tomorrow, 03:07
Joined
Jul 4, 2013
Messages
2,684
For the future you cannot embed an if/then/else/endif within a line of code. However, you can insert an IIF statement which has the syntax

iif(condition, result if true, result if false)

So you could have used
Code:
vba stuff _
& "This is to acknowledge that we " _
& "received your payment of " & Format([Amount], "Currency") _
  IIf(Me.CheckNbrX Is Not Null, " using check number " & Me.CheckNbrX & ".  ", "." ) _

 & " Your payment was for your property" blah, blah blah
 

Users who are viewing this thread

Top Bottom