New Member - String Question?

S

steview

New member

Local time

Today, 03:58

Joined

Aug 29, 2016

Messages

5

• Aug 29, 2016

• #1

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

Yesterday, 22:58

Joined

Apr 9, 2015

Messages

4,354

• Aug 29, 2016

• #2

"Other Information Reported:" & iif(isNull([SiteNotes],"", vbcrlf & [sitenotes]) ....

 

S

steview

New member

Local time

Today, 03:58

Joined

Aug 29, 2016

Messages

5

• Aug 29, 2016

• #3

Thank you for quick reply - but I get error message:

Expected: list separator or )

Have I explained correctly?

 

S

steview

New member

Local time

Today, 03:58

Joined

Aug 29, 2016

Messages

5

• Aug 29, 2016

• #4

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

Yesterday, 22:58

Joined

Oct 17, 2012

Messages

3,276

• Aug 29, 2016

• #5

Ranman was showing you what to do. You'll need to use an Immediate If (IIf) to check each [SiteNotes]/vbCrLf pair.

 

S

steview

New member

Local time

Today, 03:58

Joined

Aug 29, 2016

Messages

5

• Aug 29, 2016

• #6

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

Yesterday, 22:58

Joined

Oct 17, 2012

Messages

3,276

• Aug 29, 2016

• #7

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

Today, 12:58

Joined

Jul 4, 2013

Messages

2,799

• Aug 29, 2016

• #8

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

Yesterday, 19:58

Joined

Aug 30, 2003

Messages

36,272

• Aug 29, 2016

• #9

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

Yesterday, 22:58

Joined

Jan 23, 2006

Messages

15,563

• Aug 29, 2016

• #10

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

Yesterday, 22:58

Joined

Oct 17, 2012

Messages

3,276

• Aug 29, 2016

• #11

Yeah, I was going to, but decided to just deal with one disaster at a time.

 

S

steview

New member

Local time

Today, 03:58

Joined

Aug 29, 2016

Messages

5

• Aug 30, 2016

• #12

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!