remove strings

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
 
E

ErezM via AccessMonster.com

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...
 
M

Mike Revis

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...
 
J

John Spencer

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...
 
M

Mike Revis

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How can I get it to NOT do what I tell it to do 7
Where clause with 2 criteria 6
Substring/Parsing Not sure what to call it! 8
ACC2003/2007: Unbound Form>Class Module - Trapping Nulls and zerolength strings. 2
Replacing hard returns in long text strings. 3
create string from column data 6
Security warning 12
Autofilter problem 9

Top