isNull Help!!!

[DrSql]

I am trying to concatenate Title, FirtsName, MI & LastName in Access. I have created and expressions but small concern.
There are few people dont have a MI so using the first syntax, it get ommited for null MI's
Syntax 1:
select FullName: [Title]+' '+[FirstName]+' '+[MI]+'. '+[LastName] from EmpTable
or
and error for this syntax.
Syntax 2:
select FullName: [Title]+' '+[FirstName]+' '+IsNull([MI])+'. '+[LastName] from EmpTable

Any help would be appreciated.
Thanks

Dr.Sql
Good Luck.

 

[dhookom]

You should be using ampersands [red]&[/red] for concatenating strings. Try:

FullName: [Title] & ' ' & [FirstName] & ' ' & IIf(IsNull([MI]),'',[MI] & '. ') & [LastName]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Golom]

Try

Select NZ([Title]) & ' ' & [FirstName] & ' ' & IIF(IsNull([MI]), "", [MI] & '. ') & [LastName] As FullName
From EmpTable

IsNull returns TRUE or FALSE ... not the value of the variable.

 

[PHV]

Another way:

FullName: Title & ' ' & FirstName & ' ' & Nz(MI+'. ','') & LastName

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

I can name that tune in...

FullName: Title & ' ' & FirstName & ' ' & [MI]+ '. ' & LastName

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Good catch Duane.

 

[DrSql]

Thanks all for your quick reponses.

Dr.Sql
Good Luck.