Unwanted speech marks in fields

[AJCB]

Could anyone help me?

I have a table in an Access Database which sometimes shows speech marks in
front and at the end of the fields and sometimes it doesn't.

I need to remove these if the field has them?

What would be the best course of action for this and could you give me an
example of the query string?

Cheers
AJ

 

[UpRider]

If by 'speech marks' you mean double quotes, this will work:

UPDATE tblDateTest SET tblDateTest.Aname = Replace([Aname],"""","");

UpRider

 

[AJCB]

Sorry to sound stupid, but!!!

Where abouts will I put the field reference?

Do I replace Aname for the reference?

If by 'speech marks' you mean double quotes, this will work:

UPDATE tblDateTest SET tblDateTest.Aname = Replace([Aname],"""","");

UpRider

Could anyone help me?

I have a table in an Access Database which sometimes shows speech marks in
front and at the end of the fields and sometimes it doesn't.

I need to remove these if the field has them?

What would be the best course of action for this and could you give me an
example of the query string?

Cheers
AJ

 

[UpRider]

change tblDataTest to your table name.
change Aname to your column name

UpRider

Sorry to sound stupid, but!!!

Where abouts will I put the field reference?

Do I replace Aname for the reference?

If by 'speech marks' you mean double quotes, this will work:

UPDATE tblDateTest SET tblDateTest.Aname = Replace([Aname],"""","");

UpRider

Could anyone help me?

I have a table in an Access Database which sometimes shows speech marks
in
front and at the end of the fields and sometimes it doesn't.

I need to remove these if the field has them?

What would be the best course of action for this and could you give me
an
example of the query string?

Cheers
AJ

 

[AJCB]

I have tried this, but all I keep getting back is 'The Expression you have
entered contains an invalid syntax'

Is there another way around this?

I am sorry, if you are talking about sql query, I am afraid I have never
done one of these!

change tblDataTest to your table name.
change Aname to your column name

UpRider

Sorry to sound stupid, but!!!

Where abouts will I put the field reference?

Do I replace Aname for the reference?

If by 'speech marks' you mean double quotes, this will work:

UPDATE tblDateTest SET tblDateTest.Aname = Replace([Aname],"""","");

UpRider

Could anyone help me?

I have a table in an Access Database which sometimes shows speech marks
in
front and at the end of the fields and sometimes it doesn't.

I need to remove these if the field has them?

What would be the best course of action for this and could you give me
an
example of the query string?

Cheers
AJ

 

[AJCB]

The strings I have tried are:

UPDATE tblScanned Barcode Data SET tblScanned Barcode Data.User ID =
Replace([User ID],""","")

and

UPDATE Scanned Barcode Data SET Scanned Barcode Data.User ID = Replace([User
ID],""","")

I have tried this, but all I keep getting back is 'The Expression you have
entered contains an invalid syntax'

Is there another way around this?

I am sorry, if you are talking about sql query, I am afraid I have never
done one of these!

change tblDataTest to your table name.
change Aname to your column name

UpRider

Sorry to sound stupid, but!!!

Where abouts will I put the field reference?

Do I replace Aname for the reference?

:

If by 'speech marks' you mean double quotes, this will work:

UPDATE tblDateTest SET tblDateTest.Aname = Replace([Aname],"""","");

UpRider

Could anyone help me?

I have a table in an Access Database which sometimes shows speech marks
in
front and at the end of the fields and sometimes it doesn't.

I need to remove these if the field has them?

What would be the best course of action for this and could you give me
an
example of the query string?

Cheers
AJ

 

[UpRider]

The examples that you give appear to be trying to replace the quotes in a
*specific* row with a given UserID.

The example I gave would simply replace the quote marks in *every row of the
table* for the column ANAME.
That would fix all the existing rows in the table.

Which are you attempting to do?

UpRider

The strings I have tried are:

UPDATE tblScanned Barcode Data SET tblScanned Barcode Data.User ID =
Replace([User ID],""","")

and

UPDATE Scanned Barcode Data SET Scanned Barcode Data.User ID =
Replace([User
ID],""","")

I have tried this, but all I keep getting back is 'The Expression you
have
entered contains an invalid syntax'

Is there another way around this?

I am sorry, if you are talking about sql query, I am afraid I have never
done one of these!

change tblDataTest to your table name.
change Aname to your column name

UpRider

Sorry to sound stupid, but!!!

Where abouts will I put the field reference?

Do I replace Aname for the reference?

:

If by 'speech marks' you mean double quotes, this will work:

UPDATE tblDateTest SET tblDateTest.Aname = Replace([Aname],"""","");

UpRider

Could anyone help me?

I have a table in an Access Database which sometimes shows speech
marks
in
front and at the end of the fields and sometimes it doesn't.

I need to remove these if the field has them?

What would be the best course of action for this and could you
give me
an
example of the query string?

Cheers
AJ

 

[Douglas J. Steele]

Take a closer look at what Uprider suggested. There are supposed to be four
consecutive double quotes as the second parameter of the Replace statement:
you've only got three. (That's the way VBA works)

As well, since you've got embedded blanks in your table and field names, you
need square brackets.

UPDATE [tblScanned Barcode Data] SET [tblScanned Barcode Data].[User ID] =
Replace([User ID],"""","")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The strings I have tried are:

UPDATE tblScanned Barcode Data SET tblScanned Barcode Data.User ID =
Replace([User ID],""","")

and

UPDATE Scanned Barcode Data SET Scanned Barcode Data.User ID =
Replace([User
ID],""","")

I have tried this, but all I keep getting back is 'The Expression you
have
entered contains an invalid syntax'

Is there another way around this?

I am sorry, if you are talking about sql query, I am afraid I have never
done one of these!

change tblDataTest to your table name.
change Aname to your column name

UpRider

Sorry to sound stupid, but!!!

Where abouts will I put the field reference?

Do I replace Aname for the reference?

:

If by 'speech marks' you mean double quotes, this will work:

UPDATE tblDateTest SET tblDateTest.Aname = Replace([Aname],"""","");

UpRider

Could anyone help me?

I have a table in an Access Database which sometimes shows speech
marks
in
front and at the end of the fields and sometimes it doesn't.

I need to remove these if the field has them?

What would be the best course of action for this and could you
give me
an
example of the query string?

Cheers
AJ

 

[AJCB]

What I am trying to do is:
I have a field called User ID in the table Scanned Barcode Data, which shows:

"12288$14"
"11288$15"
"11345$133"

The figure I would like to see in the table is:

12288$14
12288$15
11345$133

Although I have been using access forabout 4 years and know a lot about
normal queries, I know nothing about SQL queries, VBA projects or Modules.

Can I just put this string into the Expression Builder? Or is code the only
way to get around this?

Sorry if I am a little confusing. I am still learning!

Take a closer look at what Uprider suggested. There are supposed to be four
consecutive double quotes as the second parameter of the Replace statement:
you've only got three. (That's the way VBA works)

As well, since you've got embedded blanks in your table and field names, you
need square brackets.

UPDATE [tblScanned Barcode Data] SET [tblScanned Barcode Data].[User ID] =
Replace([User ID],"""","")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The strings I have tried are:

UPDATE tblScanned Barcode Data SET tblScanned Barcode Data.User ID =
Replace([User ID],""","")

and

UPDATE Scanned Barcode Data SET Scanned Barcode Data.User ID =
Replace([User
ID],""","")

I have tried this, but all I keep getting back is 'The Expression you
have
entered contains an invalid syntax'

Is there another way around this?

I am sorry, if you are talking about sql query, I am afraid I have never
done one of these!

:

change tblDataTest to your table name.
change Aname to your column name

UpRider

Sorry to sound stupid, but!!!

Where abouts will I put the field reference?

Do I replace Aname for the reference?

:

If by 'speech marks' you mean double quotes, this will work:

UPDATE tblDateTest SET tblDateTest.Aname = Replace([Aname],"""","");

UpRider

Could anyone help me?

I have a table in an Access Database which sometimes shows speech
marks
in
front and at the end of the fields and sometimes it doesn't.

I need to remove these if the field has them?

What would be the best course of action for this and could you
give me
an
example of the query string?

Cheers
AJ

 

[UpRider]

You can do this with a 'normal' query, without looking at any SQL.
Make a copy of your table to test with.
Go to new query and add your test table.
Drag down User ID to the grid.
In 'query type' select 'update query'
In 'Update to' grid type
Replace([User ID],"""","")
Run the query.
Look at the test table.
If all is well, do the same query for your 'live' table.

UpRider

What I am trying to do is:
I have a field called User ID in the table Scanned Barcode Data, which
shows:

"12288$14"
"11288$15"
"11345$133"

The figure I would like to see in the table is:

12288$14
12288$15
11345$133

Although I have been using access forabout 4 years and know a lot about
normal queries, I know nothing about SQL queries, VBA projects or Modules.

Can I just put this string into the Expression Builder? Or is code the
only
way to get around this?

Sorry if I am a little confusing. I am still learning!

Take a closer look at what Uprider suggested. There are supposed to be
four
consecutive double quotes as the second parameter of the Replace
statement:
you've only got three. (That's the way VBA works)

As well, since you've got embedded blanks in your table and field names,
you
need square brackets.

UPDATE [tblScanned Barcode Data] SET [tblScanned Barcode Data].[User ID]
=
Replace([User ID],"""","")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The strings I have tried are:

UPDATE tblScanned Barcode Data SET tblScanned Barcode Data.User ID =
Replace([User ID],""","")

and

UPDATE Scanned Barcode Data SET Scanned Barcode Data.User ID =
Replace([User
ID],""","")



:

I have tried this, but all I keep getting back is 'The Expression you
have
entered contains an invalid syntax'

Is there another way around this?

I am sorry, if you are talking about sql query, I am afraid I have
never
done one of these!

:

change tblDataTest to your table name.
change Aname to your column name

UpRider

Sorry to sound stupid, but!!!

Where abouts will I put the field reference?

Do I replace Aname for the reference?

:

If by 'speech marks' you mean double quotes, this will work:

UPDATE tblDateTest SET tblDateTest.Aname =
Replace([Aname],"""","");

UpRider

Could anyone help me?

I have a table in an Access Database which sometimes shows
speech
marks
in
front and at the end of the fields and sometimes it doesn't.

I need to remove these if the field has them?

What would be the best course of action for this and could you
give me
an
example of the query string?

Cheers
AJ

 

[AJCB]

Thanks. That worked a treat!

I have already used this in 3 queries.

You can do this with a 'normal' query, without looking at any SQL.
Make a copy of your table to test with.
Go to new query and add your test table.
Drag down User ID to the grid.
In 'query type' select 'update query'
In 'Update to' grid type
Replace([User ID],"""","")
Run the query.
Look at the test table.
If all is well, do the same query for your 'live' table.

UpRider

What I am trying to do is:
I have a field called User ID in the table Scanned Barcode Data, which
shows:

"12288$14"
"11288$15"
"11345$133"

The figure I would like to see in the table is:

12288$14
12288$15
11345$133

Although I have been using access forabout 4 years and know a lot about
normal queries, I know nothing about SQL queries, VBA projects or Modules.

Can I just put this string into the Expression Builder? Or is code the
only
way to get around this?

Sorry if I am a little confusing. I am still learning!

Take a closer look at what Uprider suggested. There are supposed to be
four
consecutive double quotes as the second parameter of the Replace
statement:
you've only got three. (That's the way VBA works)

As well, since you've got embedded blanks in your table and field names,
you
need square brackets.

UPDATE [tblScanned Barcode Data] SET [tblScanned Barcode Data].[User ID]
=
Replace([User ID],"""","")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The strings I have tried are:

UPDATE tblScanned Barcode Data SET tblScanned Barcode Data.User ID =
Replace([User ID],""","")

and

UPDATE Scanned Barcode Data SET Scanned Barcode Data.User ID =
Replace([User
ID],""","")



:

I have tried this, but all I keep getting back is 'The Expression you
have
entered contains an invalid syntax'

Is there another way around this?

I am sorry, if you are talking about sql query, I am afraid I have
never
done one of these!

:

change tblDataTest to your table name.
change Aname to your column name

UpRider

Sorry to sound stupid, but!!!

Where abouts will I put the field reference?

Do I replace Aname for the reference?

:

If by 'speech marks' you mean double quotes, this will work:

UPDATE tblDateTest SET tblDateTest.Aname =
Replace([Aname],"""","");

UpRider

Could anyone help me?

I have a table in an Access Database which sometimes shows
speech
marks
in
front and at the end of the fields and sometimes it doesn't.

I need to remove these if the field has them?

What would be the best course of action for this and could you
give me
an
example of the query string?

Cheers
AJ

 

[AJCB]

Hi.

I know this was a while ago, but I have a similar problem and I cannot get
it to work.

I have a field called Order number which is 08/0128/1/28 and I want to
remove the "/". I have tried to way that you showed me before with the
following string:

replace([Order Number],"///","")

But this did not work. It returned the Order Number field exactly the same
as the original field, ie. 08/0128/1/28.

Cheers
AJ

You can do this with a 'normal' query, without looking at any SQL.
Make a copy of your table to test with.
Go to new query and add your test table.
Drag down User ID to the grid.
In 'query type' select 'update query'
In 'Update to' grid type
Replace([User ID],"""","")
Run the query.
Look at the test table.
If all is well, do the same query for your 'live' table.

UpRider

What I am trying to do is:
I have a field called User ID in the table Scanned Barcode Data, which
shows:

"12288$14"
"11288$15"
"11345$133"

The figure I would like to see in the table is:

12288$14
12288$15
11345$133

Although I have been using access forabout 4 years and know a lot about
normal queries, I know nothing about SQL queries, VBA projects or Modules.

Can I just put this string into the Expression Builder? Or is code the
only
way to get around this?

Sorry if I am a little confusing. I am still learning!

Take a closer look at what Uprider suggested. There are supposed to be
four
consecutive double quotes as the second parameter of the Replace
statement:
you've only got three. (That's the way VBA works)

As well, since you've got embedded blanks in your table and field names,
you
need square brackets.

UPDATE [tblScanned Barcode Data] SET [tblScanned Barcode Data].[User ID]
=
Replace([User ID],"""","")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The strings I have tried are:

UPDATE tblScanned Barcode Data SET tblScanned Barcode Data.User ID =
Replace([User ID],""","")

and

UPDATE Scanned Barcode Data SET Scanned Barcode Data.User ID =
Replace([User
ID],""","")



:

I have tried this, but all I keep getting back is 'The Expression you
have
entered contains an invalid syntax'

Is there another way around this?

I am sorry, if you are talking about sql query, I am afraid I have
never
done one of these!

:

change tblDataTest to your table name.
change Aname to your column name

UpRider

Sorry to sound stupid, but!!!

Where abouts will I put the field reference?

Do I replace Aname for the reference?

:

If by 'speech marks' you mean double quotes, this will work:

UPDATE tblDateTest SET tblDateTest.Aname =
Replace([Aname],"""","");

UpRider

Could anyone help me?

I have a table in an Access Database which sometimes shows
speech
marks
in
front and at the end of the fields and sometimes it doesn't.

I need to remove these if the field has them?

What would be the best course of action for this and could you
give me
an
example of the query string?

Cheers
AJ

 

[Stefan Hoffmann]

hi,

Hi.
I have a field called Order number which is 08/0128/1/28 and I want to
remove the "/". I have tried to way that you showed me before with the
following string:

replace([Order Number],"///","")

You only need to double the quotation marks, so use "/" instead.


mfG
--> stefan <--

 

[AJCB]

Thanks Stefan. This sorted it.

Regards
AJ

hi,

Hi.
I have a field called Order number which is 08/0128/1/28 and I want to
remove the "/". I have tried to way that you showed me before with the
following string:

replace([Order Number],"///","")

You only need to double the quotation marks, so use "/" instead.


mfG
--> stefan <--