Strange Query Problem


D

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
 
Ad

Advertisements

J

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]
 
J

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]
 
G

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
 
Ad

Advertisements

D

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
 
Ad

Advertisements

D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top