Replace a blank field with text

[pnabby]

I added another column to an existing table and I already had several thousand records. These are stock tickers and I just want to add the word NASDAQ in this field rather than it being blank.

I can't remember how to do this in the Search/Replace function.

Any suggestions?

 

[tbclbd]

You can create an update query, which will just add the word NASDAQ to the new field.

 

[kjv1611]

Well, if they are all blank, you could use SQL or you could use a DAO.Recordset. If you have, say, less than 100,000 records, then the recordset should work fine. Probably would work fine with plenty more than that as well, just haven't tested that small a change with that many records in DAO myself. You could do something like this in a module:

Private Function AddNadaq()
  Dim db as DAO.Database
  Dim rs as DAO.Recordset
  Set db = CurrentDb
  Set rs = db.OpenRecordSet("tblStockTicker")
  With rs
    Do While Not .EOF
      rs.Fields("NewColumnName") = "NASDAQ"
      rs.MoveNext
    Loop
  End With
  rs.Close
  Set rs = Nothing
  db.Close
  Set db = Nothing
End Sub

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[pnabby]

There are about 3000 records. I want to do this the easiest way, and I am not familiar with writing functions. The table name is Nasdaq_SD and the column name is Market and I just want to add the word NASDAQ.

If I have to use a function, do I do that under Modules?

Sorry I am not an expert by any stretch of the imagination.

Thanks,
Julie

 

[RoyVidar]

Update query? Paste into the SQL view of the query builder on a copy of the db.

[tt]update Nasdaq_SD set Market ="NASDAQ"[/tt]
- set all to Nasdaq

[tt]update Nasdaq_SD set Market = Market & " NASDAQ"[/tt]
- add Nasdaq to whatever is in the column

Roy-Vidar

 

[PHV]

Something like this (in SQL view of a query) ?
UPDATE Nasdaq_SD SET Market = "NASDAQ"
WHERE Market Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pnabby]

I tried both of those suggestions, and the Market field is empty. Any other suggestions?

 

[PHV]

and this ?
UPDATE Nasdaq_SD SET Market = "NASDAQ"
WHERE Trim(Market & "")="";

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pnabby]

That didn't work either. Neither did that function. Any other ideas?

 

[pnabby]

Oops...it works. I saved the query and closed it. Then I ran it again and it updated the table. This one worked:

UPDATE NASDAQ SET NASDAQ.Market = "NASDAQ"
WHERE (((NASDAQ.Market) Is Null));

I was just going to the results in the query, and I didn't see anything so I thought it didn't work. My bad.

Thank you so much PHV!!!!!!!!!