CASE expression

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I am a DB2 DBA and cannot remember how to structure the
CASE expression in Access. For instance, I need a query
that will change the number of a month to the actual
name.

SELECT CASE MONTH
WHEN '1' THEN 'JANUARY'
WHEN '2' THEN 'FEBRUARY'
..
..
..
END AS MONTH_NAME
FROM tbl_MONTH;

Anyone know how to perform this function?

Thanks in advance...Tom.
 
Tom said:
I am a DB2 DBA and cannot remember how to structure the
CASE expression in Access. For instance, I need a query
that will change the number of a month to the actual
name.

SELECT CASE MONTH
WHEN '1' THEN 'JANUARY'
WHEN '2' THEN 'FEBRUARY'
.
.
.
END AS MONTH_NAME
FROM tbl_MONTH;

Anyone know how to perform this function?

Thanks in advance...Tom.
Click to expand...
 
Make a function like:

Public Function calmonth(peremonth As Integer)
Select Case permonth
Case Is = 1
calmonth = "January"
Case Is = 2
calmonth = "February"

End Select
End Function


Hope i helped you?
Herman
 
you need a *query* to change the month number to a name?
in a query, you can create a calculated field to show the month name, using
the Choose() function, as

MonthName: Choose(Month([DateFieldName], "January", "February", "March",
"April", "May", "June", "July", "August", "September", "October",
"November", "December")

or you can format the date field, as

MonthName: Format([DateFieldName], "mmmm")

if you really want to use the Select Case statement, open (or create) a
standard module and add the following

Public Function isMonthName(ByVal intMonth As Integer) As String

Select Case intMonth
Case 1
isMonthName = "January"
Case 2
isMonthName = "February"
Case 3
isMonthName = "March"
'all the way down to December...then
Case Else
isMonthName = ""
End Select

call the function from the query, as

MonthName: isMonthName(Nz(Month(DateFieldName), 0))

hth
 
I am not familiar with incorporting a function into my
SELECT....what would it look like?

-----Original Message-----
you need a *query* to change the month number to a name?
in a query, you can create a calculated field to show the month name, using
the Choose() function, as

MonthName: Choose(Month
Click to expand...
([DateFieldName], "January", "February", "March",
"April", "May", "June", "July", "August", "September", "Oc tober",
"November", "December")

or you can format the date field, as

MonthName: Format([DateFieldName], "mmmm")

if you really want to use the Select Case statement, open (or create) a
standard module and add the following

Public Function isMonthName(ByVal intMonth As Integer) As String

Select Case intMonth
Case 1
isMonthName = "January"
Case 2
isMonthName = "February"
Case 3
isMonthName = "March"
'all the way down to December...then
Case Else
isMonthName = ""
End Select

call the function from the query, as

MonthName: isMonthName(Nz(Month(DateFieldName), 0))

hth


I am a DB2 DBA and cannot remember how to structure the
CASE expression in Access. For instance, I need a query
that will change the number of a month to the actual
name.

SELECT CASE MONTH
WHEN '1' THEN 'JANUARY'
WHEN '2' THEN 'FEBRUARY'
.
.
.
END AS MONTH_NAME
FROM tbl_MONTH;

Anyone know how to perform this function?

Thanks in advance...Tom.
Click to expand...


.
Click to expand...
 
Access (Jet) SQL does not support CASE...WHEN.

As an alternative to the other approaches suggested, you might try an
expression like

Format(DateSerial(2000, MONTH, 1), "mmmm")

In a query, the SQL might look something like:

SELECT
Format(DateSerial(2000, MONTH, 1), "mmmm") AS MONTH_NAME
FROM
tbl_MONTH;
 
Back
Top