Why isn't this returning the quarter number?

[GwenH]

I tried using Format([fieldName],"q") on a date and time field. It still
returned the date and time in the query. Then I switched to datePart. Now
it's telling me I have an extra ) in the expression. Why isn't the following
code working? (You probably only need to look at the first line and a half,
but I'm including it all just in case.)

SELECT DatePart("q",[purchase-date]) AS quarterNumber,
DatePart("q",[purchase-date]) AS monthNumber, amazonOrders.[purchase-date],
amazonOrders.[ship-state], amazonOrders.[item-price],
amazonOrders.[item-tax], amazonOrders.[shipping-price],
amazonOrders.[shipping-tax], [item-price]+[shipping-price] AS [Total Sale],
[item-tax]+[shipping-tax] AS [Total Tax]
FROM amazonOrders
WHERE (((amazonOrders.[ship-state]) Like "F*")) OR
(((amazonOrders.[item-tax])>0))
ORDER BY Format([amazonOrders].[purchase-date],"q") DESC ,
Format([amazonOrders].[purchase-date],"m") DESC;

Many thanks!
Gwen Harrison

 

[KARL DEWEY]

What do you get with this?
SELECT TOP 25 Format([amazonOrders].[purchase-date],"q") AS quarterNumber,
Format([amazonOrders].[purchase-date],"m") MonthNumber
FROM [amazonOrders];

 

[Tom van Stiphout]

On Tue, 19 Jan 2010 19:28:01 -0800, GwenH

That seems hard to believe. Try this query:
SELECT DatePart("q",[purchase-date]) AS quarterNumber
FROM amazonOrders
Doesn't that return numbers between 1 and 4?

Also, in the Immediate window, enter:
?datepart("q", Date)
Doesn't that return 1?

-Tom.
Microsoft Access MVP

 

[GwenH]

Okay, I figured out what was wrong. My date, which along with all my other
data is imported from an Excel spreadsheet, was text. So, I came up with the
following to pull the month, date, and year out and then format it as a date.

purchaseDate:
DateSerial(Left([purchase-date],4),Mid([purchase-date],6,2),Mid([purchase-date],9,2))

Now I am able to pull the quarter and month numbers out of the date using
Format([purchaseDate],"q") and Format([purchaseDate],"m").

Many thanks,
Gwen H