Converting Dates to "Month" in a Query

[nsymiakakis]

Hi All, First let me start by saying, I absolutely love this site. I have an access database that records are keyed in including a date field. i.e. 3/4/07, 3/20/07, 4/10/07,4/18/07, 5/4/07... and queried using a parameter query that asks for a Start and End date. i.e. 1/1/2007, 5/1/2007. These results are in the form of number Totals for that period.
This all works well. Now I have been asked to create a bar graph on a report that will show Month to Month progress, i.e Jan - June. Something to the effect of:
Jan Feb Mar Apr May
40 30 28 20 25

Then I can use a bar graph to graph the numbers and use the months as the "X" axis.

Any idea on how to convert date fields to Month names in a parameter query, if the user wants a graph to show a range of months that they choose, i.e 3/4/07, 3/20/07, 3/27/07 to come up as "March"?
I have limited knowledge of SQL, so please be kind and detail your responses.
Thank you very much in advance.
Nick

[MMcCarthy]

Hi All, First let me start by saying, I absolutely love this site. I have an access database that records are keyed in including a date field. i.e. 3/4/07, 3/20/07, 4/10/07,4/18/07, 5/4/07... and queried using a parameter query that asks for a Start and End date. i.e. 1/1/2007, 5/1/2007. These results are in the form of number Totals for that period.
This all works well. Now I have been asked to create a bar graph on a report that will show Month to Month progress, i.e Jan - June. Something to the effect of:
Jan Feb Mar Apr May
40 30 28 20 25

Then I can use a bar graph to graph the numbers and use the months as the "X" axis.

Any idea on how to convert date fields to Month names in a parameter query, if the user wants a graph to show a range of months that they choose, i.e 3/4/07, 3/20/07, 3/27/07 to come up as "March"?
I have limited knowledge of SQL, so please be kind and detail your responses.
Thank you very much in advance.
Nick

Hi Nick,

If you post the full SQL of the query you are currently using we can adapt it to show the months using the Month() or MonthName() functions.

Mary

[nsymiakakis]

Hi Mary, below are three queries. The first and second I created to sum the totals from two different tables, and then the third gives me a total of the first two. This is the total that I need to query using Months converted from the Date field.

SELECT
Sum([Environmental Rounds].[Bathroom handrails]) AS [SumOfBathroom handrails],
Sum([Environmental Rounds].[Emergency Pull Stations]) AS [SumOfEmergency Pull Stations],
[SumOfBathroom handrails]+[SumOfEmergency Pull Stations] AS Total_A
FROM [Environmental Rounds]
WHERE ((([Environmental Rounds].Date) Between [Enter Start Date] And [Enter End Date]));

SELECT
Sum([Environmental Rounds Medical Equipment].[Equipment in good repair]) AS [SumOfEquipment in good repair],
Sum([Environmental Rounds Medical Equipment].[Staff know how to request equipment repair]) AS [SumOfStaff know how to request equipment repair],
Sum([Environmental Rounds Medical Equipment].[Vertical blinds/window treatments in good repair]) AS [SumOfVertical blinds/window treatments in good repair],
[SumOfEquipment in good repair]+[SumOfStaff know how to request equipment repair]+[SumOfVertical blinds/window treatments in good repair] AS Total_B
FROM [Environmental Rounds Medical Equipment]
WHERE ((([Environmental Rounds Medical Equipment].Date) Between [Enter Start Date] And [Enter End Date]));

SELECT TotalGenRepair_ A.Total_A, TotalGenRepair_ B.Total_B, TotalGenRepair_ A.Total_A+Total GenRepair_B.Tot al_B AS GrandTotalGenRe pair
FROM TotalGenRepair_ A, TotalGenRepair_ B;

When I run the third Query, it prompts for the "Start Date", and "End Date" and totals the other two. This part works fine, although I probably took the long way around doing this.
Thanks,
Nick

[MMcCarthy]

Leave it with me Nick. I'll get to it in a while.

[MMcCarthy]

Leave it with me Nick. I'll get to it in a while.

SELECT (ER.Date) As MyDate, Sum(ER.[Bathroom handrails]) + Sum(ER.[Emergency Pull Stations]) AS Total
FROM [Environmental Rounds] AS ER
UNION
SELECT ERME.Date As MyDate, Sum(ERME.[Equipment in good repair])+Sum(ERME.[Staff know how to request equipment repair])+Sum(ERME.[Vertical blinds/window treatments in good repair]) As Total
FROM [Environmental Rounds Medical Equipment] AS ERME
WHERE MyDate Between [Enter Start Date] And [Enter End Date]))

SELECT MonthName(MyDate) As Month, Sum(Total) As GrandTotal
FROM Qry1
GROUP BY MonthName(MyDate);

The first query should replace the three you had previously and the second should give you monthly totals by MonthName.

Mary

[nsymiakakis]

Hi Mary, this definately looks a lot simpler, and I am sure we can get this to work. When I run this, I am getting an error:
"You tried to execute a query that does not include the sprcified expression 'MyDate' as part of an aggregate function."

