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.........