New Member - String Question? (1 Viewer)

steview

New member
Local time
Today, 17:06
Joined
Aug 29, 2016
Messages
5
Hello

I am new to the forum & have been dabbling in Access & VBA for a couple of years!

I have a question please:

I currently have a DB which works ok & also exports data into Outlook

In the strEmailmsg I have a long string which looks up data from several table columns & then adds new line after each piece of data (see below)

"Other Information Reported:" & vbNewLine & vbNewLine & [SiteNotes] & vbNewLine & [SiteNotes-Line2] & vbNewLine & [SiteNotes-Line3]&_

I would like NOT to add new line if no data present but move onto next [] data item?

Any suggestions please?
 

Ranman256

Well-known member
Local time
Today, 13:06
Joined
Apr 9, 2015
Messages
4,339
"Other Information Reported:" & iif(isNull([SiteNotes],"", vbcrlf & [sitenotes]) ....
 

steview

New member
Local time
Today, 17:06
Joined
Aug 29, 2016
Messages
5
Thank you for quick reply - but I get error message:

Expected: list separator or )

Have I explained correctly?
 

steview

New member
Local time
Today, 17:06
Joined
Aug 29, 2016
Messages
5
Please see full section of string - other similar information is present before 7 after & all works ok @ present so assume string is ok?

"Other Information Reported:" & vbNewLine & vbNewLine & [SiteNotes] & vbNewLine & [SiteNotes-Line2] & vbNewLine & [SiteNotes-Line3] & vbNewLine & [SiteNotes-Line4] & vbNewLine & [SiteNotes-Line5] & vbNewLine & [SiteNotes-Line6] & vbNewLine & [SiteNotes-Line7] & vbNewLine & [SiteNotes-Line8] & vbNewLine & _
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 13:06
Joined
Oct 17, 2012
Messages
3,276
Ranman was showing you what to do. You'll need to use an Immediate If (IIf) to check each [SiteNotes]/vbCrLf pair.
 

steview

New member
Local time
Today, 17:06
Joined
Aug 29, 2016
Messages
5
Thank you for another quick response - but I am unsure what you mean?

Could you send me an example please?
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 13:06
Joined
Oct 17, 2012
Messages
3,276
I linked you to the MSDN article on what IIf is and how to use it. Have you even looked at the article? I can't explain it to you any more clearly than MS did there. It even has the examples you're asking me for.
 

Cronk

Registered User.
Local time
Tomorrow, 04:06
Joined
Jul 4, 2013
Messages
2,770
An alternative to using iif is
([SiteNotes] + vbNewLine)
which results in Null if the data value is null
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:06
Joined
Aug 30, 2003
Messages
36,118
I'd say I'd use Cronk's method, but I wouldn't. ;) I'd fix the design, which I'm surprised nobody's mentioned. Having 8 notes fields is a normalization error.

http://www.r937.com/Relational.html

They should likely be records in a related table,
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:06
Joined
Jan 23, 2006
Messages
15,364
I agree with Paul. Get your tables normalized--it will save your lot of time and effort trying to work around a bad structure.
Good luck.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 13:06
Joined
Oct 17, 2012
Messages
3,276
Yeah, I was going to, but decided to just deal with one disaster at a time. :p
 

steview

New member
Local time
Today, 17:06
Joined
Aug 29, 2016
Messages
5
Thank you very much for all your advice - appreciated!

Cronk's solution works perfectly for now

I will read up on all advice offered - as I have lot's to learn!

A genuine thank you to all!
 

Users who are viewing this thread

Top Bottom