If I were doing this I would add an equivalents table
ApprovalValues
Field: CurrentValue
Field: NewValue
Then all you need to do is run an update query that looks like the
following
UPDATE Table INNER JOIN ApprovalValues
ON Table.[Approval Status] = ApprovalValues.CurrentValue
SET Table.[Approval Status] = [ApprovalValues].[NewValue]
I am sorry but i don't understand
" In 2 other field I have tekst like {"dfadfadfdfgsdg"} and I want to
remove the {" and "} by adding code to the code above."
If you mean that in FieldA you have the word "And" and you wish to remove
that then you could probably do that with an update query that looks like
the following
UPDATE Table
SET Table.[FieldA] = Trim(Replace(" " &
.[FieldA] & " ", " and "))
WHERE Table.[FieldA] Like "* and *"
Using a query to do this is almost always more efficient than stepping
through a recordset. Plus, in my experience, using an update query seems
to
lead to less bloat in the database.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Ok a bit more explaination:
I have created a switchboard with a button and build an event
procedure.
For 1 field I use the following code to replace text
Set db = CurrentDb
strSQL = "SELECT [Approval Status] FROM
;"
Set rst = [db].OpenRecordset(strSQL, dbOpenDynaset)
With [rst]
If (.EOF = False) Then
.MoveFirst
Do While (.EOF = False)
.Edit
Select Case ![Approval Status]
Case "approved"
![Approval Status] = "yes"
Case "denied"
![Approval Status] = "x"
Case "pending"
![Approval Status] = " "
Case Else
MsgBox ![Approval Status] & vbCrLf & "This is wrong!",
vbExclamation
End Select
.Update
.MoveNext
Loop
End If
.Close
End With
Set rst = Nothing
[db].Close
Set db = Nothing
In 2 other field I have tekst like {"dfadfadfdfgsdg"} and I want to
remove
the {" and "} by adding code to the code above.
Thanks for helping
:
You did not say how you are doing this. Assuming you are using the
replace
function
Replace([SomeField],Chr(34),"") will replace a quotation mark (") with
a
zero-length string
You can search for records that have a quotation mark using
Field: SomeField
Criteria: LIKE "*""*"
Or if that is a bit confusing
Criteria: LIKE "*" & Chr(34) & "*"
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
I have a table with multiple fields.
In more then one field there are character as { } or ""
I want to remove these characters.
How can I remove these characters? I know already use a code to
replace
text
and it is preffered to add these removal to the code
Thanks