Convert SQL to MS Access SQL (CASE, WHEN)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Convert SQL to MS Access SQL (CASE, WHEN)

Author

Topic

dofree
Starting Member

3 Posts

Posted - 2007-11-06 : 13:53:35

Someone that can convert this SQL code to something working with MS Access (2003/2007) SQL?

Göran

UPDATE
tblA, tblB
SET
credit =
(CASE
WHEN credit >= credit3 THEN credit4
WHEN credit >= credit2 THEN credit3
WHEN credit >= credit1 THEN credit2
ELSE credit1
END)
WHERE
tblB.SupplierID = tblA.SupplierID AND tblA.ClientID = 6

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
http://www.dofree.se Program för; Fakturering / Bokföring / Inkasso m.m. även GRATIS!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-06 : 19:37:52

You don't have to convert it to Access. You can runSQL in Access VBA if need be.

However,you can build it with Access' Immediate If function in the query builder using the design view for help
.
something like this:


Update TblA
Set credit = IIF(Credit>=credit3,credit4,IIF(Credit>=credit2,credit3,IIF(Credit>=credit2,credit2,credit1)))
FROM TblA inner join TblB on TblA.SupplierID = TblB.SupplierID
WHERE tblA.ClientID = 6

and then this for table b:


Update TblB
Set credit = IIF(Credit>=credit3,credit4,IIF(Credit>=credit2,credit3,IIF(Credit>=credit2,credit2,credit1)))
FROM TblA inner join TblB on TblA.SupplierID = TblB.SupplierID
WHERE tblA.ClientID = 6

The main problem you will have is that you can't update two tables at once, as told by Peso in your other post on this topic.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92182

dofree
Starting Member

3 Posts

Posted - 2007-11-07 : 11:02:34

Thanks dataguru71,

you put me in the right direction.
Sorry for fooling you with 2 tables.
I was thinking wrong. I only need 1.

Hera are MS Access SQL code that work:

UPDATE
tblA
SET
credit =
IIf([Credit] >=[credit3],[credit4],
IIf([Credit]>=[credit2],[credit3],
IIf([Credit]>=[credit1],[credit2],
[credit1])));

tblA have this field:
PostID : int
Credit : currency
Credit1 : currency
Credit2 : currency
Credit3 : currency
Credit4 : currency

10-4

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
http://www.dofree.se Program för; Fakturering / Bokföring / Inkasso m.m. även GRATIS!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources