22 replies
by traceyae Accepted solution
by tony hopkinson Accepted solution
Select MonthName(datepart("m",orderdate),true)
as OrderMonth,
count(ordernumber) from cdorders
Group By OrderMonth
or something like that anyway.
by traceyae Accepted solution
by tony hopkinson Accepted solution
I spelt your column names wrong.
orderdate should be [order date] perhaps?
by tony hopkinson Accepted solution
I spelt your column names wrong.
orderdate should be [order date] perhaps?
by bschaettle Accepted solution
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 Accepted solution
edited by moderator
Message was edited by: beth.blakely@...
Posted: 03/23/2007 @ 07:41 (edited 03/23/2007 @ 08:07)
by traceyae Accepted solution
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 Accepted solution
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 Accepted solution
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 Accepted solution
edited by moderator
Message was edited by: beth.blakely@...
Posted: 03/23/2007 @ 07:41 (edited 03/23/2007 @ 08:07)
by traceyae Accepted solution
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 Accepted solution
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 Accepted solution
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 Accepted solution
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 Accepted solution
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 Accepted solution
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 Accepted solution
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 Accepted solution
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 Accepted solution
by shellbott Accepted solution
Edited by moderator
Message was edited by: beth.blakely@...
by bschaettle Accepted solution
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.
Trending Storage posts
Sort by Trending