Wednesday, March 28, 2012

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:oUpdate [YourTable]
set FirstName = isnull(FirstName, 'now invalid'),
LastName = isnull(LastName, 'now invalid'),
etc...|||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|||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|||Are you using SQL Server or MS Access? The isnull() function is different in TSQL than in Access VB.|||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.|||Thanks a lot for the suggestions. I'm using MS Access.

No comments:

Post a Comment