Add text at the beginning of a field

[Steviet811]

Hi

I wish to add text at the beginning of a string in a field. I will use the append query function but, do not know the syntax.

Can anyone help please?

 

[MStef]

Do you wish "Append query" or "Update query" ??
Do you wish to add a new record, or update an old record ???

 

[plog]

You used the term 'append', which means an INSERT query (http://www.1keydata.com/sql/sqlinsert.html) but you have described an UPDATE query (http://www.1keydata.com/sql/sqlupdate.html). Also,

I wish to add text at the beginning of a string in a field

What text are you adding? Is it the same for every field? Can you give some sample before data and some sample after data?

 

[Steviet811]

Thanks very much for looking

My problem is that I'm trying to run an update query to add a text string to the start of the field to resolve an issue that I'm having with date characters that are at the beginning of some of the fields.

I'm exporting the data as a csv for use in the crm salesforce.com but have hard returns in one of the fields that need to be removed first. To do this I was running an update query using

Replace([FieldNameHere],Chr(13) & Chr(10),". ")

but I get key violations that I've identified as being caused by dates being entered 25/01/12 in the notes field for example. One solution I'd intended on trying was to add a text string at the beginning of every notes field in every record approx 1100.

An example of the data from the field in question is attached for reference.

I'm unsure whether an update or an append query is correct.

Any help gratefully received

 

[jdraw]

I think you are going to have to tell us exactly what you are trying to do. I have a feeling that you txt file is some sort of running comments on specific issues.

I have had applications where there was a Notes table with a specific format
DateOfComment - date the comment was made.
MessageText - variable length text message relating to issue or status

This looks similar to your txt example.

 

[Steviet811]

Hi jdraw

The hard returns are causing on import to the crm salesforce.com to create new fields which is then misaligning the data and it's ending up in the incorrect fields. I want to batch edit the notes field (as sample file) before export removing the spaces and the hard returns.

My plan was to add a string like "added from TE dbase" for example

 

[jdraw]

My suggestion -
Import the data as is into a temporary table in Access.
Go through each record and remove the hard returns (using a query).
You may need some queries to help with what records go where.
Then update your "real table" with the temporary txt records.

 

[Steviet811]

I have a plan and wish to add a fixed length text string to the beginning of the field.

Any suggestions would be gratefully received

 

[jdraw]

Good luck with your plan

 

[Pat Hartman]

I'm not sure how adding a prefix will solve the problem but it is easy enough to do.
In the QBE, select the table you want to update. Select the column you want to update. Change the query type to Update. In the Update To cell add the string plus the column name:

"added from TE dbase" & [yourcolumnname]

Enclose the column name in square brackets to avoid any confusion.

And needless to say - BACKUP FIRST!!!! before running any update queries.