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

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?

--
Wayne Morgan
MS Access MVP


"Tom" wrote in message
...
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?

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" wrote:

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?

--
Wayne Morgan
MS Access MVP


"Tom" wrote in message
...
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?

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


"Tom" wrote in message
...
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" wrote:

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?

--
Wayne Morgan
MS Access MVP


"Tom" wrote in message
...
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?

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?

"Wayne Morgan" wrote:

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


"Tom" wrote in message
...
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" wrote:

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?

--
Wayne Morgan
MS Access MVP


"Tom" wrote in message
...
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?

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?

"Wayne Morgan" wrote:

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


"Tom" wrote in message
...
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" wrote:

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?

--
Wayne Morgan
MS Access MVP


"Tom" wrote in message
...
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?

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))

--
Wayne Morgan
MS Access MVP


"Tom" wrote in message
...
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?