Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Posts
    5

    Red face Unanswered: Replacing blank records with text

    Hi there,

    I'm in a bit of a jam here and will appreciate any help.

    I need the SQL code to replace a record if the record is empty.

    For instance, I have about 7 columns containing over 40K records. In the firstname field, some records are blank. I need to replace all the blank firstname fields with this: 'now invalid' (without the quotes)

    What would be the best way to achieve this?

    Thanks

    newbie

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Update [YourTable]
    set FirstName = isnull(FirstName, 'now invalid'),
    LastName = isnull(LastName, 'now invalid'),
    etc...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2006
    Posts
    5

    Red face RE: Replacing blind records with text

    Thanks alot for your response blindman.

    I didnt add that for the blank firstname fields I want to replace, all their lastnames contain valid data which I do not want to replace. all the lastnames are 'Design Professional'

    So i modified the sql like this:

    Update [My Table]
    set FirstName = isnull(FirstName, 'Now Invalid')
    where lastname = 'Design Professional'

    I think the above query has a problem because it generated an error saying:
    saying 'wrong number of arguements used with function in query expression isnull(FirstName, 'now invalid')

    can you help with this?

    Thank you very much

  4. #4
    Join Date
    Mar 2006
    Posts
    5

    Red face RE: Replacing blank (oops) records with text

    Thanks alot for your response blindman.

    I didnt add that for the blank firstname fields I want to replace, all their lastnames contain valid data which I do not want to replace. all the lastnames are 'Design Professional'

    So i modified the sql like this:

    Update [My Table]
    set FirstName = isnull(FirstName, 'Now Invalid')
    where lastname = 'Design Professional'

    I think the above query has a problem because it generated an error saying:
    saying 'wrong number of arguements used with function in query expression isnull(FirstName, 'now invalid')

    can you help with this?

    Thank you very much

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you using SQL Server or MS Access? The isnull() function is different in TSQL than in Access VB.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you are in Access look up NZ in help. Same thing as the T-SQL ISNull basically. IsNull in Access means something totally different to IsNull in T-SQL.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2006
    Posts
    5

    Thanks

    Thanks a lot for the suggestions. I'm using MS Access.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •