Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Replace a blank field with text

Status
Not open for further replies.

pnabby

IS-IT--Management
Aug 29, 2004
46
US
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?
 
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:

Code:
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 [infinity]
"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
 
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
 
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
 
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
 
I tried both of those suggestions, and the Market field is empty. Any other suggestions?
 
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
 
That didn't work either. Neither did that function. Any other ideas?
 
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!!!!!!!!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top