convert week of 06-35 into "Week of 8/27/2006"

[Guest]

I'm using DatePart function to convert date into a week number and performing
various calculation for each week of the year and that works fine.

Format(DatePart("yyyy",[Date Report Due]),"0000") & "-" &
Format(DatePart("ww",[Date Report Due]),"00")

In the next field I would like to display the corresponding week of date as
"mm-dd/yyyy" that users will see that week 35 is week of 8/27/2006. Please
help. Thanks.

 

[Graham R Seach]

Martina,

Am I missing something?
Format([Date Report Due],"mm-dd/yyyy")

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

I din't ask correctly. I don't think this function is going to convert [Date
Report Due] of 8/29/2006 into week of 8/27/2006. For instance I would like to
show
# of reports due
week of 8/27/2006 2
week of 9/3/2006 45
week of 9/10/2006 10

currently my query i showing 2006-35 instedad of week of 8/27/2006.

Martina,

Am I missing something?
Format([Date Report Due],"mm-dd/yyyy")

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I'm using DatePart function to convert date into a week number and
performing
various calculation for each week of the year and that works fine.

Format(DatePart("yyyy",[Date Report Due]),"0000") & "-" &
Format(DatePart("ww",[Date Report Due]),"00")

In the next field I would like to display the corresponding week of date
as
"mm-dd/yyyy" that users will see that week 35 is week of 8/27/2006. Please
help. Thanks.

 

[John Spencer]

One way would be to use the DateAdd function against your calculated value.

