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.

Repeating Record

How can I display same repeating record in a table?
Any help will be appreciated.any idea about this question?|||I guess you mean duplicates...

based on a key?

SELECT Col1, Col2
FROM myTable99
GROUP BY Col1, Col2
HAVING COUNT(*) > 1|||hi Brett,

Thank you ..it worked

repeating identity

When using Access - you had to be careful about the autoincrement feature. If you delete a record from a table ( autoincrement id=1000) -then compact/repair - then add a new record to that table - the autoincrement field will say 1000 - if that autoincrement value was used to uniquely identify something - it is no longer unique. This is all background for my question...

Does SQL 2000 do this also ? I must have read somewhere that it doesn't - since I have code that moves records around ( delete from one table - insert into other), but the other night, I was awakened by the thought that SQL2000 does the same as Access - i.e. repeating identity after compact/repair

Do I need to worry ?The only thing you need to worry about is if you happen to use theSET IDENTITY_INSERT ON command when inserting records.

This command will allow you to specify a value for the identity column when inserting a record instead of allowing SQL to assign one. And SQL won't know that your assigned numbers are already used and could attempt to reuse them later. This would only happen if you were sloppy in your assignment -- and it is not likely you would need to ever use the SET IDENTITY_INSERT anyway.

FWIW,
Terri|||thank you everyone : ) After re-reading my original post - I thought maybe I should reword it , just to make sure.

I have a table for service calls that has the identity field used for the service call number.
When the call is closed, I delete it from the open service call table - and insert it into the closed calls table.

If the call was number 1000 - open service calls highest identity will be 999.

when the next new call is created - will SQL assign 1001 or 1000 ( access would set new rec to 1000 - if a compact was done )

thanks again|||I'm not 100% clear...but if I am following you...

If your service call was 1000, and then was closed, and you then deleted that service call record from your table, the next identity assigned would be 1001. SQL Server will not reuse 1000.

With 2 exceptions
-- you explicitly insert a record with the identity field set to 1000
-- you reset the identity seed to 1000

Under normal circumstances without you doing anything funky like that, you are fine. SQL Server does not behave like Access.

Terri|||thanks again

Repeated Elements

Can someone please help? I need the tags sorted by record
(which contains many levels of elements), not by a group
of <LName>elements.
Using
http://localhost/Customer/views/Empl...xsd/Employee/L
Name='King' I get the following:
<LName>Buchanan</LName><LName>Callahan</LName><LName>Davoli
o</LName><LName>Dodsworth</LName><LName>Fuller</LName><LNam
e>King</LName><LName>Leverling</LName><LName>Peacock</LName
><LName>Suyama</LName>
--^
I am not sure what the problem is. Do you want a single wrapper element
around? Do you want a different result? How would that look like?
Thanks
Michael
"TO" <anonymous@.discussions.microsoft.com> wrote in message
news:2e48a01c46a9d$fe009fc0$a301280a@.phx.gbl...
> Can someone please help? I need the tags sorted by record
> (which contains many levels of elements), not by a group
> of <LName>elements.
> Using
> http://localhost/Customer/views/Empl...xsd/Employee/L
> Name='King' I get the following:
> <LName>Buchanan</LName><LName>Callahan</LName><LName>Davoli
> o</LName><LName>Dodsworth</LName><LName>Fuller</LName><LNam
> e>King</LName><LName>Leverling</LName><LName>Peacock</LName
> --^
>

Repeat details section if

Is there a way to make the details section repeat depending on the quantity that is associated with a record. For example we have a product and if the customer orders more then one we want the sticker to print out twice. I am running crystal reports 8.
Thanks
DaveYou can set the number of copies to print , depending on the quantity|||How do you generate report?
If you generate it on customerwise, thru coding you need to check the number of records it prints and accordingly set print copies