Convert week # into date of the first day of week in SQL

[Guest]

I have created a crosstab query to lay out a report with the days of the week
as columns and the week number as rows. I would like to convert the week
number to the date of the first day of that week. How would you write that
code?

 

[Wayne Morgan]

See if this formula will work for you,

DateAdd("d",((WeekOfYear-1)*7) - Weekday(#1/1/2004#) + 1, #1/1/2004#)

Where 1/1/2004 would be the first day of the year in question.

 

[Guest]

This formula works only for the stated year 2004. Is there a way to use this
in a database with multiyears?

 

[Wayne Morgan]

I believe so. Try,

DateAdd("d",((WeekOfYear-1)*7) - Weekday(DateSerial(YearRequested,1,1)) + 1,
DateSerial(YearRequested,1,1))

 

[Guest]

This is the formula I have typed. It has invaliad syntax, but I do not see
it. Please advise. Will this formula need to be changed as years change? Is
there a way to unversalize it so it will be maintenance free?

=DateAdd("d",(([Week
No]-1)*7)-Weekday(#1/1/2003#([2004],1,1))+1,#1/1/2003#([2004],1,1))

 

[Douglas J. Steele]

The invalid syntax is at the end: #1/1/2003#([2004],1,1)) What were you
hoping that meant?

Yes, it will need to be changed every year, since you've hard-coded the
years into it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This is the formula I have typed. It has invaliad syntax, but I do not see
it. Please advise. Will this formula need to be changed as years change? Is
there a way to unversalize it so it will be maintenance free?

=DateAdd("d",(([Week
No]-1)*7)-Weekday(#1/1/2003#([2004],1,1))+1,#1/1/2003#([2004],1,1))

I believe so. Try,

DateAdd("d",((WeekOfYear-1)*7) - Weekday(DateSerial(YearRequested,1,1)) + 1,
DateSerial(YearRequested,1,1))

 

[Guest]

I was using the formula Wayne sent me and I am not sure why it is included.

Is there a way to write this so the years are not hard coded?

The invalid syntax is at the end: #1/1/2003#([2004],1,1)) What were you
hoping that meant?

Yes, it will need to be changed every year, since you've hard-coded the
years into it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This is the formula I have typed. It has invaliad syntax, but I do not see
it. Please advise. Will this formula need to be changed as years change? Is
there a way to unversalize it so it will be maintenance free?

=DateAdd("d",(([Week
No]-1)*7)-Weekday(#1/1/2003#([2004],1,1))+1,#1/1/2003#([2004],1,1))

I believe so. Try,

DateAdd("d",((WeekOfYear-1)*7) - Weekday(DateSerial(YearRequested,1,1)) + 1,
DateSerial(YearRequested,1,1))

--
Wayne Morgan
MS Access MVP


This formula works only for the stated year 2004. Is there a way to use
this
in a database with multiyears?

:

See if this formula will work for you,

DateAdd("d",((WeekOfYear-1)*7) - Weekday(#1/1/2004#) + 1, #1/1/2004#)

Where 1/1/2004 would be the first day of the year in question.

 

[Douglas J. Steele]

You're not using what Wayne said to.

He posted:

DateAdd("d",((WeekOfYear-1)*7) - Weekday(DateSerial(YearRequested,1,1)) + 1,
DateSerial(YearRequested,1,1))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I was using the formula Wayne sent me and I am not sure why it is included.

Is there a way to write this so the years are not hard coded?

The invalid syntax is at the end: #1/1/2003#([2004],1,1)) What were you
hoping that meant?

Yes, it will need to be changed every year, since you've hard-coded the
years into it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This is the formula I have typed. It has invaliad syntax, but I do not see
it. Please advise. Will this formula need to be changed as years

change?
Is

there a way to unversalize it so it will be maintenance free?

=DateAdd("d",(([Week
No]-1)*7)-Weekday(#1/1/2003#([2004],1,1))+1,#1/1/2003#([2004],1,1))

:

I believe so. Try,

DateAdd("d",((WeekOfYear-1)*7) -

Weekday(DateSerial(YearRequested,1,1))
+ 1,

DateSerial(YearRequested,1,1))

--
Wayne Morgan
MS Access MVP


This formula works only for the stated year 2004. Is there a way

to
use

this
in a database with multiyears?

:

See if this formula will work for you,

DateAdd("d",((WeekOfYear-1)*7) - Weekday(#1/1/2004#) + 1, #1/1/2004#)

Where 1/1/2004 would be the first day of the year in question.

 

[Wayne Morgan]

Tom,

WeekOfYear and YearRequested should be variables, field names, or
parameters. If so, this will make it "dynamic". If you hard code the values
in instead, then you will need to change it every year.

--
Wayne Morgan
MS Access MVP

I was using the formula Wayne sent me and I am not sure why it is included.

Is there a way to write this so the years are not hard coded?

The invalid syntax is at the end: #1/1/2003#([2004],1,1)) What were you
hoping that meant?

Yes, it will need to be changed every year, since you've hard-coded the
years into it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This is the formula I have typed. It has invaliad syntax, but I do not
see
it. Please advise. Will this formula need to be changed as years
change? Is
there a way to unversalize it so it will be maintenance free?

=DateAdd("d",(([Week
No]-1)*7)-Weekday(#1/1/2003#([2004],1,1))+1,#1/1/2003#([2004],1,1))

:

I believe so. Try,

DateAdd("d",((WeekOfYear-1)*7) -
Weekday(DateSerial(YearRequested,1,1)) + 1,
DateSerial(YearRequested,1,1))

--
Wayne Morgan
MS Access MVP


This formula works only for the stated year 2004. Is there a way to use
this
in a database with multiyears?

:

See if this formula will work for you,

DateAdd("d",((WeekOfYear-1)*7) - Weekday(#1/1/2004#) + 1,
#1/1/2004#)

Where 1/1/2004 would be the first day of the year in question.

 

[Guest]

Is there a way where I can use the parameters "Start Date" and an "End Date"
where I enter them in typical date fashion xx/xx/xx in this formula to
extract the information covering any year or years?

Tom,

WeekOfYear and YearRequested should be variables, field names, or
parameters. If so, this will make it "dynamic". If you hard code the values
in instead, then you will need to change it every year.

--
Wayne Morgan
MS Access MVP

I was using the formula Wayne sent me and I am not sure why it is included.

Is there a way to write this so the years are not hard coded?

The invalid syntax is at the end: #1/1/2003#([2004],1,1)) What were you
hoping that meant?

Yes, it will need to be changed every year, since you've hard-coded the
years into it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



This is the formula I have typed. It has invaliad syntax, but I do not
see
it. Please advise. Will this formula need to be changed as years
change?
Is
there a way to unversalize it so it will be maintenance free?

=DateAdd("d",(([Week
No]-1)*7)-Weekday(#1/1/2003#([2004],1,1))+1,#1/1/2003#([2004],1,1))

:

I believe so. Try,

DateAdd("d",((WeekOfYear-1)*7) -
Weekday(DateSerial(YearRequested,1,1))
+ 1,
DateSerial(YearRequested,1,1))

--
Wayne Morgan
MS Access MVP


This formula works only for the stated year 2004. Is there a way to
use
this
in a database with multiyears?

:

See if this formula will work for you,

DateAdd("d",((WeekOfYear-1)*7) - Weekday(#1/1/2004#) + 1,
#1/1/2004#)

Where 1/1/2004 would be the first day of the year in question.

 

[Wayne Morgan]

Probably, but I'd need to know more about what you're trying to do. You
started asking for a date when given a week. Now you want to enter a date or
dates. What do you want the result to be?

 

[Guest]

I have created one table that contains dates and opening and closing figures
for each day. I have created a query and extracted from the date: Month, Week
No, and Weekday Name. I then created a crosstab query to show in the Row
Heading: Month, Week (shown as the date of the first day of that week), and
Sum Total of the figures. The Column Heading is the Weekdays. The Crosstab
query works fine.

However, the problem is when I create a report. The report looks great but
the parameters only give me the year I hard coded. I would like to be able to
enter a typical Start and End date and it disply the information in the
crosstab report by the week.

I tried to send you the crosstab but it will not let me paste it into this
screen. I hope I have given you enough information to help.

Thanks for all your time and effort.

Tom

 

[Wayne Morgan]

There are 3 common ways of getting parameters into a query.

1) Place what looks like a field name, but isn't, in the criteria of the
query. When the name isn't recognized, you'll be prompted for it when the
query is run.

Example:
SELECT tblMyTable.* FROM tblMyTable WHERE tblMyTable.MyField = [Enter a
Value];

2) Have a form with the values you want to use. Refer to the controls on the
form in the criteria.

Example:
SELECT tblMyTable.* FROM tblMyTable WHERE tblMyTable.MyField =
Forms!frmMyFrom!txtMyTextbox;

3) Create the SQL for the query in code and then "rewrite" the query.

Example:
strSQL = "SELECT tblMyTable.* FROM tblMyTable WHERE tblMyTable.MyField =
#1/1/2004#;"
CurrentDb.QueryDefs("qryMyQuery").SQL = strSQL

I hope this helps. I haven't done much work with cross-tab queries, but the
basics for getting the information into them is the same.

 

[Guest]

I understand the procedures you outlined. Let me go back to my orginal
question: Can I convert "Week No" into the date of Sunday of that week? The
formula I was sent and used is =DateAdd("d",(([Week
No]-1)*7)-Weekday(#1/1/2003#+1,#1/1/2003#). This works well expect I have
multiyear dates in my database. I need to extract data that covers two years.
This formula will only allow the hard coded year. Is there another way to
write the formula?

There are 3 common ways of getting parameters into a query.

1) Place what looks like a field name, but isn't, in the criteria of the
query. When the name isn't recognized, you'll be prompted for it when the
query is run.

Example:
SELECT tblMyTable.* FROM tblMyTable WHERE tblMyTable.MyField = [Enter a
Value];

2) Have a form with the values you want to use. Refer to the controls on the
form in the criteria.

Example:
SELECT tblMyTable.* FROM tblMyTable WHERE tblMyTable.MyField =
Forms!frmMyFrom!txtMyTextbox;

3) Create the SQL for the query in code and then "rewrite" the query.

Example:
strSQL = "SELECT tblMyTable.* FROM tblMyTable WHERE tblMyTable.MyField =
#1/1/2004#;"
CurrentDb.QueryDefs("qryMyQuery").SQL = strSQL

I hope this helps. I haven't done much work with cross-tab queries, but the
basics for getting the information into them is the same.

--
Wayne Morgan
MS Access MVP

I have created one table that contains dates and opening and closing
figures
for each day. I have created a query and extracted from the date: Month,
Week
No, and Weekday Name. I then created a crosstab query to show in the Row
Heading: Month, Week (shown as the date of the first day of that week),
and
Sum Total of the figures. The Column Heading is the Weekdays. The Crosstab
query works fine.

However, the problem is when I create a report. The report looks great but
the parameters only give me the year I hard coded. I would like to be able
to
enter a typical Start and End date and it disply the information in the
crosstab report by the week.

I tried to send you the crosstab but it will not let me paste it into this
screen. I hope I have given you enough information to help.

Thanks for all your time and effort.

Tom

 

[Guest]

I understand setting paramets as you have outlined. Let me go back to my
orginal question which was "I would like to convert the week
number to the date of the first day of that week. How would you write that
code?" The formula I was given is "=DateAdd("d",(([Week
No]-1)*7)-Weekday(#1/1/2003#+1,#1/1/2003#)" and it works well for the year
that is hard coded.

The database I have has multiple years and I need to extract it in multiyear
reports. Is there a way to write this formula so I can get data that will
range from 2002 to the present?

There are 3 common ways of getting parameters into a query.

1) Place what looks like a field name, but isn't, in the criteria of the
query. When the name isn't recognized, you'll be prompted for it when the
query is run.

Example:
SELECT tblMyTable.* FROM tblMyTable WHERE tblMyTable.MyField = [Enter a
Value];

2) Have a form with the values you want to use. Refer to the controls on the
form in the criteria.

Example:
SELECT tblMyTable.* FROM tblMyTable WHERE tblMyTable.MyField =
Forms!frmMyFrom!txtMyTextbox;

3) Create the SQL for the query in code and then "rewrite" the query.

Example:
strSQL = "SELECT tblMyTable.* FROM tblMyTable WHERE tblMyTable.MyField =
#1/1/2004#;"
CurrentDb.QueryDefs("qryMyQuery").SQL = strSQL

I hope this helps. I haven't done much work with cross-tab queries, but the
basics for getting the information into them is the same.

--
Wayne Morgan
MS Access MVP

I have created one table that contains dates and opening and closing
figures
for each day. I have created a query and extracted from the date: Month,
Week
No, and Weekday Name. I then created a crosstab query to show in the Row
Heading: Month, Week (shown as the date of the first day of that week),
and
Sum Total of the figures. The Column Heading is the Weekdays. The Crosstab
query works fine.

However, the problem is when I create a report. The report looks great but
the parameters only give me the year I hard coded. I would like to be able
to
enter a typical Start and End date and it disply the information in the
crosstab report by the week.

I tried to send you the crosstab but it will not let me paste it into this
screen. I hope I have given you enough information to help.

Thanks for all your time and effort.

Tom

 

[Wayne Morgan]

Yes, you could take the value from your fields and break it down. You would
need the DateSerial function also. The formula will give you Sunday of the
week specified, you just need to plug in the field instead of the hard coded
date. The second formula I sent should do that.

=DateAdd("d",((WeekOfYear-1)*7) - Weekday(DateSerial(YearRequested,1,1)) +
1,
DateSerial(YearRequested,1,1))

Replace WeekOfYear with the name of the field that contains this value and
replace YearRequested with a formula that generates the year from the date
in the other field.

Example:
=DateAdd("d",(([WeekFieldName]-1)*7) -
Weekday(DateSerial(Year([DateFieldName],1,1)) + 1,
DateSerial(Year([DateFieldName]),1,1))