Replace Function Access 2003

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I can't seem to get the replace function to work correctly in Access. I'm
trying to convert "Mr" to "Male" for a gender specific project. I know that
the string I'm using sees the text as "Mr" as I have used a selection query
to test it. But I'm unable to get a result using
Replace([WA]![Sex],"Mr","Male"). It simply will not work, also I'm unable to
use the same script to replace "&" with a comma ",".

Everything I have read about my first problem on the internet and in books
tells me it's correct.

Pleasse help.........
 
What version of Access are you using? If you are using 2000 has it been
patched to the latest version?

Does not work is not a good description of your problem. Do you get any
error messages?

Do you want to permanently change the value or are you just attempting to do
this in a normal Select query?
Are you attempting to do this in an update query (permanent change)?

Can you post the ACTUAL SQL you are using. Menu: View: SQL (copy and paste
the text)?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Hi John,

Thanks for taking the time to look at my problem. No I'm using Access 2003,
but the database file is 2000 file format. I have had Data type mismatch when
trying to append and no action is taken.

I would like to update/append to a new field.

As for SQL, I don't have an option under View for SQL. But under Tools:
Options Tables/Queries tab the SQL Server Compatible Syntax (ANSI 92) has 2
options checkbox: This database which is unchecked and a greyed out Default
for new databases.

Cheers

--
Paul Wilson


John Spencer said:
What version of Access are you using? If you are using 2000 has it been
patched to the latest version?

Does not work is not a good description of your problem. Do you get any
error messages?

Do you want to permanently change the value or are you just attempting to do
this in a normal Select query?
Are you attempting to do this in an update query (permanent change)?

Can you post the ACTUAL SQL you are using. Menu: View: SQL (copy and paste
the text)?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Paul Wilson said:
Hi,

I can't seem to get the replace function to work correctly in Access. I'm
trying to convert "Mr" to "Male" for a gender specific project. I know
that
the string I'm using sees the text as "Mr" as I have used a selection
query
to test it. But I'm unable to get a result using
Replace([WA]![Sex],"Mr","Male"). It simply will not work, also I'm unable
to
use the same script to replace "&" with a comma ",".

Everything I have read about my first problem on the internet and in books
tells me it's correct.

Pleasse help.........
Click to expand...
Click to expand...
 
One: Are you saying that when you have the query open in design view, that
the View menu does not show an SQL option? It should.

Two: if you are getting a type mismatch error then I suspect that the field
WA.Sex may be defined in your table as a LOOKUP field. That is on the
Lookup tab you have chosen Combobox and then supplied a source for the
values. If that is the case, then the real value that is stored in the
field may well be a number. When you look at the table in data sheet view
does the SEX field have a combobox to choose from? If so, then you have
used the Lookup field capability.

In SQL view I would expect to see something like the following for an update
query.
UPDATE WA
SET WA.SEX = Replace([WA].[Sex],"Mr","Male")
WHERE WA.Sex is not null

Of course, if Sex is just Mr then you can avoid the Replace function
completely
UPDATE WA
SET WA.SEX = "Male"
WHERE WA.Sex ="Mr"

Or if Sex could be entered as Mr or Mr. then change the where to
WHERE WA.Sex Like "Mr*"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Paul Wilson said:
Hi John,

Thanks for taking the time to look at my problem. No I'm using Access
2003,
but the database file is 2000 file format. I have had Data type mismatch
when
trying to append and no action is taken.

I would like to update/append to a new field.

As for SQL, I don't have an option under View for SQL. But under Tools:
Options Tables/Queries tab the SQL Server Compatible Syntax (ANSI 92) has
2
options checkbox: This database which is unchecked and a greyed out
Default
for new databases.

Cheers

--
Paul Wilson


John Spencer said:
What version of Access are you using? If you are using 2000 has it been
patched to the latest version?

Does not work is not a good description of your problem. Do you get any
error messages?

Do you want to permanently change the value or are you just attempting to
do
this in a normal Select query?
Are you attempting to do this in an update query (permanent change)?

Can you post the ACTUAL SQL you are using. Menu: View: SQL (copy and
paste
the text)?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Paul Wilson said:
Hi,

I can't seem to get the replace function to work correctly in Access.
I'm
trying to convert "Mr" to "Male" for a gender specific project. I know
that
the string I'm using sees the text as "Mr" as I have used a selection
query
to test it. But I'm unable to get a result using
Replace([WA]![Sex],"Mr","Male"). It simply will not work, also I'm
unable
to
use the same script to replace "&" with a comma ",".

Everything I have read about my first problem on the internet and in
books
tells me it's correct.

Pleasse help.........
Click to expand...
Click to expand...
Click to expand...
 
Firstly I didn't realise I had to have the design view open to access the SQL
view, thanks for the heads up. I had no problem with the first suggestion you
made using the replace function you supplied as a sample. The second method
you provided which was using Set method worked, but why is sex in uppercase
in the set argument and not in the where?

--
Paul Wilson


John Spencer said:
One: Are you saying that when you have the query open in design view, that
the View menu does not show an SQL option? It should.

Two: if you are getting a type mismatch error then I suspect that the field
WA.Sex may be defined in your table as a LOOKUP field. That is on the
Lookup tab you have chosen Combobox and then supplied a source for the
values. If that is the case, then the real value that is stored in the
field may well be a number. When you look at the table in data sheet view
does the SEX field have a combobox to choose from? If so, then you have
used the Lookup field capability.

In SQL view I would expect to see something like the following for an update
query.
UPDATE WA
SET WA.SEX = Replace([WA].[Sex],"Mr","Male")
WHERE WA.Sex is not null

Of course, if Sex is just Mr then you can avoid the Replace function
completely
UPDATE WA
SET WA.SEX = "Male"
WHERE WA.Sex ="Mr"

Or if Sex could be entered as Mr or Mr. then change the where to
WHERE WA.Sex Like "Mr*"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Paul Wilson said:
Hi John,

Thanks for taking the time to look at my problem. No I'm using Access
2003,
but the database file is 2000 file format. I have had Data type mismatch
when
trying to append and no action is taken.

I would like to update/append to a new field.

As for SQL, I don't have an option under View for SQL. But under Tools:
Options Tables/Queries tab the SQL Server Compatible Syntax (ANSI 92) has
2
options checkbox: This database which is unchecked and a greyed out
Default
for new databases.

Cheers

--
Paul Wilson


John Spencer said:
What version of Access are you using? If you are using 2000 has it been
patched to the latest version?

Does not work is not a good description of your problem. Do you get any
error messages?

Do you want to permanently change the value or are you just attempting to
do
this in a normal Select query?
Are you attempting to do this in an update query (permanent change)?

Can you post the ACTUAL SQL you are using. Menu: View: SQL (copy and
paste
the text)?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi,

I can't seem to get the replace function to work correctly in Access.
I'm
trying to convert "Mr" to "Male" for a gender specific project. I know
that
the string I'm using sees the text as "Mr" as I have used a selection
query
to test it. But I'm unable to get a result using
Replace([WA]![Sex],"Mr","Male"). It simply will not work, also I'm
unable
to
use the same script to replace "&" with a comma ",".

Everything I have read about my first problem on the internet and in
books
tells me it's correct.

Pleasse help.........
Click to expand...
Click to expand...
Click to expand...
 
No reason other than typing skills.

ACCESS is almost never case-sensitive.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Back
Top