Case Statement

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

Guest

Does Access have a function similar to Case in DB2 or Oracle? What I am trying to do is clean up some data which has been entered as either "MNB" or "MNC" where it should only read "MNB". So in DB2 my query would read

Select Case C_Typ
When "MNC" then "MNB
Else C_Typ
End as C_Typ

From ...

I would like to avoid going down the unions route if possible

Any help would be much appreciated.
 
Rowan

Check Access HELP -- "Select Case" is a valid function in Access, but the
syntax may vary from what you're used to.
 
Access does have a Select Case ... End Select statement. In the
circumstance you describe, however, you may only need an Update Query to do
your data cleanup. For example:

- Open a new query and select the table containing the field/fields to be
updated.
- Right click in the upper portion of the query's design and click Query
Type from the popup menu.
- Select Update Query
- In the first empty column in the lower portion of the query's design view,
enter the following in each row:
Field: row - C_TYPE
Update to: row - "MNB"
Criteria: row - "MNC"

Should your needs involve analyzing the field for numerous different values,
you could create a function in a public module which could be called by your
update query. Something like the following:

Public Function UpdateC_TYPE (strCtype as string) as String

Select Case strCtype
Case "MNC"
UpdateC_TYPE = "MNB"
Case "XYZ"
UpdateC_TYPE = "ABC"
Case Else
UpdateC_TYPE = "MNB"
End Select

End Function

You would then use this function in your Update Query in the Update to: row
as follows:

UpdateC_TYPE([C_TYPE])


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Rowan said:
Does Access have a function similar to Case in DB2 or Oracle? What I am
Click to expand...
trying to do is clean up some data which has been entered as either "MNB" or
"MNC" where it should only read "MNB". So in DB2 my query would read:
 
Does Access have a function similar to Case in DB2 or Oracle? What I am trying to do is clean up some data which has been entered as either "MNB" or "MNC" where it should only read "MNB". So in DB2 my query would read:

Select Case C_Type
When "MNC" then "MNB"
Else C_Type
End as C_Type

From ....

I would like to avoid going down the unions route if possible.

Any help would be much appreciated.
Click to expand...

How about typing
Select Case
in your VBA Help file Index box and seeing what comes up.
 
I am trying to do this in SQL and not VBA, hence the posting in Access - Queries and not Access - Programming. Sarcastic replies not needed thank you very much.
 
You can use an Immediate If statement

SELECT IIF(C_type="MNC","MNC",C_Type) as TheType
FROM ...
 
Back
Top