Solved

Check if all characters are spaces- Access 2003

Posted on 2013-01-29
5
296 Views
Last Modified: 2013-02-04
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
0
Comment
Question by:ssmith94015
5 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 
LVL 26

Expert Comment

by:jerryb30
Comment Utility
Try trim(fieldName)
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
Use this expression:

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

/gustav
0
 

Author Closing Comment

by:ssmith94015
Comment Utility
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
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Great!

/gustav
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now