mass update in the comment

[ThaiByThai]

Hi all -
I just updated a field in our master database with people DOB that matches their emp ID - I need to do a mass update in the comment field that say "Updated on 4-4-2011" but does not delete the the current comment that is already there in the comment field - i'm not sure how to go about doing this... i am using access 2007 and i try the append but that fail...


SELECT [MASTER DATABASE].ID, [MASTER DATABASE].[Birth Date], [MASTER DATABASE].Comment
FROM [MASTER DATABASE] RIGHT JOIN [DOB Query] ON [MASTER DATABASE].ID = [DOB Query].ID;

 

[DCrake]

If this is a one off exercise then do the following

Create a new query and add the desired table
Bring down the comments field
Create a second column

Alias:[Comments] & " vbNewLine & "Updated on 4-4-2011"

Switch to datasheet view to view both the existing and revised comments field.

Notice the new text is added to the end of any existing text.

Next Highlight the revised column and press Ctrl+C (Copy)
Then highlight the comment column and press Ctrl+V (Paste)

This will overwrite the existing comments with the revised text.

 

[Brianwarnock]

you just need an update query

in the update to field write what David put in the alias, the newline could be replaced by " " or the spaces incorporated into the " Updated etc

David's approach is more work but allows for a visual check if that makes you happier.


Brian

 

[ThaiByThai]

Thanks Brian and David for the respond!

I test it out and it works great! - i have a question thou - will this work if i need to update 200 thousands records?

i would like to double check and make sure that the comments was added at the end of the comments textbox and that there is a break before updating it... but it with 200 thousands records -- not sure if this will copy and paste without crashing...

 

[DCrake]

You can set the filter condition to where DOB is not null.
Or you could do it in bits filter in on a range of primary keys.

 

[ThaiByThai]

Hi David -

how do i make sure that the comments (text) is started on a new line? i want to make a break at the end of the comments and then added my comments..

right now i have it

Expr1: ([comments] & "4/8/2011: DOB updated.") & Chr$(13) &
Chr$(10)

this is adding the comments at the end of the text but i would like it to start on a new line at the end of the comments...

i.e.

2-2-200: John Doe started on 4/1/2000
2-5-2001: John Doe request leave
4/8/2011: DOB udpated

Because right now it's adding 4/8/2011 right next to leave...

 

[Brianwarnock]

David showed you how to do that in post 2

Brian

 

[ThaiByThai]

Hi Brian -

when i enter

Expr1[comments] & " vbNewLine & "4/8/2011: DOB updated" )

I'm getting an error "The expression you entered contains invalid syntax"

but when i enter:

Expr1: ([comments] & "4/8/2011: DOB updated.") & Chr$(13) &
Chr$(10)

the comments does get added but not a the start of a new line... but right after the pervious comments.

 

[DCrake]

Syntax error

Expr1:[comments] & vbNewLine & "4/8/2011 : DOB updated"

 

[Brianwarnock]

Syntax error

Expr1:[comments] & vbNewLine & "4/8/2011 : DOB updated"

LOL I had to go back and check , yep a stray " , syntax errors are so easy to do and miss, but normally one spots other peoples' , especially if trying to run the code.

Brian