Putting the same text into every cell in a query column

[adrian.stock22]

Putting the same text into every cell in a query column

Hi All,

I sometimes have to mark every record in a query with the same text in a field, when the field is either empty or contains whatever text.

At present I do this by copying the text, and then pasting it (ctrl-v cursor-down, a thousand times) into every cell in a column.

How can I do this in a more efficient way?

Thanks for your help.

Adrian

 

[Meltdown]

add a new column in the query grid like this:

ColumnName:"My Text"

 

[adrian.stock22]

Hi Meltdown,

Thanks, but ...
either I don't understand what you mean, or your solutition does not do what I want.

I need the "mytext" in every cell of the given column so that mytext will come up whenever I call up one of these records in whatever context.

I cannot see how creating an additional column can do that, but perhaps I am stupid - sorry.

I must not alter the structure of the table, e.g. add additional columns, data are imported into the table every month from another source with the same structure.

I need some command like "write 'mytext' into every field x (column x) of every record in this query".

Any more ideas?

Thanks.

Adrian

 

[Pauldohert]

Make a query with calculated fields to display the correct data - ie use the Nz function to convert the nulls to what you want, try IIf to convert text (depends how many you have, or a function that will convert you text, or a table with the covertions inseparate fields may work also). The field names may have to be differant to the source table, if so, you can probably then query the query renaming the fields back to what they were orininally.

Post a bit of sample data - and what you want to produce and I am sure someone will give you an example.

Paul

 

[Pat Hartman]

Create an update query. Open the QBE. Select the table you want to update. Select the column you want to update. Select the column for the criteria if different from the column you want to update. Change the query type to update. That adds a new row to the grid. Add the replacement value here. In the criteria, place whatever criteria you want to use to control which rows get updated.

 

[adrian.stock22]

Hi All,

In response to Paul Dohert, I have posted a sample of the query table at the following address:

http://www.tudo.co.uk/testing/access_query_1.gif

Typically the column called "Luigi date" will be empty or partly-empty or contain a varity of keywords, Sep or Jan and the like.

Typically I will fill all cells of the column, empty or not, with some text, such as hps05-07. At present I do it by pasting the text into each cell, 500 or 1000 times.

What I want is a way of 'declaring' that each cell in the "Luigi date" column, or whatever column, is to be filled with 'mytext' or whatever text.

I now begin to think that my knowledge of Access is very elementary, because I do not really understand what Pat Hartman suggests.

However, to save you giving explanations which are too elemntary and tedious, I have the book "Running Access 2000" by John Viescas. I do not have the time to work through this book from cover to cover. Perhaps someone could refer me to the chapter which answers my question in simpler terms.

Thanks.

Adrian

 

[mresann]

Create a new query. Go to SQL view. Replace the text there with this statement:

UPDATE TableName SET [TableName].[luigi Date] = "hps05-07";

Replace the table name "TableName" with the name of your table.

WARNING: EVERY current record will be changed. if you need to restrict the records that get changed, look up WHERE clause on Access Help.

 

[DanG]

So the message here is...
As Pat said look in your book under update query and as mresann said and I can not stress this more..if you are going to do an update query copy your table to practice on because once the query is run you can't "un-update" it.
Hint: in your update query you can use the "Is Null" to find empty cells and then update them with your intended replacement value using the "update to" row in the lower grid area

DanG

 

[adrian.stock22]

Hi,

Thank you all for your help and warnings.

Update Query worked beautifully.

Adrian