Get MonthNumber from abbreviated month name

[avenuw]

Hello,

i am trying to get the month number to order the months in my sql query from an column (abbMon) with abbreviated month names in the format MMM.

this is what i have

select month(abbMon,T) from myTable
order by month(abbMon,T)

But I get an error indicating wrong number of arguments used in my query. I looked up the month function and the syntax is the following

Month(date,(abbrev))
where abbrev is a boolean character to indicate if date is abbreviated.

What am i doing wrong?

Thanks,

 

[Golom]

Don't know where you saw that but my documentation for Month says
[blue]
Month Function

Returns a Variant (Integer) specifying a whole number between 1 and 12, inclusive, representing the month of the year.

Syntax

Month(date)

The required date argument is any Variant, numeric expression, string expression, or any combination, that can represent a date. If date contains Null, Null is returned.
[/blue]
there is no second argument.

You just want

select month(abbMon) from myTable
order by month(abbMon)

assuming that "abbMon" contains a Datetime field.

 

[avenuw]

Hi Golom,

I tried the following code and gave me an #Error for the entire column

select month(abbMon) from myTable
order by month(abbMon)

abbMon contains abbreviated month names ( that I got using format(mydate,"MMM")previous query called myTable.

Any ideas ?

 

[MajP]

from an column (abbMon) with abbreviated month names

Public Function MonthNumber(strMonth As String) As Integer
  MonthNumber = Month(CDate("01 " & strMonth & " 2008"))
End Function
[code]

[code]
select MonthNumber(abbMon) from myTable
order by MonthNumber(abbMon)
[/code}

 

[MajP]

abbreviated month names ( that I got using format(mydate,"MMM")previous query called myTable

I did not see that part. So if you have a real date stored somewhere use that in your query as Golom suggests. The month function is used on a date variable.

You can use my fix but all you are then doing is taking a date converting it to a string (using format), and then taking the string and converting it back to a date.

 

[EJVTECH]

Easiest way I can think of would be to have a monthID field in myTable that is 1 through 12 (January thru December). Then your sql would be:

SELECT Left$(MonthName([monthID]),3) AS abbMonth
FROM myTable
ORDER BY myTable.monthID;

 

[avenuw]

Thanks for all your suggestions.

What i did eventually was instead of tryng to get monthNumber from query2, I went to the previous query1 ( which query2 gets its info from) and used the month() method on the original date. Much easier than using the month method on the abbreviated month name.

Thanks Again

 

[MajP]

good choice.