SQL question

F

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
 
6

'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.
 
T

Tom Wickerath

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
__________________________________________
 
F

Fritz Peinbauer

Thanks a lot, that it!




'69 Camaro said:
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.
 
J

Jan Kowalski

U¿ytkownik "Fritz Peinbauer said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top