Remove "" from a field name

[noboffinme]

Hi

I need to remove quotation marks from the field name column in an Access table.

The fieldname can change & I currently have 3 sets of quotes at each end of the fieldname.

I've tried a few versions of Update statements but no luck, can anyone help??

 

[John Big Booty]

Are you attempting to remover the double quotes form the data within the table or from the actual name of the field

 

[noboffinme]

Hi

The actual field names, not the field heading.

The names vary but they all currently have 3 quotes at the start & also at the finish of the value.

 

[John Big Booty]

Sorry, I must be having a bit of a blonde moment

Can you post a screen shot?

 

[noboffinme]

I can show you an example right here;

What I have now;

Fieldname
"""value"""
"""value2"""
"""value3"""

What I want is;

Fieldname
value
value
value

 

[John Big Booty]

OK so it is the data that contains the miscreant double quotes?

 

[John Big Booty]

If my above assumption is correct use the following as the Update To argument in an update query;

 Mid([FieldName],4,Len([FieldName])-6)

 

[noboffinme]

Yes, can you help?

I want to do an Update query in the query builder to remove the quotes.

 

[John Big Booty]

Quick tip try the above in a back up copy of your DB to ensure you get the correct start and end points in the Mid() function.

 

[noboffinme]

I have No idea what you're talking about........

 

[John Big Booty]

OK have a look at the attached. First look at the data in the Table is should replicate your data to some extent. Now have a look at Query2 in design view, now run that query. Now do the same with Query3. Now close the table if it is still open, and reopen it and have a look at the data.

 

[noboffinme]

Thanks John Big Booty

Would never have thought of MID & LEN for this purpose.

So now I get it that

Mid([ItemName],4,Len([ItemName])-6)

is useful as - The MID function starts at '4' as this is the first letter I want kept after my 3 " marks & the number of letters it returns after that is defined by LEN which is the whole fieldname length minus 6 (which is 2 x 3 quotation marks). Brilliant !!

Thanks for your help

 

[John Big Booty]

Glad you got it sorted

 

[dfenton]

Is there some reason why Replace(FieldName,Chr(34) & Chr(34) & Chr(34), "") is not satisfactory?

 

[SOS]

Is there some reason why Replace(FieldName,Chr(34) & Chr(34) & Chr(34), "") is not satisfactory?

And if you didn't want any quotes (not just the triple threat) a simple

Replace(FieldName, Chr(34),"")

should take care of it, no?

 

[Galaxiom]

I expect John's Mid solution is more efficient. The Replace solution requires every character to be individually assessed. Mid just grabs the range of characters.

 

[SOS]

I expect John's Mid solution is more efficient. The Replace solution requires every character to be individually assessed. Mid just grabs the range of characters.

More efficient IF, and ONLY if, the quotes are exactly the same. If you have one, two, three, four to get rid of then it is not more efficient. Right?

 

[Galaxiom]

Yes. John's solution cannot be used with variable numbers of quote characters.
But the poster indicated they had exactly three quotes at each end.

 

[noboffinme]

Thanks dfenton SOS & Galaxiom for your input,

Re the response - Replace(FieldName,Chr(34) & Chr(34) & Chr(34), "")

What is Chr(34) & how does it work??

dfenton,

No reason why this wouldn't be satisfactory, I just don't follow how it works & it hadn't been posted as a possible solution before John Big Bootys response.

For future possibilities, I would be interested in this & other solutions for when the problem isn't as 'tidy' as 'exactly 3 of the same chars at each end of a string' to be removed. Johns solution was suitable for that purpose & got me past a deadline. : )

Thanks to all : )

 

[Galaxiom]

Chr is a function that returns the character from a decimal ASCII code argument.
34 is the double quote mark.

The ampersand is concatenation so Chr(34) & Chr(34) & Chr(34) is the same as literal """.

However since the double quote already is a string delimiter it it can't just be typed straight into the expression as """. The Chr(34) is the work around.

There is another way to escape a string of literal double quote but it isn't particularly elegant. The escape character is another double quote so:

Chr(34) & Chr(34) & Chr(34)
can be replaced by eight double quotes.

""""""""