Solved

Check if all characters are spaces- Access 2003

Posted on 2013-01-29
5
307 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38833356
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
ID: 38833406
Try trim(fieldName)
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 38834391
Use this expression:

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

/gustav
0
 

Author Closing Comment

by:ssmith94015
ID: 38851766
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
ID: 38852126
Great!

/gustav
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

832 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