How can I get it to NOT do what I tell it to do

[Mike Revis]

Hi Group
Access 2000 Win XP pro

I have an unbound text box (txtDescription) on a report that I use the
following code in.
All fields are text type.

In the VB editor I have

txtDescription = IIf(Len(Trim([UNorIDNumber]) & "") = 0, "",
[UNorIDNumber] & ", ") & _
IIf(Len(Trim([PackingGroup]) & "") = 0, "" & vbCrLf, [PackingGroup] &
vbCrLf)

This works perfectly. To perfectly.

As written it returns UN1111, III

If there is no PackingGroup it returns UN1111,

As expected.

Is there a way that if there is no PackingGroup the apostrophe can be
suppressed?

If no PackingGroup the desired result would be UN1111

As always any thoughts, comments or suggestions are welcome.

Best regards,

Mike

 

[Douglas J. Steele]

Don't put the comma in unless you know it's needed:

txtDescription = IIf(Len(Trim([UNorIDNumber]) & "") = 0, "",
[UNorIDNumber]) & _
IIf(Len(Trim([PackingGroup]) & "") = 0, "" & vbCrLf, ", " & [PackingGroup]
& vbCrLf)

 

[Mike Revis]

Thanks Doug,
I was so locked in to the comma following the UNorIDNumber I couldn't
realize that it also preceded the PackingGroup.

Mike

Don't put the comma in unless you know it's needed:

txtDescription = IIf(Len(Trim([UNorIDNumber]) & "") = 0, "",
[UNorIDNumber]) & _
IIf(Len(Trim([PackingGroup]) & "") = 0, "" & vbCrLf, ", " & [PackingGroup]
& vbCrLf)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Group
Access 2000 Win XP pro

I have an unbound text box (txtDescription) on a report that I use the
following code in.
All fields are text type.

In the VB editor I have

txtDescription = IIf(Len(Trim([UNorIDNumber]) & "") = 0, "",
[UNorIDNumber] & ", ") & _
IIf(Len(Trim([PackingGroup]) & "") = 0, "" & vbCrLf, [PackingGroup] &
vbCrLf)

This works perfectly. To perfectly.

As written it returns UN1111, III

If there is no PackingGroup it returns UN1111,

As expected.

Is there a way that if there is no PackingGroup the apostrophe can be
suppressed?

If no PackingGroup the desired result would be UN1111

As always any thoughts, comments or suggestions are welcome.

Best regards,

Mike

 

[Marshall Barton]

Access 2000 Win XP pro

I have an unbound text box (txtDescription) on a report that I use the
following code in.
All fields are text type.

In the VB editor I have

txtDescription = IIf(Len(Trim([UNorIDNumber]) & "") = 0, "",
[UNorIDNumber] & ", ") & _
IIf(Len(Trim([PackingGroup]) & "") = 0, "" & vbCrLf, [PackingGroup] &
vbCrLf)

This works perfectly. To perfectly.

As written it returns UN1111, III

If there is no PackingGroup it returns UN1111,

As expected.

Is there a way that if there is no PackingGroup the apostrophe can be
suppressed?

If no PackingGroup the desired result would be UN1111

A slight variation of Doug's suggestion that suppresses the
extra comma even if UNorIDNumber is blank.

txtDescription = Mid( _
IIf(Len(Trim(UNorIDNumber) & "") = 0, _
"", ", " & UNorIDNumber) _
& IIf(Len(Trim(PackingGroup) & "") = 0, _
"", ", " & PackingGroup) _
& vbCrLf, 3)

 

[Mike Revis]

Thanks Marshall
That was my next question that I didn't know I had until I got Doug's
response.

Mike

Access 2000 Win XP pro

I have an unbound text box (txtDescription) on a report that I use the
following code in.
All fields are text type.

In the VB editor I have

txtDescription = IIf(Len(Trim([UNorIDNumber]) & "") = 0, "",
[UNorIDNumber] & ", ") & _
IIf(Len(Trim([PackingGroup]) & "") = 0, "" & vbCrLf, [PackingGroup] &
vbCrLf)

This works perfectly. To perfectly.

As written it returns UN1111, III

If there is no PackingGroup it returns UN1111,

As expected.

Is there a way that if there is no PackingGroup the apostrophe can be
suppressed?

If no PackingGroup the desired result would be UN1111

A slight variation of Doug's suggestion that suppresses the
extra comma even if UNorIDNumber is blank.

txtDescription = Mid( _
IIf(Len(Trim(UNorIDNumber) & "") = 0, _
"", ", " & UNorIDNumber) _
& IIf(Len(Trim(PackingGroup) & "") = 0, _
"", ", " & PackingGroup) _
& vbCrLf, 3)

 

[Douglas J. Steele]

Another approach is to take advantage of the fact that + propagates Nulls,
while & doesn't:

txtDescription = ([UNorIDNumber] + ", ") & [PackingGroup] & vbCrLf

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Marshall
That was my next question that I didn't know I had until I got Doug's
response.

Mike

Access 2000 Win XP pro

I have an unbound text box (txtDescription) on a report that I use the
following code in.
All fields are text type.

In the VB editor I have

txtDescription = IIf(Len(Trim([UNorIDNumber]) & "") = 0, "",
[UNorIDNumber] & ", ") & _
IIf(Len(Trim([PackingGroup]) & "") = 0, "" & vbCrLf, [PackingGroup] &
vbCrLf)

This works perfectly. To perfectly.

As written it returns UN1111, III

If there is no PackingGroup it returns UN1111,

As expected.

Is there a way that if there is no PackingGroup the apostrophe can be
suppressed?

If no PackingGroup the desired result would be UN1111

A slight variation of Doug's suggestion that suppresses the
extra comma even if UNorIDNumber is blank.

txtDescription = Mid( _
IIf(Len(Trim(UNorIDNumber) & "") = 0, _
"", ", " & UNorIDNumber) _
& IIf(Len(Trim(PackingGroup) & "") = 0, _
"", ", " & PackingGroup) _
& vbCrLf, 3)

 

[Marshall Barton]

Another approach is to take advantage of the fact that + propagates Nulls,
while & doesn't:

txtDescription = ([UNorIDNumber] + ", ") & [PackingGroup] & vbCrLf

But, Doug, that leaves an extra trailing comma when
PackingGroup is a ZLS, Null or blanks.

 

[Douglas J. Steele]

Another approach is to take advantage of the fact that + propagates Nulls,
while & doesn't:

txtDescription = ([UNorIDNumber] + ", ") & [PackingGroup] & vbCrLf

But, Doug, that leaves an extra trailing comma when
PackingGroup is a ZLS, Null or blanks.

Yeah, I think you're correct. Marsh. I was too lazy to test...