Access 2003 - Help with Iif(IsNull) statements

JeffBarker

Registered User.
Local time
Today, 17:18
Joined
Dec 7, 2010
Messages
130
Hi guys,

I'm trying to return Mr Stevenson (if we're missing the First Name), Mr James Stevenson (if we have the First Name) or James Stevenson (if we're missing the person's Title) as an alias in an Access query, but can't quite get it right.

This is what I currently have:

Contact: IIf(IsNull([First Name]),[Title] & " " & [Surname],[Title] & " " & [First Name] & " " & [Surname]) Or IIf(IsNull([Title]),[First Name] & " " & [Surname])

But all it returns is -1.

Can anyone assist, sil vous plait??

Thanks,

Jeff.
 
Something like:
Code:
Contact: ltrim$(rtrim$([Title] & " " & [First Name] & " ") & " " & [Surname])
 
Something like:
Code:
Contact: ltrim$(rtrim$([Title] & " " & [First Name] & " ") & " " & [Surname])

That's brilliant, thanks very much - would you just be able to explain a little bit about how that works please??

Added to your rep, btw! :)
 
Just noticed I may have added one too many & " ". You can get rid of the one just after [First Name].

Rtrim$() - will remove any trailing spaces in a String - aka Right Trim
LTrim$() - will remove any leading spaces in a String - aka Right Trim

With the $ it can only handle Text datatypes (or Strings) and since we are concatenating a space to each field, it will always be a String.

If you look at the bracketing you might be able to work out what's going on. If Title is Null, RTrim() will deal with the space, if FirstName is Null, LTrim will handle the space. It will work in all combinations of Null fields.
 
If you look at the bracketing you might be able to work out what's going on. If Title is Null, RTrim() will deal with the space, if FirstName is Null, LTrim will handle the space. It will work in all combinations of Null fields.

Right - excellent, it makes sense!!

Thanks again!! :)
 

Users who are viewing this thread

Back
Top Bottom