SQL question

[Fritz Peinbauer]

Hello NG,

with Sql Server i user expression like this:

SELECT
column1,
column2,
CASE column3
WHEN 'DE' THEN 'Germany'
WHEN 'FR' THEN 'France'
ELSE 'N/A'
END,
colume4
FROM table1


Is there any equivalent for CASE .. - expression in MSAccces?

Thanks for response

Fritz

 

['69 Camaro]

Hi, Fritz.

Is there any equivalent for CASE .. - expression in MSAccces?

Try the SWITCH function:

SELECT column1, column2,
SWITCH(column3 = "DE", "Germany", column3 = "FR", "France",
column3 <> NULL, "N/A") AS Country, column4
FROM table1;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

Hi Fritz,

You can use the built-in IIF function. The general format is:

IIF(Expression, ValueIfTrue, ValueIfFalse)

In this case, you can nest a few together. Something like this (untested):

IIF([column3] = "DE", "Germany", IIF([column3] = "FR", "France", "N/A"))

While you are allowed to nest up to 10 levels, this can get really ugly and
difficult to maintain if you have several conditions. An alternative is to
write a custom function, and call the function from your query. This is
usually always *much* easier to maintain. Something like this:

CountryCode:=DetermineCountryCode([column3])

You would then create a custom function named DetermineCountryCode. A
similar example is provided here (see message # 9, but read the entire
thread):

http://groups.google.com/group/micr..._frm/thread/b85d4522abcc9b48/a290d168485d48d0


Function DetermineCountryCode (CCode As Variant) As String

If IsNull(CCode) Then
DetermineCountryCode = "N/A"
Exit Function
End If

Select Case CCode
Case "DE"
DetermineCountryCode = "Germany"
Case "FR"
DetermineCountryCode = "France"
Case Else
DetermineCountryCode = "N/A" (Or "Unknown", or whatever)
End Select

End Function


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Fritz Peinbauer]

Thanks a lot, that it!

Hi, Fritz.


Try the SWITCH function:

SELECT column1, column2,
SWITCH(column3 = "DE", "Germany", column3 = "FR", "France",
column3 <> NULL, "N/A") AS Country, column4
FROM table1;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Jan Kowalski]

Hello NG,

with Sql Server i user expression like this:

SELECT
column1,
column2,
CASE column3
WHEN 'DE' THEN 'Germany'
WHEN 'FR' THEN 'France'
ELSE 'N/A'
END,
colume4
FROM table1


Is there any equivalent for CASE .. - expression in MSAccces?

Thanks for response

Fritz

 

[Jan Kowalski]

--
msnews.microsoft.com

 

['69 Camaro]

You're welcome! Glad to help.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.