User Defined Function now working in query design grid in ACCESS 2003
I need to supply the function with the name of the month and have it give me back the integer. Simply, I created the function, which does work. However, in the code snippet, the first line is my call using it in the query grid of ACCESS 2003. I simply cannot figure out how to tell it what the month is. "445"Month" is another column with varying month names, so I do not understand where my flaw is.
MonthNo: WhatMonthIsIt("&tblSalesDaily445_and_Calendar.445Month&")
Public Function WhatMonthIsIt(ByRef strMonthName As String) As Integer
If strMonthName = "Jan" Or strMonthName = "January" Then WhatMonthIsIt = 1
If strMonthName = "Feb" Or strMonthName = "February" Then WhatMonthIsIt = 2
If strMonthName = "Mar" Or strMonthName = "March" Then WhatMonthIsIt = 3
If strMonthName = "Apr" Or strMonthName = "April" Then WhatMonthIsIt = 4
If strMonthName = "May" Or strMonthName = "May1" Then WhatMonthIsIt = 5
If strMonthName = "Jun" Or strMonthName = "June" Then WhatMonthIsIt = 6
If strMonthName = "Jul" Or strMonthName = "July" Then WhatMonthIsIt = 7
If strMonthName = "Aug" Or strMonthName = "August" Then WhatMonthIsIt = 8
If strMonthName = "Sep" Or strMonthName = "September" Then WhatMonthIsIt = 9
If strMonthName = "Oct" Or strMonthName = "October" Then WhatMonthIsIt = 10
If strMonthName = "Nov" Or strMonthName = "November" Then WhatMonthIsIt = 11
If strMonthName = "Dec" Or strMonthName = "December" Then WhatMonthIsIt = 12
End Function
btw this is much easier to read
Public Function WhatMonthIsIt(sMonth as string) as Integer
Select Case Left(UCase(sMonth), 3)
Case "JAN"
WhatMonthIsIt = 1
Case "FEB"
WhatMonthIsIt = 2
Case "MAR"
WhatMonthIsIt = 3
Case "You get the idea...."
Case "DEC"
WhatMonthIsIt = 12
Case Else
'You passed an unreadable month into sMonth. Handle it here.
End Select
End Sub
Public Function WhatMonthIsIt(sMonth as string) as Integer
Select Case Left(UCase(sMonth), 3)
Case "JAN"
WhatMonthIsIt = 1
Case "FEB"
WhatMonthIsIt = 2
Case "MAR"
WhatMonthIsIt = 3
Case "You get the idea...."
Case "DEC"
WhatMonthIsIt = 12
Case Else
'You passed an unreadable month into sMonth. Handle it here.
End Select
End Sub
ASKER
Hello Jim, I tried the Select Case statement first, but could not get it to work. The If statement sequence at least (in the immediate window and when I type in the month name in the funciton in query grid) does return the appropriate value. In the query, there is another column entitled 445Month that contains the string short names for the months, ie., Jan, Feb, Mar, etc. So, I want to pass the values from this column to my function, but I know that my function needs quotes for a string and I think that is where I am getting stuck. But I have tried various combinations of quiotes and getting no where. I tried using the full qualified name of the column (table.columnname) and just the column name in the function, but neither worked as well.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
WORKED!!!!!
ASKER
thank you very much!
The double-quotes mean you are passing a string named &tblSalesDaily445_and_Cale
Exactly how are you determining what month to feed the function?