Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Strange Query Problem

Discussion in 'Information Technology' started by Daveo, Jul 28, 2006.

  1. Daveo

    Daveo
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    SciTech Strange Blue World: Alien Planet's True Color Revealed, a First Breaking News Jul 12, 2013
    Truth Stranger Than Fiction! Interfaith Dialogues May 26, 2012
    Dr. Strangestlove (Or How I learned To Stop Worrying And Love My Computer) Inspirational Stories May 22, 2011
    Estranged Women Seek Help from Ramoowalia! Hard Talk Mar 9, 2011
    Nature Stranger Than Paradise (The Adivassi and FRA) Breaking News Jun 16, 2010

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

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

    "Daveo" wrote:

    > 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
    >
    >
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 20 Jun 2006 07:50:33 -0700, "Daveo" <writetodaveo@gmail.com> wrote:

    >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]
     
  5. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    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" wrote:

    > 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
    >
    >
     
  6. Daveo

    Daveo
    Expand Collapse
    Guest

    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
     
  7. Daveo

    Daveo
    Expand Collapse
    Guest

    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
     

Share This Page