Update function

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

Guest

Can I create a function thru the module and update the data with new mapping
for a table?
If yes, please tell me how.

The field size is 3 char Text.

Old EthnicCode New EthnicCode

1 700
2 600
3 500
41 201
42 202
43 203


Thanks a bunch,
Siew-Ming
 
Can I create a function thru the module and update the data with new mapping
for a table?
If yes, please tell me how.

The field size is 3 char Text.

Old EthnicCode New EthnicCode

1 700
2 600
3 500
41 201
42 202
43 203

Thanks a bunch,
Siew-Ming
Click to expand...

Do you wish the new data placed in a new field or just overwrite
existing data?
Into a new field.....

Add a module to your database.
Copy the below function to the module.

Function UpdateData(strIn As String)

Dim NewValue As String
Select Case strIn
Case Is = "1"
NewValue = 700
Case Is = "2"
NewValue = 600
Case Is = "3"
NewValue = 500
Case Is = "41"
NewValue = 201
Case Is = "42"
NewValue = 202
Case Is = "43"
NewValue = 203
Case Else
NewValue = strIn
End Select
UpdateData = NewValue
End Function

Call it from an Update query.

UPDATE YourTable SET YourTable.NewEthnicCode =
UpdateData([OldEthnicCode])
WHERE YourTable.OldEthnicCode Is Not Null;
 
fredg said:
Add a module to your database.
Copy the below function to the module.

Function UpdateData(strIn As String)

Dim NewValue As String
Select Case strIn
Case Is = "1"
NewValue = 700
Click to expand...
Case Else
NewValue = strIn
End Select
UpdateData = NewValue
End Function

Call it from an Update query.

UPDATE YourTable SET YourTable.NewEthnicCode =
UpdateData([OldEthnicCode])
WHERE YourTable.OldEthnicCode Is Not Null;
Click to expand...

Personally, I'd create a temporary table that maps the old code to the new
one and use that, rather than a function.
 
and I probably use the Switch() function directly in the Update Query since
there are only 6 possibilities and it sounds like a once-off update.

Cheers
Van
 
Back
Top