Week format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a query that will count the total Work Orders by Week and I have
Chart for that: y- count of WO and x- Week, Year.
In the Query I have:
WO# - Count
Week: Format([ReportedDate],â€wwâ€â€, “â€yyyyâ€)
It’s shows like: 6, 2007 20,2007 ….
It is working, but the problem is that access is showing week 20 before 6
(it is recognizing 20 like smaller then 6 or 7).
So in the chart the weeks are: 20,2007 6,2007 7,2007…
What am I doing wrong?

Thanks for the help.
 
Hi,

The field is sorted as text, so 20 come before 6 becuase it's taking the
first chr (6>2) as the first sort and then it moving to the second one.

If you wnt it to sort by year and then by month, change the sort

Select * From TableNAme
Order By Year([ReportedDate]) , Val(Format([ReportedDate],â€wwâ€))
 
Ofer, thank you very much for the quick answer.
The Val(Format([ReportedDate],â€wwâ€)) is working, but I can't figure out the
Order By part.

Thansk again,
(תודה)

Ofer Cohen said:
Hi,

The field is sorted as text, so 20 come before 6 becuase it's taking the
first chr (6>2) as the first sort and then it moving to the second one.

If you wnt it to sort by year and then by month, change the sort

Select * From TableNAme
Order By Year([ReportedDate]) , Val(Format([ReportedDate],â€wwâ€))

--
Good Luck
BS"D


Anna said:
I created a query that will count the total Work Orders by Week and I have
Chart for that: y- count of WO and x- Week, Year.
In the Query I have:
WO# - Count
Week: Format([ReportedDate],â€wwâ€â€, “â€yyyyâ€)
It’s shows like: 6, 2007 20,2007 ….
It is working, but the problem is that access is showing week 20 before 6
(it is recognizing 20 like smaller then 6 or 7).
So in the chart the weeks are: 20,2007 6,2007 7,2007…
What am I doing wrong?

Thanks for the help.
Click to expand...
Click to expand...
 
The Val(Format([ReportedDate],”ww”)) is working, but I can't figure out the
Order By part.
Click to expand...

Just Order By [ReportedDate] itself.

John W. Vinson [MVP]
 
I know that it should be very easy, but I can't get it.
I have to have the year with the week in the same field.

Thank you for the help.

John W. Vinson said:
The Val(Format([ReportedDate],â€wwâ€)) is working, but I can't figure out the
Order By part.
Click to expand...

Just Order By [ReportedDate] itself.

John W. Vinson [MVP]
Click to expand...
 
שלו×

John had a good point, you can order the query by the Date instead of
breaking it into two as I suggested.

Not sure if that what you are asking for, but:

In the "Order By" you define the order of the sort in the query

Order By FieldName Asc ' or without Asc
will order the records by this field name from buttom to top

Order By FieldName Dsc
will order the records by this field name from top to buttom

You van Order By by more then one field
Order By FieldName Asc, FieldName2 Desc

If you need help with the query, can you post your SQL?

(בבקשה)

--
Good Luck
BS"D


Anna said:
I know that it should be very easy, but I can't get it.
I have to have the year with the week in the same field.

Thank you for the help.

John W. Vinson said:
The Val(Format([ReportedDate],â€wwâ€)) is working, but I can't figure out the
Order By part.
Click to expand...

Just Order By [ReportedDate] itself.

John W. Vinson [MVP]
Click to expand...
Click to expand...
 
