Strange Query Problem

[Daveo]

Hi there,

I have a query which has a calculated field:

SupervisorName: IIf(IsNull([SupervisorTitle]),[SupervisorFirstname] & "
" & [SupervisorLastname],[SupervisorTitle] & " " &
[SupervisorFirstname] & " " & [SupervisorLastName])

The field does exactly what it's supposed to do for every record apart
from one. Where it should show "Mrs Jane Bloggs" it just shows "Mrs"
i.e. the forename and surname parts are missing. There are over 700
records in the database and this is the only one it does it for.

I've tried deleting the title, surname and forename and re-entering
them with no success.

Can anyone shed any light?

Many thanks - David

 

[Guest]

I would check the database to see if there is a record with a title and no
name.

 

[Guest]

I would check the database to see if there is a record with a title and no
name.

 

[John Vinson]

Hi there,

I have a query which has a calculated field:

SupervisorName: IIf(IsNull([SupervisorTitle]),[SupervisorFirstname] & "
" & [SupervisorLastname],[SupervisorTitle] & " " &
[SupervisorFirstname] & " " & [SupervisorLastName])

The field does exactly what it's supposed to do for every record apart
from one. Where it should show "Mrs Jane Bloggs" it just shows "Mrs"
i.e. the forename and surname parts are missing. There are over 700
records in the database and this is the only one it does it for.

I've tried deleting the title, surname and forename and re-entering
them with no success.

This sounds like a data problem.

There's actually a simpler expression which takes advantage of the
fact that both the & and the + operators concatenate strings, but the
+ operator "propagates nulls" while the & operator treats a NULL as a
zero length string:

SupervisorName: ([SupervisorTitle] + " ") & [SupervisorFirstName] & "
" & [SupervisorLastName]


John W. Vinson[MVP]

 

[John Vinson]

Hi there,

I have a query which has a calculated field:

SupervisorName: IIf(IsNull([SupervisorTitle]),[SupervisorFirstname] & "
" & [SupervisorLastname],[SupervisorTitle] & " " &
[SupervisorFirstname] & " " & [SupervisorLastName])

The field does exactly what it's supposed to do for every record apart
from one. Where it should show "Mrs Jane Bloggs" it just shows "Mrs"
i.e. the forename and surname parts are missing. There are over 700
records in the database and this is the only one it does it for.

I've tried deleting the title, surname and forename and re-entering
them with no success.

This sounds like a data problem.

There's actually a simpler expression which takes advantage of the
fact that both the & and the + operators concatenate strings, but the
+ operator "propagates nulls" while the & operator treats a NULL as a
zero length string:

SupervisorName: ([SupervisorTitle] + " ") & [SupervisorFirstName] & "
" & [SupervisorLastName]


John W. Vinson[MVP]

 

[Guest]

David:

Any chance there could be an inadvertent carriage return/line feed at the
start of Mrs Bloggs' SupervisorFirstname field's value? This could mean
that you are simply not seeing the rest of the expression. I can't think of
any other explanation other than that the row might be corrupt, in which case
deleting the row and entering a new one might be the only answer.

Re-entering the row could be slightly tricky if the key is an autonumber
and/or referential integrity is enforced. You'd have to remove the
referential integrity enforcement first, and if its an autonumber you'd have
to compact the database containing the table after deleting the row then
insert the row with an SQL statement so that the original value can be
inserted into the autonumber field. Finally you'd re-enforce referential
integrity.

Ken Sheridan
Stafford, England

 

[Daveo]

John - Many thanks for the tip.

Ken - There was no carriage return but deleting and re-creating the
record did the trick!

Thanks again - David

 

[Daveo]

John - Many thanks for the tip.

Ken - There was no carriage return but deleting and re-creating the
record did the trick!

Thanks again - David