Question Locked

Month Name function problem in MS Access query

0 helpful
22 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a simple table containing order number, order date, cd number, cd title etc. I have imported 5000 records and need to analyse the data for trends. I need to create a query that will extract and list month names and the number of orders for each month. The closest I have got to this has resulted in a parameter query which I do not want. I want to view all months. Can anyone help?

22 replies

by tony hopkinson

Select MonthName(datepart("m",orderdate),true)
as OrderMonth,
count(ordernumber) from cdorders
Group By OrderMonth

or something like that anyway.

by traceyae

I'm getting 3 parameter boxes coming up asking me to enter order number, order date and order month. The current order date field is ddmmyyyy. Could this be anything to with it?

by tony hopkinson

I spelt your column names wrong.

orderdate should be [order date] perhaps?

by tony hopkinson

I spelt your column names wrong.

orderdate should be [order date] perhaps?

by bschaettle

RE: "The current order date field is ddmmyyyy"

Yes, I believe this is exactly the problem; I had a similar situation bite me just last week. Remember that Access is a Microsoft Office product, and it will likely interpret your ddmmyyyy string as an integer "datevalue" -- the number of days since January 1, 1900.

Last week I had a user type "031907" into an Access query prompt, intending to restrict a query to March 19, 2007. Access interpreted this string as the integer value 31907 and internally converted it to the date May 10, 1987. You can see this date calculation easily in Excel: type 1/1/1900 into a cell, then in another cell add 31907 to it and format the result as a date.

I'm pretty sure this will give you what you want:

Select MonthName(Mid(orderdate,3,2))
as OrderMonth,
count(ordernumber) from cdorders
Group By OrderMonth

by beth blakley

edited by moderator

Message was edited by: beth.blakely@...
Posted: 03/23/2007 @ 07:41 (edited 03/23/2007 @ 08:07)

by traceyae

I entered
SELECT
MonthName(Mid(OrderDate,3,2))
as OrderMonth,
COUNT(OrderNumber)
FROM tblOrder
GROUP BY MonthName(Mid(OrderDate,3,2))
and am now getting an error message saying
data type mismatch in criteria expression

by bschaettle

Maybe "MonthName" doesn't like strings with leading zeros, like "01" for January. Try casting the 2-digit month strings as an integer:

SELECT
MonthName(CInt(Mid(OrderDate,3,2)))
as OrderMonth,
COUNT(OrderNumber)
FROM tblOrder
GROUP BY MonthName(CInt(Mid(OrderDate,3,2)))

by tony hopkinson

or Month() ??
Like the other guy said dates in a different format ddmm.... is aht it expects, or OrderDate can't be null can it?
Mid(... would probably return an empty string, which Monthname won't like at all.

by beth blakley

edited by moderator

Message was edited by: beth.blakely@...
Posted: 03/23/2007 @ 07:41 (edited 03/23/2007 @ 08:07)

by traceyae

I changed it to reflect the fields in the database e.g.
Select MonthName(datepart("m",Order_Date),true)
as OrderMonth,
count(Order_Number) from tblOrder
Group By OrderMonth
and that's when I got the 3 parameter boxes coming up

by tony hopkinson

Parameters get asked for on unrecognised names ususally in access, or at least that used to be the way it worked

Don't use it my self, I* morped the query for a sql server database and it objected to OrderMonth.

Select MonthName(datepart("m",Order_Date),true),
count(Order_Number) from tblOrder
Group By MonthName(datepart("m",Order_Date),true)

Sudden thought, you were doing this with parameters, has access kept them ?

by traceyae

Thanks for replying. I entered the following:
Select MonthName(Mid(OrderDate,3,2))
as OrderMonth,
count(OrderNumber) from tblOrder
Group By OrderMonth

and got an error message saying:
you tried to execute a query that does not include the specified expression MonthName(Mid(OrderDate,3,2))as part of an aggreagate function

by bschaettle

You need to read the error statement you got very literally, and do what it says. Look at the Group By clause below and you'll understand what I mean. You'd think that you could reference "OrderMonth" in the Group By clause, but it doesn't work that way. Sometimes SQL drives me nuts.
--Bob

SELECT
MonthName(Mid(OrderDate,3,2))
as OrderMonth,
COUNT(OrderNumber)
FROM tblOrder
GROUP BY MonthName(Mid(OrderDate,3,2))

by tony hopkinson

though SQL server gave me a sensible error message instead of the foolishness in access.

Sure that worked on some other DBMS's , obviously the SQL parser is leaving the AS until after the Group BY, so it goes Huh?

Don't like the use of Mid though, always makes me nervous with locales.

by traceyae

Have just excitedly tried:
SELECT
MonthName(CInt(Mid(OrderDate,3,2)))
as OrderMonth,
COUNT(OrderNumber)
FROM tblOrder
GROUP BY MonthName(CInt(Mid(OrderDate,3,2)))

But, I'm getting the same error message as last time. I don't understand it. It should be a simple enough query but it just won't work

by bschaettle

As I recall, the error you're getting says something about "data type mismatch."

At this point I suspect that you have some bad data in your table.

I would use the following query and look at the list of 2-character strings it pulls for the 'mm' portion of your OrderDate field. See if there are any that do not correspond to the numbers 1->12.

SELECT
Mid(OrderDate,3,2)
as OrderMonth,
COUNT(OrderNumber)
FROM tblOrder
GROUP BY Mid(OrderDate,3,2)

by traceyae

I changed date/time field to required and added
SELECT
Mid(OrderDate,3,2)
as OrderMonth,
COUNT(OrderNumber)
FROM tblOrder
GROUP BY Mid(OrderDate,3,2)
this resulted in the following being returned /0 and /1 in the order month field.
I changed it to
SELECT Mid(OrderDate,4,2) AS OrderMonth, Count(tblOrder.OrderNumber) AS CountOfOrderNumber
FROM tblOrder
GROUP BY Mid(OrderDate,4,2);
and lo and behold it returns the 12 months and the number of orders for each
A big thankyou!

by bschaettle

...if you have any NULLs or blank strings in the OrderDate field, this query won't handle it. You'll have to handle those exceptions using IIF, or scrub the data before analyzing it.

by shellbott

Edited by moderator

Message was edited by: beth.blakely@...

by bschaettle

RE: "resulted in the following being returned /0 and /1"

In your post of 3/25 you said: "The current order date field is ddmmyyyy"

However, the slashes you got when you ran the query successfully tell me that your OrderDate is actually in 'dd/mm/yyyy' format. Please try to be more precise when you post online.

This discussion is closed to new replies.