Getting rid of NULL value

[darude]

Hello,
I have the fields StudentFirstName and StudentLastName. Some of the names have various numbers of NULL values following the name.

When I use Trim([StudentFirstName]) it leaves 1 NULL value following some of the names, not all.

How do I eliminate the NULL values so the concatenation looks correct?

Thank you in advance.

 

[dhookom]

Access table would not store spaces at the end of text values. Where is your data coming from? What makes you think there are nulls stored in your field values?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[GingerR]

=iif(Right(StudentName,1) is null,Trim(Left([StudentName],Len([StudentName])-1)),Trim([StudentName]))

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[darude]

Thanks for the responses. To answer DHookum, the data originally comes in from excel and when I do a concatenation there is an extra space after some of the names.

 

[JoeAtWork]

Some of the names have various numbers of NULL values following the name.

I don't think this is possible. Null means "unknown" or "absence of data", therefore you can't combine it with data. A field is either Null or it has data.

I suspect what you are calling Null is some other character that doesn't have a visual representation, such as a carriage return or a line feed.

If you can figure out what the extra character is, you could use the Replace function to get rid of it, for example

Replace([StudentFirstName], Chr(13), "")

will remove any carriage returns.