remove strings

  • Thread starter Thread starter Mike Revis
  • Start date Start date
M

Mike Revis

Hi Group,
Win xp pro. Access 2007.

I thought I knew how to do this but I am wrong.
txtDescription is an unbound text box.
How can I get rid of the strings when there is no data in a field?

If only one field has data then I get the obvious.

, ProperShippingName, (), , , , , FP= C-cc

Each field with data must be separated with a "comma space".


txtDescription = [UNorIDNumber] & ", " & _
[ProperShippingName] & ", " & _
"(" & [TechnicalName] & ")" & ", " & _
[Class] & ", " & _
[SubClass] & ", " & _
[PackingGroup] & ", " & _
[Authorization] & ", " & _
"FP= " & [Flashpoint] & " C-cc"

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike
 
Thank you Erez.
That works.
I thought I knew that but I guess I just had a brain freeze.

Is there any way to not show the "comma" after the field if there is only
one field with data.

I am getting
ProperShippingName, (comma)
if there is only the one field with data.

Thanks for your time.

Best regards,
Mike

ErezM via AccessMonster.com said:
hi
if no data means the values are NULL then use this:
txtDescription = [UNorIDNumber] & ", " & _
[ProperShippingName] & ", " & _
IIf(IsNull([TechnicalName]), "", "(" & [TechnicalName] &
")")
& ", " & _
[Class] & ", " & _
[SubClass] & ", " & _
[PackingGroup] & ", " & _
[Authorization] & ", " & _
IIf(IsNull([Flashpoint]), "", "FP= " & [Flashpoint] & "
C-cc")


it checks whether the value is null, and adds it (and the prefix and
suffix)
only if it's not

hope this is what you wanted
Erez

Mike said:
Hi Group,
Win xp pro. Access 2007.

I thought I knew how to do this but I am wrong.
txtDescription is an unbound text box.
How can I get rid of the strings when there is no data in a field?

If only one field has data then I get the obvious.

, ProperShippingName, (), , , , , FP= C-cc

Each field with data must be separated with a "comma space".

txtDescription = [UNorIDNumber] & ", " & _
[ProperShippingName] & ", " & _
"(" & [TechnicalName] & ")" & ", " & _
[Class] & ", " & _
[SubClass] & ", " & _
[PackingGroup] & ", " & _
[Authorization] & ", " & _
"FP= " & [Flashpoint] & " C-cc"

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike
Click to expand...
Click to expand...
 
Simplest way is to add the comma and space BEFORE every item and then
use the mid function to return the string starting at the third character.

txtDescription = MID _
(IIF(IsNull(UNorIDNumber),"",", " & [UNorIDNumber]) & _
IIF(IsNull(ProperShippingName),"",", " & [ProperShippingName]) & _
IIF(IsNull(TechnicalName),"",", (" & [TechnicalName] & ")") & _
IIF(IsNull(Class),"",", " & [Class]) & _
IIF(IsNull(SubClass),"",", " & [SubClass]) & _
IIF(IsNull(PackingGroup),"",", " & [PackingGroup]) & _
IIF(IsNull(Authorization),"",", " & [Authorization]) & _
IIF(IsNull(FlashPoint),"",", FP= " & [Flashpoint] & " C-cc"),3)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Mike said:
Thank you Erez.
That works.
I thought I knew that but I guess I just had a brain freeze.

Is there any way to not show the "comma" after the field if there is only
one field with data.

I am getting
ProperShippingName, (comma)
if there is only the one field with data.

Thanks for your time.

Best regards,
Mike

ErezM via AccessMonster.com said:
hi
if no data means the values are NULL then use this:
txtDescription = [UNorIDNumber] & ", " & _
[ProperShippingName] & ", " & _
IIf(IsNull([TechnicalName]), "", "(" & [TechnicalName] &
")")
& ", " & _
[Class] & ", " & _
[SubClass] & ", " & _
[PackingGroup] & ", " & _
[Authorization] & ", " & _
IIf(IsNull([Flashpoint]), "", "FP= " & [Flashpoint] & "
C-cc")


it checks whether the value is null, and adds it (and the prefix and
suffix)
only if it's not

hope this is what you wanted
Erez

Mike said:
Hi Group,
Win xp pro. Access 2007.

I thought I knew how to do this but I am wrong.
txtDescription is an unbound text box.
How can I get rid of the strings when there is no data in a field?

If only one field has data then I get the obvious.

, ProperShippingName, (), , , , , FP= C-cc

Each field with data must be separated with a "comma space".

txtDescription = [UNorIDNumber] & ", " & _
[ProperShippingName] & ", " & _
"(" & [TechnicalName] & ")" & ", " & _
[Class] & ", " & _
[SubClass] & ", " & _
[PackingGroup] & ", " & _
[Authorization] & ", " & _
"FP= " & [Flashpoint] & " C-cc"

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike
Click to expand...
Click to expand...
Click to expand...
 
Thank you John,
Works great.

Best regards,
Mike

John Spencer said:
Simplest way is to add the comma and space BEFORE every item and then use
the mid function to return the string starting at the third character.

txtDescription = MID _
(IIF(IsNull(UNorIDNumber),"",", " & [UNorIDNumber]) & _
IIF(IsNull(ProperShippingName),"",", " & [ProperShippingName]) & _
IIF(IsNull(TechnicalName),"",", (" & [TechnicalName] & ")") & _
IIF(IsNull(Class),"",", " & [Class]) & _
IIF(IsNull(SubClass),"",", " & [SubClass]) & _
IIF(IsNull(PackingGroup),"",", " & [PackingGroup]) & _
IIF(IsNull(Authorization),"",", " & [Authorization]) & _
IIF(IsNull(FlashPoint),"",", FP= " & [Flashpoint] & " C-cc"),3)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Mike said:
Thank you Erez.
That works.
I thought I knew that but I guess I just had a brain freeze.

Is there any way to not show the "comma" after the field if there is only
one field with data.

I am getting
ProperShippingName, (comma)
if there is only the one field with data.

Thanks for your time.

Best regards,
Mike

ErezM via AccessMonster.com said:
hi
if no data means the values are NULL then use this:
txtDescription = [UNorIDNumber] & ", " & _
[ProperShippingName] & ", " & _
IIf(IsNull([TechnicalName]), "", "(" & [TechnicalName] &
")")
& ", " & _
[Class] & ", " & _
[SubClass] & ", " & _
[PackingGroup] & ", " & _
[Authorization] & ", " & _
IIf(IsNull([Flashpoint]), "", "FP= " & [Flashpoint] & "
C-cc")


it checks whether the value is null, and adds it (and the prefix and
suffix)
only if it's not

hope this is what you wanted
Erez

Mike Revis wrote:
Hi Group,
Win xp pro. Access 2007.

I thought I knew how to do this but I am wrong.
txtDescription is an unbound text box.
How can I get rid of the strings when there is no data in a field?

If only one field has data then I get the obvious.

, ProperShippingName, (), , , , , FP= C-cc

Each field with data must be separated with a "comma space".

txtDescription = [UNorIDNumber] & ", " & _
[ProperShippingName] & ", " & _
"(" & [TechnicalName] & ")" & ", " & _
[Class] & ", " & _
[SubClass] & ", " & _
[PackingGroup] & ", " & _
[Authorization] & ", " & _
"FP= " & [Flashpoint] & " C-cc"

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike
Click to expand...
Click to expand...
Click to expand...
 
Back
Top