Re: Query Help Please! Is a Weekly data format Possible?

Dear Klatuu:

Yes, and DatePart("ww", "11/8/2006") works fine. Depends on the format of
the text. As I said,

"Many string formats for dates can be recognized automatically."

There are a number of string formats that work, and some that don't.

Tom Ellison


"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CCD9960D-F32F-4987-8146-163962B7EA55@xxxxxxxxxxxxxxxx
Thanks for the reminder, Tom. I had forgotten about the type conversion
for
the Like operator. It is true that neither month nor day can be four
digits.
As long is the application is using USA date formatting, only the year
would
match. I am not positive, but I think it is possible, that using Euro
date
formatting, 2005 could possibly match May 20.
As to the Datepart, it required delimiters. datepart("m","05252006") will
return and error 13 (Type Mismatch)

"Tom Ellison" wrote:

Dear Klatuu:

It is not necessarily true that the query shows that the Management Date
must be a string. The statement:

(((Management.Date) Like "*2005*"));

works for strings or dates the same. The Like operatore causes its
operands
to be converted to strings, even if it is a date. Since neither day nor
month can be 4 digits, this can only match the year portion.

Also, the DatePart function I used will work on a date that is in string
form, within limits. Many string formats for dates can be recognized
automatically.

I suggest that there is not sufficient evidence to conclude that the
original post can only mean it is a string. And, if it is, this does not
mean my solution won't work.

I was fully aware of this as I posted. But thanks for checking up on me.

Tom Ellison



"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C2186EF7-31B6-4AD0-99AF-F957A1191021@xxxxxxxxxxxxxxxx
That should do if Management.Date is a date/time field. Based on the
OP,
It
appears to be a text field. That is why I asked for the format. It
will
take the DateSerial function to make it into a date.
Also, I am suprised it is working at all with a field named Date. It
should
be renamed or in brackets. It will have to be in brackets to use the
date
function, or I think Access is going to get really confused.

"Tom Ellison" wrote:

Dear Marie:

By "weekly" format do you mean having one row per week instead of one
row
per day? If so:

SELECT DatePart("ww", Management.Date), Management.queue,
Sum(Management.RECEIVED) AS SumOfRECEIVED
FROM Management
INNER JOIN Queue
ON Management.queue = Queue.Queue
WHERE [Queue.Group] Like "*exl*"
GROUP BY DatePart("ww", Management.Date), Management.queue
HAVING DatePart("yyyy", [Management.Date] = 2005

Please let me know if this helped, and if I can be of any other
assistance.

The above is for a Sunday to Saturday week. It can be set to another
basis.
See the online help for DatePart.

Tom Ellison


"Marie James via AccessMonster.com" <u20313@uwe> wrote in message
news:5e16f50071b12@xxxxxx
Hello! I have a query built like this:
SELECT Management.Date, Management.queue, Sum(Management.RECEIVED)
AS
SumOfRECEIVED
FROM Management INNER JOIN Queue ON Management.queue = Queue.Queue
WHERE (((Queue.Group) Like "*exl*"))
GROUP BY Management.Date, Management.queue
HAVING (((Management.Date) Like "*2005*"));
and this gives me the results I need everyday for the entire 2005
year.
Is there a way to have this query group the data in a weekly format
for
the
entire 2005 year?
Any assistance is Greatly Appreciated!

Kind Regards,

Marie James

--
Message posted via http://www.accessmonster.com








.

Relevant Pages