DateAdd("ww",Val(Mid(YearWeek,6)-1,DateSerial(Val(YearWeek),1,1))

Another would be to adjust the actual date with the dateAdd function

DateAdd("d",1-Weekday([Date Report Due]),[Date Report Due])

I din't ask correctly. I don't think this function is going to convert
[Date
Report Due] of 8/29/2006 into week of 8/27/2006. For instance I would like
to
show
# of reports due
week of 8/27/2006 2
week of 9/3/2006 45
week of 9/10/2006 10

currently my query i showing 2006-35 instedad of week of 8/27/2006.

Martina,

Am I missing something?
Format([Date Report Due],"mm-dd/yyyy")

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I'm using DatePart function to convert date into a week number and
performing
various calculation for each week of the year and that works fine.

Format(DatePart("yyyy",[Date Report Due]),"0000") & "-" &
Format(DatePart("ww",[Date Report Due]),"00")

In the next field I would like to display the corresponding week of
date
as
"mm-dd/yyyy" that users will see that week 35 is week of 8/27/2006.
Please
help. Thanks.

 

[Guest]

It works! Thank you so much!

One way would be to use the DateAdd function against your calculated value.

DateAdd("ww",Val(Mid(YearWeek,6)-1,DateSerial(Val(YearWeek),1,1))

Another would be to adjust the actual date with the dateAdd function

DateAdd("d",1-Weekday([Date Report Due]),[Date Report Due])

I din't ask correctly. I don't think this function is going to convert
[Date
Report Due] of 8/29/2006 into week of 8/27/2006. For instance I would like
to
show
# of reports due
week of 8/27/2006 2
week of 9/3/2006 45
week of 9/10/2006 10

currently my query i showing 2006-35 instedad of week of 8/27/2006.

Martina,

Am I missing something?
Format([Date Report Due],"mm-dd/yyyy")

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I'm using DatePart function to convert date into a week number and
performing
various calculation for each week of the year and that works fine.

Format(DatePart("yyyy",[Date Report Due]),"0000") & "-" &
Format(DatePart("ww",[Date Report Due]),"00")

In the next field I would like to display the corresponding week of
date
as
"mm-dd/yyyy" that users will see that week 35 is week of 8/27/2006.
Please
help. Thanks.

 

[Guest]

John, addweek date function converted by week number correctly.
I’m using this function in the “Week of Finding closed†field in additional
sub-queries and performing various calculations such as counting Findings by
week of finding closed.

If I group my data by field “Week of Finding closedâ€, which is formatted the
way you told me, I get error message “Data Type mismatch in criteria
expressionâ€. If I group my data by field “Week Finding Closedâ€, which is
formatted as 06-35 (I don’t want to use this format because people don’t know
what week is 35), the query runs fine. Any idea how can I perform
calculations by using the date format you suggested?

It works! Thank you so much!

One way would be to use the DateAdd function against your calculated value.

DateAdd("ww",Val(Mid(YearWeek,6)-1,DateSerial(Val(YearWeek),1,1))

Another would be to adjust the actual date with the dateAdd function

DateAdd("d",1-Weekday([Date Report Due]),[Date Report Due])

I din't ask correctly. I don't think this function is going to convert
[Date
Report Due] of 8/29/2006 into week of 8/27/2006. For instance I would like
to
show
# of reports due
week of 8/27/2006 2
week of 9/3/2006 45
week of 9/10/2006 10

currently my query i showing 2006-35 instedad of week of 8/27/2006.


:

Martina,

Am I missing something?
Format([Date Report Due],"mm-dd/yyyy")

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I'm using DatePart function to convert date into a week number and
performing
various calculation for each week of the year and that works fine.

Format(DatePart("yyyy",[Date Report Due]),"0000") & "-" &
Format(DatePart("ww",[Date Report Due]),"00")

In the next field I would like to display the corresponding week of
date
as
"mm-dd/yyyy" that users will see that week 35 is week of 8/27/2006.
Please
help. Thanks.

 

[John Spencer]

Is [Date of Report Due] ever null (blank)? That can cause a problem.

Can you post the SQL text (Menu: View: SQL) of the query that works and if you
can the one that is failing?

You can try ti apply criteria to one calculated field and display the other
calculated field.

Personnally, I would use the second alternative and group by it.

DateAdd("d",1-Weekday([Date Report Due]),[Date Report Due])

You will have to decide what you want to do with records where [Date Report Due]
is null. Eliminate them? Assign a bogus date to them? Return null by testing
with an IIF statement?

IIF([Date Report Due is Null, Null, DateAdd("d",1-Weekday([Date Report
Due]),[Date Report Due]))

John, addweek date function converted by week number correctly.
I’m using this function in the “Week of Finding closedâ€* field in additional
sub-queries and performing various calculations such as counting Findings by
week of finding closed.

If I group my data by field “Week of Finding closedâ€*, which is formatted the
way you told me, I get error message “Data Type mismatch in criteria
expressionâ€*. If I group my data by field “Week Finding Closedâ€*, which is
formatted as 06-35 (I don’t want to use this format because people don’t know
what week is 35), the query runs fine. Any idea how can I perform
calculations by using the date format you suggested?

It works! Thank you so much!

One way would be to use the DateAdd function against your calculated value.

DateAdd("ww",Val(Mid(YearWeek,6)-1,DateSerial(Val(YearWeek),1,1))

Another would be to adjust the actual date with the dateAdd function

DateAdd("d",1-Weekday([Date Report Due]),[Date Report Due])


I din't ask correctly. I don't think this function is going to convert
[Date
Report Due] of 8/29/2006 into week of 8/27/2006. For instance I would like
to
show
# of reports due
week of 8/27/2006 2
week of 9/3/2006 45
week of 9/10/2006 10

currently my query i showing 2006-35 instedad of week of 8/27/2006.


:

Martina,

Am I missing something?
Format([Date Report Due],"mm-dd/yyyy")

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I'm using DatePart function to convert date into a week number and
performing
various calculation for each week of the year and that works fine.

Format(DatePart("yyyy",[Date Report Due]),"0000") & "-" &
Format(DatePart("ww",[Date Report Due]),"00")

In the next field I would like to display the corresponding week of
date
as
"mm-dd/yyyy" that users will see that week 35 is week of 8/27/2006.
Please
help. Thanks.

 

[Guest]

You're correct. The blanks were causing the calculation to not run. It is
interesting that the blanks didn't matter when I was using the "yy-ww"
format.

Thank you so much!

Is [Date of Report Due] ever null (blank)? That can cause a problem.

Can you post the SQL text (Menu: View: SQL) of the query that works and if you
can the one that is failing?

You can try ti apply criteria to one calculated field and display the other
calculated field.

Personnally, I would use the second alternative and group by it.

DateAdd("d",1-Weekday([Date Report Due]),[Date Report Due])

You will have to decide what you want to do with records where [Date Report Due]
is null. Eliminate them? Assign a bogus date to them? Return null by testing
with an IIF statement?

IIF([Date Report Due is Null, Null, DateAdd("d",1-Weekday([Date Report
Due]),[Date Report Due]))

John, addweek date function converted by week number correctly.
I’m using this function in the “Week of Finding closedâ€* field in additional
sub-queries and performing various calculations such as counting Findings by
week of finding closed.

If I group my data by field “Week of Finding closedâ€*, which is formatted the
way you told me, I get error message “Data Type mismatch in criteria
expressionâ€*. If I group my data by field “Week Finding Closedâ€*, which is
formatted as 06-35 (I don’t want to use this format because people don’t know
what week is 35), the query runs fine. Any idea how can I perform
calculations by using the date format you suggested?

It works! Thank you so much!

:

One way would be to use the DateAdd function against your calculated value.

DateAdd("ww",Val(Mid(YearWeek,6)-1,DateSerial(Val(YearWeek),1,1))

Another would be to adjust the actual date with the dateAdd function

DateAdd("d",1-Weekday([Date Report Due]),[Date Report Due])


I din't ask correctly. I don't think this function is going to convert
[Date
Report Due] of 8/29/2006 into week of 8/27/2006. For instance I would like
to
show
# of reports due
week of 8/27/2006 2
week of 9/3/2006 45
week of 9/10/2006 10

currently my query i showing 2006-35 instedad of week of 8/27/2006.


:

Martina,

Am I missing something?
Format([Date Report Due],"mm-dd/yyyy")

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I'm using DatePart function to convert date into a week number and
performing
various calculation for each week of the year and that works fine.

Format(DatePart("yyyy",[Date Report Due]),"0000") & "-" &
Format(DatePart("ww",[Date Report Due]),"00")

In the next field I would like to display the corresponding week of
date
as
"mm-dd/yyyy" that users will see that week 35 is week of 8/27/2006.
Please
help. Thanks.