month function..

[thewabit]

I am trying to write a function to look at a date field that has a format of:1/21/2010 and change the numerical month to an abbreviated textual month such as: Jan.

"When I run the query, it gives me an eror of " wrong number of arguments used with function in query expression 'datetomonth([obdate])'

Here is the function:
Public Function datetomonth()
MonthName(1, True)
monthname(2, True)
monthname(3, True)
monthname(4, True)
monthname(5, True)
monthname(6, True)
monthname(7, True)
monthname(8, True)
monthname(9, True)
monthname(10, True)
monthname(11, True)
monthname(12, True)




End Function

They are all in red too..which makes me think I did something wrong.

[c_smithwick]

First off, your function as written doesn't ask for any input arguments - that's why you are getting the error. Also, you aren't returning anything. Assuming you want to change your input to a formatted string output, use the function below. (the input argument is declared as a variant in order to accept either a string or a date variable as input)

Function datetomonth(varInput as Variant) as String
    datetomonth = Format(CDate(varInput), "d mmm yyyy")
End Function

[thewabit]

Thanks!

I understerstand now. But when I run the query that has the "datetomonth" in it, it still says "undefined function..." How does the query know to go look for that module?

[c_smithwick]

Sorry - The Function must be public for the query to "see" it

Public Function datetomonth(.......

and make sure and save the module first before trying it, or the changes might not be recognized.

[thewabit]

Im not sure why it is not working....

I have put the code in as you sent it...I changed it to public.

Public Function datetomonth(varInput As Variant) As String
datetomonth = Format(CDate(varInput), "d mmm yyyy")
End Function

Here is the SQL:

SELECT Year([obdate]) AS [Year], datetomonth([ObDate]) AS OBMonth, tblLOSA_Details.HumanFactor, Count(tblLOSA_Details.HumanFactor) AS HumanFactorQTY, tblLOSA_Details.RiskLevel
FROM tblObservations INNER JOIN tblLOSA_Details ON tblObservations.ObID = tblLOSA_Details.ObID
GROUP BY Year([obdate]), datetomonth([ObDate]), tblLOSA_Details.HumanFactor, tblLOSA_Details.RiskLevel;

[c_smithwick]

I notice in your query the output you are seeking from the function is a field called "OBMonth". Are you just wanting the month portion of your input returned? When you say it isn't working, what exactly is it doing or not doing - what kind of output are you getting from your query. I've run the function with both a date literal input (#1/1/2010#) and a string input ("1/1/2010") and both return "1 Jan 2010" as the output. I need more info as to exactly what problem you are having.

[thewabit]

Thanks for hanging with me here.

Here is the first SQL: (from the earlier post)

SELECT Year([obdate]) AS [Year], datetomonth([ObDate]) AS OBMonth, tblLOSA_Details.HumanFactor, Count(tblLOSA_Details.HumanFactor) AS HumanFactorQTY, tblLOSA_Details.RiskLevel
FROM tblObservations INNER JOIN tblLOSA_Details ON tblObservations.ObID = tblLOSA_Details.ObID
GROUP BY Year([obdate]), datetomonth([ObDate]), tblLOSA_Details.HumanFactor, tblLOSA_Details.RiskLevel;

Here is the second SQL: (this is a query of that first query above to get data to populate a graph...

SELECT MonthDisplay([OBMonth]) AS Expr1, Sum(qryLOSATrend_HF.HumanFactorQTY) AS SumOfHumanFactorQTY
FROM qryLOSATrend_HF
GROUP BY MonthDisplay([OBMonth]), (Year([OBMonth])*12+Month([OBMonth])-1)
ORDER BY (Year([OBMonth])*12+Month([OBMonth])-1);

When I say "It is not working"..(I should have been more clear...sorry)...I meant that I put in your function but when I run the first query, it still says "undefined function....datetomonth...".

It appears that the datetomonth is a function to extract the month from obdate (which is a table field that has a format of "m/dd/yyyy") and is naming that "ObMonth"

Then it appears that ObMonth is used in the next query to display the month (format "mmm") for the graph.

I am trying to piece this DB together as I have inherited it. Sorry to be so long. Thanks for your help!

[c_smithwick]

since your final display is just based on the second query, it seems useless to be manipulating the [obdate] field so many times, as the output for your second query looks like it is simply pulling Year and Month data from the [OBMonth] field and formatting it for display. You can do all of these same operations on your original [OBdate] field. I would suggest changing your first query to:

SELECT Year([obdate]) AS [Year], [ObDate] AS OBMonth, tblLOSA_Details.HumanFactor, Count(tblLOSA_Details.HumanFactor) AS HumanFactorQTY, tblLOSA_Details.RiskLevel
FROM tblObservations INNER JOIN tblLOSA_Details ON tblObservations.ObID = tblLOSA_Details.ObID
GROUP BY Year([obdate]), Month([ObDate]), tblLOSA_Details.HumanFactor, tblLOSA_Details.RiskLevel;

This will just pass the [ObDate] field through to the next query, where it can be manipulated for output. No need to re-invent the wheel.

[thewabit]

Thanks! It looks like we are making progress. I did have to change things a little but the graph now plots the number based on the listbox selection. HOWEVER...1st problem...the date on the x axis is a month but it is always "Jan". The records over in the listbox are all "Oct" records. I'm not sure why it is not picking up the right month. the first query runs and shows "10" for Oct.


Graph query (2nd)

SELECT Format([OBMonth],"mmm") AS Expr1, Sum(qryLOSATrend_HF.HumanFactorQTY) AS SumOfHumanFactorQTY
FROM qryLOSATrend_HF
GROUP BY Format([OBMonth],"mmm"), (Year([OBMonth])*12+Month([OBMonth])-1)
ORDER BY (Year([OBMonth])*12+Month([OBMonth])-1);

[c_smithwick]

Don't know what else to tell you without knowing exactly what your raw data is. Make sure ANYTIME you are performing an operation on a date field, you ensure it is in date format or you might get screwy results. I have encountered situations in the past where access has taken data from a date field and not treated it as a date (or at least not how I expected it to). Read up on how access stores and displays dates and you might find the answer.

[thewabit]

Thanks for your help! I will keep searching.

[maximus]

Reading you thread I assume these are your requirements:
1) Your date is in the Format mm/dd/yyyy. e.g 4/15/2010
2) You want to write a function to display abbreviated names for months

Option Compare Database
Dim dtMyDate As Date
Dim MyMonthIndex As Integer
Dim strMonthName(12) As String

Function AbbMonth(dtMyDate) As String
'************************************
'* written by Siddthartha Majumdar *
'* on 04/19/2010 *
'************************************
'scope of the function
'a)Will Display abbreviated names for months from any given date.
If IsNull(dtMyDate) Then 'if MyDate is Null exit function
Exit Function
Else
strMonthName(1) = "Jan" 'Array contaning the abbreviated names of the months
strMonthName(2) = "Feb"
strMonthName(3) = "Mar"
strMonthName(4) = "Apr"
strMonthName(5) = "May"
strMonthName(6) = "Jun"
strMonthName(7) = "Jul"
strMonthName(8) = "Aug"
strMonthName(9) = "Sep"
strMonthName(10) = "Oct"
strMonthName(11) = "Nov"
strMonthName(12) = "Dec"


MyMonthIndex = DatePart("m", dtMyDate) 'Used date part function to to get the Month Index as integer
AbbMonth = strMonthName(MyMonthIndex) 'Using month Index to display Month Name.
End If
End Function


This function can be used in both queries and forms:

Syntax:AbbMonth(dtMyDate)

Attaching a mdb with a form AbbDate at startup. type date in the text box provided and press enter and see the abbreviated month name is displayed in the text box below.

If this solves your problem mark the thread solved.

[c_smithwick]

Nice function, but it's superfluous. You can do the same thing by using the standard VBA Format function in one line

Format(dtMyDate, "mmm")

[maximus]

Your are right absolutely right. I just wanted to show if you do need to write some thing like this how can you do it.