Add linebreaks in memo fields via vba

[Armitage2k]

Hello!

I have data in a memo field which is originally imported from my companies management system. However, during the export from this system, all line breaks of this text based data are delete and replaced with *-symbols.

Now I am looking for a way to replace all * symbols in the table (Column: Notes) with line breaks. Any idea how I can do that?

thanks,
A2k

 

[georgedwilkinson]

Aircode off the top of my head (that means you test it on a TEST database):

update mytable set columnname = replace(columnname,"*",vbCrLf)

Also, not sure in Access, but the name "Notes" is a reserved word in many SQL variants. So you may have to set the column name(s) in brackets. You really should never use reserved words or words with a potential to be reserved for database object names.

 

[boblarson]

Aircode off the top of my head (that means you test it on a TEST database):

update mytable set columnname = replace(columnname,"*",vbCrLf)

That won't work because you can't use vbCrLf in a query (it is VBA). You can, however, use

Chr(13) & Chr(10)

So

update mytable set columnname = replace(columnname,"*", Chr(13) & Chr(10))

 

[georgedwilkinson]

That is correct...didn't even think of that.

 

[Armitage2k]

hmm, dont get along with the syntax. shows me an "expecting" end error... :?

Maybe I should mention that I want to do the replacement in a table which I have earlier defined via variable "Category".

    'replace * with: Linebreak + Space + * in column Notes
    update db.recordset(category) [COLOR="Red"]set[/COLOR] [Notes] = replace([Notes],"*", Chr(10) & Chr(42) & Chr(32))

thanks,
A2k

 

[boblarson]

hmm, dont get along with the syntax. shows me an "expecting" end error... :?

Maybe I should mention that I want to do the replacement in a table which I have earlier defined via variable "Category".

    'replace * with: Linebreak + Space + * in column Notes
    update db.recordset(category) [COLOR="Red"]set[/COLOR] [Notes] = replace([Notes],"*", Chr(10) & Chr(42) & Chr(32))

thanks,
A2k

The code you posted isn't a valid Update query. What is this

db.recordset(category)

thing that you've put in?

If you are going to run an update query the update query should be structured:

UPDATE YourTABLENameHere SET [Category] = Replace([Category],"*",Chr(13) & Chr(10));

 

[Armitage2k]

The code you posted isn't a valid Update query. What is this

db.recordset(category)

thing that you've put in?

If you are going to run an update query the update query should be structured:

UPDATE YourTABLENameHere SET [Category] = Replace([Category],"*",Chr(13) & Chr(10));

db is my current database (sure you know...), recordset(category) is my table which is defined in the string "Category" and the column name is always "Notes". The problem is that I dont know the table which is supposed to be replaced until it is defined by the user in my "category" string.

Is there any special syntax I need to follow when writing a string as table name into the UPDATE query?

btw, the query should look more like this, shouldnt it?

UPDATE $category-string$ SET[Category] = Replace([Notes],"*", Chr()13 & Chr(10));

thanks for the help,
A2k

 

[georgedwilkinson]

There are many things wrong with that.

An update statement is a SQL statement that doesn't take parameters for the table name. So no, you cannot do that. The simplest approach is to write a separate update query for each table if you have more than one. If you have so many that this doesn't make sense, you need a little better primer on using VBA and SQL together, I would imagine.

You have to have a space before and after key words (like "SET").

The VBA function Chr() requires a parameter and you cannot have a number (in this case, "13") just randomly sitting around in the middle of a Replace function call.

And the biggie: I had assumed that your column named "notes" was a Memo field. If that is the case, you "probably" need to put it back into the same field. It is unlikely that you have a field named "Category" and and field named "Notes" in the same table that are both Memo type fields.

So, your SQL would look like:

UPDATE mytable SET [Notes] = Replace([Notes], "*", Chr(13) & Chr(10));
[code]

 

[Armitage2k]

well, I am getting a bit closer know.

I have tried a different approach, since you mentioned that variables for tables are not exactly working with this query, hence i have splitted the query into two (that apparently worked for someone who had a similar problem, but he complained he could not connect to many "&"s together...)

StarUpdate_query = "UPDATE" & Category
StarUpdate_query = StarUpdate_query & "SET [Notes] = Replace([Notes], " * ", Chr(13) & Chr(10))"

DoCmd.RunSQL StarUpdate_query

Now the macro goes one step further, but now starts to complain that the types are not compatible.
To my syntax. the "Category" is a variable which holds the name of my table. One table only, thats it. And yes, the "Notes" fields are all memo fields and of course shall be replaced and inserted into the same field.

So from my understanding this whole thing does not work because I cannot insert a variable into a SQL statement... Is there any way out of this?

Thanks for the guidance,
A2k

 

[georgedwilkinson]

One question: why?

Just write an update query and be done with it. There is absolutely NO need for a variable.

 

[Armitage2k]

there is a need for a variable if I dont know which table needs to be updated.

 

[georgedwilkinson]

Then perhaps you should show us the rest of your subroutine. And put a space before and after whatever the value of Category is.

 

[boblarson]

there is a need for a variable if I dont know which table needs to be updated.

So you have memo fields in many tables? Just a suggestion - you may want to watch out for that as that can be a prime candidate for corruption. I'm not saying your design has problems or anything, but just be careful in your use of memo fields. They can be big trouble.