Any ideas?
Nick

[MMcCarthy]

Try this ...

SELECT (ER.Date) As MyDate, Sum(ER.[Bathroom handrails]) + Sum(ER.[Emergency Pull Stations]) AS Total
FROM [Environmental Rounds] AS ER
UNION
SELECT ERME.Date As MyDate, Sum(ERME.[Equipment in good repair])+Sum(ERME.[Staff know how to request equipment repair])+Sum(ERME.[Vertical blinds/window treatments in good repair]) As Total
FROM [Environmental Rounds Medical Equipment] AS ERME
WHERE MyDate Between [Enter Start Date] And [Enter End Date]))
GROUP BY MyDate

Mary

[nsymiakakis]

Same Error.
To troubleshoot, I took out all references to Mydate, and just ran the Summing query, and it worked great.
Next I recopied what you sent me, and I took out all the lines except for the first two...
SELECT (ER.Date) As MyDate, Sum(ER.[Bathroom handrails]) + Sum(ER.[Emergency Pull Stations]) AS Total
FROM [Environmental Rounds] AS ER

...and it came up with the error. So it is erroring on (ER.Date) As MyDate

[MMcCarthy]

Try this ...

SELECT [Date], Sum(ER.[Bathroom handrails]) + Sum(ER.[Emergency Pull Stations]) AS Total
FROM [Environmental Rounds] AS ER
WHERE [Date] Between [Enter Start Date] And [Enter End Date]))
UNION
SELECT [Date], Sum(ERME.[Equipment in good repair])+Sum(ERME.[Staff know how to request equipment repair])+Sum(ERME.[Vertical blinds/window treatments in good repair]) As Total
FROM [Environmental Rounds Medical Equipment] AS ERME
WHERE [Date] Between [Enter Start Date] And [Enter End Date]))
GROUP BY [Date]

This is going to ask for the dates twice.

Mary

[nsymiakakis]

Sorry Mary, Same aggregate function error, this time with the word "Date" instead of "My Date"
I appreciate you taking the time with me.
Nick

[MMcCarthy]

OK, lets go again ...

SELECT [Date], Sum(ER.[Bathroom handrails]) + Sum(ER.[Emergency Pull Stations]) AS Total
FROM [Environmental Rounds] AS ER
WHERE [Date] Between [Enter Start Date] And [Enter End Date]))
GROUP BY [Date]
UNION
SELECT [Date], Sum(ERME.[Equipment in good repair])+Sum(ERME.[Staff know how to request equipment repair])+Sum(ERME.[Vertical blinds/window treatments in good repair]) As Total
FROM [Environmental Rounds Medical Equipment] AS ERME
WHERE [Date] Between [Enter Start Date] And [Enter End Date]))
GROUP BY [Date]

This is still going to ask for the dates twice.

Mary

[nsymiakakis]

Hi Mary, That worked great, and it only asked me for the date once.

I ran the 2nd query, modifying it to say "Date" instead of MyDate, and if I don't type in any dates it runs fine, but with no returned data, which I expected.

But if I place in a range of dates, it returns with the error:
"The expression is typed incorrectly, or it is too complex to be evaluated..."
I have gotten this error in the past if the data in the cells was not the correct format for what the query was looking for, i.e trying to "Sum" text.

Here is the query:
SELECT MonthName(Date) AS [Month], Sum(Total) AS GrandTotal
FROM Query1
GROUP BY MonthName(Date) ;

We are almost there,
Nick

[MMcCarthy]

Hi Mary, That worked great, and it only asked me for the date once.

I ran the 2nd query, modifying it to say "Date" instead of MyDate, and if I don't type in any dates it runs fine, but with no returned data, which I expected.

But if I place in a range of dates, it returns with the error:
"The expression is typed incorrectly, or it is too complex to be evaluated..."
I have gotten this error in the past if the data in the cells was not the correct format for what the query was looking for, i.e trying to "Sum" text.

Here is the query:
SELECT MonthName(Date) AS [Month], Sum(Total) AS GrandTotal
FROM Query1
GROUP BY MonthName(Date) ;

We are almost there,
Nick

Date is a reserved word as it is a function. enclose Date in square brackets as follows:

SELECT MonthName([Date]) AS [Month], Sum([Total]) AS GrandTotal
FROM Query1
GROUP BY MonthName([Date]);

[nsymiakakis]

I am sorry Mary, but I get the same error.
When I look under the "Help" for MonthDate, I notice it has this Syntax:
MonthName ( month [, abbreviate] )
where the month is a number from 1-12, since I am using the [Date] field, which is in the form of 3/3/07, will this function still work?

Nick

[nsymiakakis]

How about this senario, I create a field called MonthYear in my table and mask it to only give me >L<LL\-00;;_ from a Medium Date.

Is it possible to have a single Form field input into two different fields on the same table? One that would be the normal Date of 3/15/07 and the other to conver that input to Mar-07.

Nick