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

Open in new window

Sandra SmithRetiredAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl..

MonthNo: WhatMonthIsIt([tblSalesDaily445_and_Calendar].[445Month])
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>WhatMonthIsIt("&tblSalesDaily445_and_Calendar.445Month&")
The double-quotes mean you are passing a string named &tblSalesDaily445_and_Calendar.445Month&"), which doesn't translate to any month in your WhatMonthIsIt function.

Exactly how are you determining what month to feed the function?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Sandra SmithRetiredAuthor Commented:
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.
0
 
Sandra SmithRetiredAuthor Commented:
WORKED!!!!!
0
 
Sandra SmithRetiredAuthor Commented:
thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.