Month Number: Mid([Date],4,2)

I also have the SQL that goes with the query:

SELECT Mid([Date],4,2) AS [Month Number], Count(tblOrders.Order_Number) AS CountOfOrders, Sum(tblBikes.Price) AS SumOfPrice, Format([Date],"mmmm") AS [Month Name]
FROM tblOrders INNER JOIN (tblBikes INNER JOIN [Order/Product] ON tblBikes.Model_Number = [Order/Product].Model_Number) ON tblOrders.Order_Number = [Order/Product].Order_Number
GROUP BY Mid([Date],4,2), Format([Date],"mmmm");

:confused:

It is extracting the Month number from todays date, the format of the date is UK eg 31/12/2006

Brian

Month Number: Mid([Date],4,2)

The calcuated field Month Number will display data from the field [Date] commencing with the 4th character from the left and two characters along. Or to put it another way the Mid function is saying.......extract 2 characters from the field [Date] and starting with the 4th character from the left.

Thanks Brian,

Would you mind if i asked you how you know that? And what do the "4" and the "2" represent?

rr,

Month Number: Mid([Date],4,2)

Is an attempt to return the month for your Date field in your table.
There are a couple of problems with it:

First, Date is a RESERVED word in Access, you should choose another
name for your date field. Otherwise, unpredictable bad things can
happen.

Second, the above assumes that the month is a two-digit value. Is your
date field really defined as a date field, or is it a string. If it is
a date datatype, you should use something like --> Format([YourDate], "mm") or
use the DatePart function.

Last of all the --> Format([Date],"mmmm")
references in the Group By clause seems to serve no purpose.

hth,
Wayne

The 4 is the stating point, and the 2 the number of characters, Date is the function returning todays date.
May I suggest that you read ACCESS help on Right, Left, Mid. they are in VBA help so you need to goto module, new and this will open the vba edior and you can select help. I'm sure there is a quicker way but I cannot remember it.

Brian

OOPs hadn't noticed the[] round date :o well spotted Wayne.

Thanks for all your replys guys that helps me make more sense of it.