UPDATE using CASE

[dbonin1599]

I am trying to figure out how to update a field in my database based on varying criteria. For instant, if field is currently "Tree" I want to set it to "OAK", but if it is currently "Leaf" I want it set to "Maple" SO in my code I have this so far, but, of course, it is not working or I wouldn't be writing here.

UPDATE TreeTable
SET TreeTable.Type =
CASE TreeTable.Type = "MuniPD"
TreeTable.Type = "Albert Lea PD"
CASE TreeTable.Type = "DPS"
TreeTable.Type = "State Patrol"
ELSE TreeTable.Type= "NO WAY"
END
Can anyone give me some direction on this?

Dan

 

[HiTechCoach]

I am trying to figure out how to update a field in my database based on varying criteria. For instant, if field is currently "Tree" I want to set it to "OAK", but if it is currently "Leaf" I want it set to "Maple" SO in my code I have this so far, but, of course, it is not working or I wouldn't be writing here.

UPDATE TreeTable
SET TreeTable.Type =
CASE TreeTable.Type = "MuniPD"
TreeTable.Type = "Albert Lea PD"
CASE TreeTable.Type = "DPS"
TreeTable.Type = "State Patrol"
ELSE TreeTable.Type= "NO WAY"
END
Can anyone give me some direction on this?

Dan

dan,

Welcome to AWF!

Unfortunately, JET/ACE (Access's default database engine) does not support that system.

I normally handle this by creating a public function in a Code module that can be called from the query.

Place this in a code module:

Option Compare Database
Option Explicit


Public Function fConvertType(pstrCurrentType As String) As String

Select Case pstrCurrentType

   Case "MuniPD"
        fConvertType = "Albert Lea PD"
   Case "DPS"
        fConvertType = "State Patrol"
   Case Else
        fConvertType = "NO WAY"

End Select

End Function

Example usage:

? fConvertType("MuniPD")
Albert Lea PD

NOTE: If you have a lot, I would create a table witht he conversion list and have the function look it up from the table.


Your SQL will be something like this:

   ..     SET TreeTable.Type = fConvertType(TreeTable.Type) ...

Hope this helps ...

 

[ajetrumpet]

to my knowledge, CASE is a vba statement, NOT sql. there is no way easy way to do this i don't think. you need IF statements or IIF() statements for every value in the table.

if you seriously have a lot of different values in the table, i would consider putting the updated values AND the old values in another table and doing it that way, otherwise this situation will get UGLY with code, and you will easily get lost in it.


for example, if you have OLD VALUE = field1 and NEW VALUE = field2 in a table called TBL, then you can UPDATE your table without CASE statements like this:

dim db as database
dim rs as recordset
set db=currentdb
set rs=db.openrecordset("tbl")

with rs
   .movelast
   .movefirst

      do until .eof
         db.execute "UPDATE OLDtable SET " & _
                         "[oldvaluefield] = '" & !field2 & "' " & _
                         "WHERE [oldvaluefield] = !field1"
         .movenext
      loop

end with

db.close
rs.close
set db=nothing
set rs=nothing

 

[dbonin1599]

Thanks so much.

I actually went back and tried the IIF statements since there are not a lot of variables rather just a lot of records that need updating. This worked great. One note though, I wasn't sure how the last if statements FALSE (see below) should be handled. I finally used the field itself to update itself and that worked.

UPDATE DGBTestVariations
SET DGBTestVariations.LEA =
IIF(DGBTestVariations.LEA = "MuniPD", "Albert Lea PD",
IIF (DGBTestVariations.LEA = "MuniPD2", "Alden PD",
IIF(DGBTestVariations.LEA = "DPS","State Patrol",
IIF(DGBTestVariations.LEA = "Sheriff","Freeborn County Sheriff",
IIF(DGBTestVariations.LEA = "DNR","DNR",
IIF(DGBTestVariations.LEA = "DOT","DOT",
IIF(DGBTestVariations.LEA = "ANY","ANY",DGBTestVariations.LEA )
)
)
)
)
)
)

Thank you for the help, sometimes it is quite a chore getting things done in MS Access, but I can usually find a way to make it happen, I am just so thankful for a site like this where we can lock brains together with other developers with similar issues, to come out with better or at least usable solutions.

I really like your idea and have a place I can use it.

Cheers,

Dan

 

[LPurvis]

Switch would be a more common function choice for Jet in your position - AFAICR it is a shared function and so would work with Jet even from data access external to Access.

That aside, the substitution table as Boyd mentions is the best bet anyway.

Cheers.

 

[dbonin1599]

Switch would be a more common function choice for Jet in your position - AFAICR it is a shared function and so would work with Jet even from data access external to Access.

That aside, the substitution table as Boyd mentions is the best bet anyway.

Cheers.

My main issue here is I want to avoid VB, this is a one time table update, so I want to do it SQL as much as possible, the IIF worked

I have never use Switch in Access, how is it used? Is it similar to Case?

Dan

 

[LPurvis]

No I was speaking from a query perspective. Pretty much all non-aggregate SQL functions are shared VBA functions called by the expression service.

Switch passes value based upon the first matching expression.
Direct from the help file: (which is rare for me to quote - but, hey, it's there ;-)

Matchup = Switch(CityName = "London", "English", CityName _
= "Rome", "Italian", CityName = "Paris", "French")

But can, as I said before, be called from a query.
There's no "Else" clause.
However you could consider only updating rows which fall within one of the values which interests you - it's more efficient anyway, rather than updating rows with their own value.

Nothing with per se with your nested immediate if statement, but they get messy very quickly.

 

[dbonin1599]

Thanks Leigh, ajetrumpet and HiTechCoach

You are the reason this sight is so much fun to visit.

Happy Holidays from Minnesota!