This is my query:
SELECT Count(qryWorkOrders.[WO#]) AS [CountOfWO#],
Val(Format([ReportedDate],"ww")) AS Week
FROM [qryAllT&DWorkOrders-SumComp], qryWorkOrders
GROUP BY Val(Format([ReportedDate],"ww")), qryWorkOrders.ReportedDate,
qryWorkOrders.Status
HAVING (((qryWorkOrders.Status) Like "COMP*" Or (qryWorkOrders.Status) Like
"CON*" Or (qryWorkOrders.Status) Like "CLOSE*"));

I neet to Format the ReportedDate as "week, year" and I don't know where to
add the yyyy in the Val(Format....)

Thanks again,
Anna
(המון תודה)

Ofer Cohen said:
שלו×

John had a good point, you can order the query by the Date instead of
breaking it into two as I suggested.

Not sure if that what you are asking for, but:

In the "Order By" you define the order of the sort in the query

Order By FieldName Asc ' or without Asc
will order the records by this field name from buttom to top

Order By FieldName Dsc
will order the records by this field name from top to buttom

You van Order By by more then one field
Order By FieldName Asc, FieldName2 Desc

If you need help with the query, can you post your SQL?

(בבקשה)

--
Good Luck
BS"D


Anna said:
I know that it should be very easy, but I can't get it.
I have to have the year with the week in the same field.

Thank you for the help.

John W. Vinson said:
The Val(Format([ReportedDate],â€wwâ€)) is working, but I can't figure out the
Order By part.

Just Order By [ReportedDate] itself.

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
Try

SELECT Count(qryWorkOrders.[WO#]) AS [CountOfWO#],
Format(Date,"ww-yyyy") AS WeekAndYear
FROM [qryAllT&DWorkOrders-SumComp], qryWorkOrders
GROUP BY Val(Format([ReportedDate],"ww")), qryWorkOrders.ReportedDate,
qryWorkOrders.Status
HAVING (((qryWorkOrders.Status) Like "COMP*" Or (qryWorkOrders.Status) Like
"CON*" Or (qryWorkOrders.Status) Like "CLOSE*"))
Order By ReportedDate

Copy and paste it into a query, not the Order By is by the date field as
John suggested

--
Good Luck
BS"D


Anna said:
This is my query:
SELECT Count(qryWorkOrders.[WO#]) AS [CountOfWO#],
Val(Format([ReportedDate],"ww")) AS Week
FROM [qryAllT&DWorkOrders-SumComp], qryWorkOrders
GROUP BY Val(Format([ReportedDate],"ww")), qryWorkOrders.ReportedDate,
qryWorkOrders.Status
HAVING (((qryWorkOrders.Status) Like "COMP*" Or (qryWorkOrders.Status) Like
"CON*" Or (qryWorkOrders.Status) Like "CLOSE*"));

I neet to Format the ReportedDate as "week, year" and I don't know where to
add the yyyy in the Val(Format....)

Thanks again,
Anna
(המון תודה)

Ofer Cohen said:
שלו×

John had a good point, you can order the query by the Date instead of
breaking it into two as I suggested.

Not sure if that what you are asking for, but:

In the "Order By" you define the order of the sort in the query

Order By FieldName Asc ' or without Asc
will order the records by this field name from buttom to top

Order By FieldName Dsc
will order the records by this field name from top to buttom

You van Order By by more then one field
Order By FieldName Asc, FieldName2 Desc

If you need help with the query, can you post your SQL?

(בבקשה)

--
Good Luck
BS"D


Anna said:
I know that it should be very easy, but I can't get it.
I have to have the year with the week in the same field.

Thank you for the help.

:

The Val(Format([ReportedDate],â€wwâ€)) is working, but I can't figure out the
Order By part.

Just Order By [ReportedDate] itself.

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
This is the result:
CountOfWO# WeekAndYear Expr1002
1 26-1905 6
what I need is:
CountOfWO# Week
1 6,2007
1 6,2007
1 6,2007
1 6,2007
1 7,2007
1 24,2007
That on the chart I'll see all the weeks and count of the WO for this week.
This is again my SQL:
SELECT Count(qryWorkOrders.[WO#]) AS [CountOfWO#],
Val(Format([ReportedDate],"ww")) AS Week
FROM qryWorkOrders
GROUP BY Val(Format([ReportedDate],"ww")), qryWorkOrders.ReportedDate,
qryWorkOrders.Status
HAVING (((qryWorkOrders.Status) Like "COMP*" Or (qryWorkOrders.Status) Like
"CON*" Or (qryWorkOrders.Status) Like "CLOSE*"))
ORDER BY qryWorkOrders.ReportedDate;

Thank you very much for the help!!

Ofer Cohen said:
Try

SELECT Count(qryWorkOrders.[WO#]) AS [CountOfWO#],
Format(Date,"ww-yyyy") AS WeekAndYear
FROM [qryAllT&DWorkOrders-SumComp], qryWorkOrders
GROUP BY Val(Format([ReportedDate],"ww")), qryWorkOrders.ReportedDate,
qryWorkOrders.Status
HAVING (((qryWorkOrders.Status) Like "COMP*" Or (qryWorkOrders.Status) Like
"CON*" Or (qryWorkOrders.Status) Like "CLOSE*"))
Order By ReportedDate

Copy and paste it into a query, not the Order By is by the date field as
John suggested

--
Good Luck
BS"D


Anna said:
This is my query:
SELECT Count(qryWorkOrders.[WO#]) AS [CountOfWO#],
Val(Format([ReportedDate],"ww")) AS Week
FROM [qryAllT&DWorkOrders-SumComp], qryWorkOrders
GROUP BY Val(Format([ReportedDate],"ww")), qryWorkOrders.ReportedDate,
qryWorkOrders.Status
HAVING (((qryWorkOrders.Status) Like "COMP*" Or (qryWorkOrders.Status) Like
"CON*" Or (qryWorkOrders.Status) Like "CLOSE*"));

I neet to Format the ReportedDate as "week, year" and I don't know where to
add the yyyy in the Val(Format....)

Thanks again,
Anna
(המון תודה)

Ofer Cohen said:
שלו×

John had a good point, you can order the query by the Date instead of
breaking it into two as I suggested.

Not sure if that what you are asking for, but:

In the "Order By" you define the order of the sort in the query

Order By FieldName Asc ' or without Asc
will order the records by this field name from buttom to top

Order By FieldName Dsc
will order the records by this field name from top to buttom

You van Order By by more then one field
Order By FieldName Asc, FieldName2 Desc

If you need help with the query, can you post your SQL?

(בבקשה)

--
Good Luck
BS"D


:

I know that it should be very easy, but I can't get it.
I have to have the year with the week in the same field.

Thank you for the help.

:

The Val(Format([ReportedDate],â€wwâ€)) is working, but I can't figure out the
Order By part.

Just Order By [ReportedDate] itself.

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
Ofer,
I don't know what I did, but it's working like a charm!!!

Thanks you so much!!!
Anna


Anna said:
This is the result:
CountOfWO# WeekAndYear Expr1002
1 26-1905 6
what I need is:
CountOfWO# Week
1 6,2007
1 6,2007
1 6,2007
1 6,2007
1 7,2007
1 24,2007
That on the chart I'll see all the weeks and count of the WO for this week.
This is again my SQL:
SELECT Count(qryWorkOrders.[WO#]) AS [CountOfWO#],
Val(Format([ReportedDate],"ww")) AS Week
FROM qryWorkOrders
GROUP BY Val(Format([ReportedDate],"ww")), qryWorkOrders.ReportedDate,
qryWorkOrders.Status
HAVING (((qryWorkOrders.Status) Like "COMP*" Or (qryWorkOrders.Status) Like
"CON*" Or (qryWorkOrders.Status) Like "CLOSE*"))
ORDER BY qryWorkOrders.ReportedDate;

Thank you very much for the help!!

Ofer Cohen said:
Try

SELECT Count(qryWorkOrders.[WO#]) AS [CountOfWO#],
Format(Date,"ww-yyyy") AS WeekAndYear
FROM [qryAllT&DWorkOrders-SumComp], qryWorkOrders
GROUP BY Val(Format([ReportedDate],"ww")), qryWorkOrders.ReportedDate,
qryWorkOrders.Status
HAVING (((qryWorkOrders.Status) Like "COMP*" Or (qryWorkOrders.Status) Like
"CON*" Or (qryWorkOrders.Status) Like "CLOSE*"))
Order By ReportedDate

Copy and paste it into a query, not the Order By is by the date field as
John suggested

--
Good Luck
BS"D


Anna said:
This is my query:
SELECT Count(qryWorkOrders.[WO#]) AS [CountOfWO#],
Val(Format([ReportedDate],"ww")) AS Week
FROM [qryAllT&DWorkOrders-SumComp], qryWorkOrders
GROUP BY Val(Format([ReportedDate],"ww")), qryWorkOrders.ReportedDate,
qryWorkOrders.Status
HAVING (((qryWorkOrders.Status) Like "COMP*" Or (qryWorkOrders.Status) Like
"CON*" Or (qryWorkOrders.Status) Like "CLOSE*"));

I neet to Format the ReportedDate as "week, year" and I don't know where to
add the yyyy in the Val(Format....)

Thanks again,
Anna
(המון תודה)

:

שלו×

John had a good point, you can order the query by the Date instead of
breaking it into two as I suggested.

Not sure if that what you are asking for, but:

In the "Order By" you define the order of the sort in the query

Order By FieldName Asc ' or without Asc
will order the records by this field name from buttom to top

Order By FieldName Dsc
will order the records by this field name from top to buttom

You van Order By by more then one field
Order By FieldName Asc, FieldName2 Desc

If you need help with the query, can you post your SQL?

(בבקשה)

--
Good Luck
BS"D


:

I know that it should be very easy, but I can't get it.
I have to have the year with the week in the same field.

Thank you for the help.

:

The Val(Format([ReportedDate],â€wwâ€)) is working, but I can't figure out the
Order By part.

Just Order By [ReportedDate] itself.

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
×ין בעד מה
בהצלחה בהמשך

עופר

--
Good Luck
BS"D


Anna said:
Ofer,
I don't know what I did, but it's working like a charm!!!

Thanks you so much!!!
Anna


Anna said:
This is the result:
CountOfWO# WeekAndYear Expr1002
1 26-1905 6
what I need is:
CountOfWO# Week
1 6,2007
1 6,2007
1 6,2007
1 6,2007
1 7,2007
1 24,2007
That on the chart I'll see all the weeks and count of the WO for this week.
This is again my SQL:
SELECT Count(qryWorkOrders.[WO#]) AS [CountOfWO#],
Val(Format([ReportedDate],"ww")) AS Week
FROM qryWorkOrders
GROUP BY Val(Format([ReportedDate],"ww")), qryWorkOrders.ReportedDate,
qryWorkOrders.Status
HAVING (((qryWorkOrders.Status) Like "COMP*" Or (qryWorkOrders.Status) Like
"CON*" Or (qryWorkOrders.Status) Like "CLOSE*"))
ORDER BY qryWorkOrders.ReportedDate;

Thank you very much for the help!!

Ofer Cohen said:
Try

SELECT Count(qryWorkOrders.[WO#]) AS [CountOfWO#],
Format(Date,"ww-yyyy") AS WeekAndYear
FROM [qryAllT&DWorkOrders-SumComp], qryWorkOrders
GROUP BY Val(Format([ReportedDate],"ww")), qryWorkOrders.ReportedDate,
qryWorkOrders.Status
HAVING (((qryWorkOrders.Status) Like "COMP*" Or (qryWorkOrders.Status) Like
"CON*" Or (qryWorkOrders.Status) Like "CLOSE*"))
Order By ReportedDate

Copy and paste it into a query, not the Order By is by the date field as
John suggested

--
Good Luck
BS"D


:

This is my query:
SELECT Count(qryWorkOrders.[WO#]) AS [CountOfWO#],
Val(Format([ReportedDate],"ww")) AS Week
FROM [qryAllT&DWorkOrders-SumComp], qryWorkOrders
GROUP BY Val(Format([ReportedDate],"ww")), qryWorkOrders.ReportedDate,
qryWorkOrders.Status
HAVING (((qryWorkOrders.Status) Like "COMP*" Or (qryWorkOrders.Status) Like
"CON*" Or (qryWorkOrders.Status) Like "CLOSE*"));

I neet to Format the ReportedDate as "week, year" and I don't know where to
add the yyyy in the Val(Format....)

Thanks again,
Anna
(המון תודה)

:

שלו×

John had a good point, you can order the query by the Date instead of
breaking it into two as I suggested.

Not sure if that what you are asking for, but:

In the "Order By" you define the order of the sort in the query

Order By FieldName Asc ' or without Asc
will order the records by this field name from buttom to top

Order By FieldName Dsc
will order the records by this field name from top to buttom

You van Order By by more then one field
Order By FieldName Asc, FieldName2 Desc

If you need help with the query, can you post your SQL?

(בבקשה)

--
Good Luck
BS"D


:

I know that it should be very easy, but I can't get it.
I have to have the year with the week in the same field.

Thank you for the help.

:

The Val(Format([ReportedDate],â€wwâ€)) is working, but I can't figure out the
Order By part.

Just Order By [ReportedDate] itself.

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum query 5
How to Union Crosstabs... 1
Week starts on Friday 2
Need Sum of Weeks Between 2 Dates 1
Convert One Column Data to Multiple Columns Data 4
Week, monthly, quarterly 2
Crosstab Query by Week with Dates as Headers 5
Extract data from previous week, previous two weeks, etc. 2

Back
Top