Check if all characters are spaces- Access 2003

I have a program that brings in data from another system.  However, if ther eis nothing in the field, rather than brining in null, it seems to populate with a lot of spaces - how can I check to see if all characters in a field a simply spaces and set the field to null?

Sandra
Sandra SmithRetiredAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
Use this expression:

IIf(Len(Trim([YourPerhapsEmptyField])) = 0, Null, Trim([YourPerhapsEmptyField]))

/gustav
0
 
Rey Obrero (Capricorn1)Commented:
try this query

select [fieldName], len([fieldName])
from tableName
where len([fieldName]) >0

check the resulting query..

maybe they are not spaces, but some unprintable characters like tab, CR, LF

to set the fields to Null, use an update query, but you need to find out first what those unprintable characters
0
 
jerryb30Commented:
Try trim(fieldName)
0
 
Sandra SmithRetiredAuthor Commented:
This worked as I really did need to check to see if it was 0 length as some were and some had spaces in both front and at the end of the imported string.

Sandra
0
 
Gustav BrockCIOCommented:
Great!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.