week number

P

Pedro

Hi everyone,
Here's a tuff one :
On my report i have several records grouped by the week
number. Question : How to say that, week number is from 22-
12-03 and 28-12-03. I want to leave the information that
my week number is for example nº 50. But i also want to
say that, that week number is from data 1 till date 2.
Any ideas?
Tks in advance
Pedro
 
E

Evi

Firstly, don't forget to group by Year first, then by week number (New Year
will be here soon)

In the Group header for your WeekNumbers put a text box and type in it:

=Min([MyDateField]) & " to " & Max([MyDateField])

(Replace MyDateField with the real name of your date field)

Or did you actually want the Start date of eg the 30th week in the year and
the end date of the 30th week in the year?
Evi


Hi everyone,
Here's a tuff one :
On my report i have several records grouped by the week
number. Question : How to say that, week number is from 22-
12-03 and 28-12-03. I want to leave the information that
my week number is for example nº 50. But i also want to
say that, that week number is from data 1 till date 2.
Any ideas?
Tks in advance
Pedro
 
P

Pedro

Hi Evi and many tks for your reply, but now that's
working, i would like that the [MyDateField] should be not
my date input but THE date. Let me explain : My date input
does not have all the dates i need. And i need on that
MyDateField, all dates, wich means always between 7 days
difference. Maybe i should have a table for those dates. I
hope you understand my english, he he he.
Tks in advance
Pedro
 
E

Evi

If you want a date field to put in the current date when you enter a new
record then in Table Design, in the Default Value row type:
Date()

I've found that having a Date table is a big advantage. (Just don't call the
field with the date, 'Date' Access plays up if you do that because Date is a
function. Call it ADate or something).

I don't know if I do understand you correctly but be patient with me and
keep explaining and one of the group will get there in the end.

If you want to enter all the dates for a whole year into your date table,
first set up your table so that it will only accept a date once. Do that in
Table Design View by clicking the date field and go down to where it says
'Indexed' and choose 'Yes (No Duplicates)

Then here is some code that will input all the rest of the dates for any
year you choose.

Sub FillInDates()
'fills in the dates for the year
Dim MyYr As Integer
Dim MyTbl As String
Dim TotDays As Integer
Dim b As Integer
Dim MySql As String
Dim StDate As Date
Dim MyDateField As String
MyTbl = InputBox("What is the name of your date table?")
MyDateField = InputBox("What is the name of your date field in the table?")
MyTbl = MyTbl & "(" & MyDateField & ")"
'Name of table and Date field in brackets
MyYr = InputBox("Which year? eg 2005")
'Calculate if the year is a leap year'
If MyYr Mod 4 = 0 Then
'a leap year
TotDays = 366
Else
TotDays = 365
End If
DoCmd.SetWarnings False
For b = 1 To TotDays
MySql = "INSERT INTO " & MyTbl & " VALUES (" & Format(DateSerial(MyYr, 1,
b), "0") & ")"
DoCmd.RunSQL MySql
Next b
DoCmd.SetWarnings True
End Sub

Paste the code into a blank module in your database and click in it and
press the Go/Continue button.


If you want to know the date 7 days after any date then in another field in
your query put

NextDate: [YourDateField]+7

If you want your dates grouped into weeks then in a query based on your date
table put

Wk: DatePart("ww",[YourDateField])

You can group by this field in your report.
Evi




Hi Evi and many tks for your reply, but now that's
working, i would like that the [MyDateField] should be not
my date input but THE date. Let me explain : My date input
does not have all the dates i need. And i need on that
MyDateField, all dates, wich means always between 7 days
difference. Maybe i should have a table for those dates. I
hope you understand my english, he he he.
Tks in advance
Pedro
-----Original Message-----
Firstly, don't forget to group by Year first, then by week number (New Year
will be here soon)

In the Group header for your WeekNumbers put a text box and type in it:

=Min([MyDateField]) & " to " & Max([MyDateField])

(Replace MyDateField with the real name of your date field)

Or did you actually want the Start date of eg the 30th week in the year and
the end date of the 30th week in the year?
Evi


Hi everyone,
Here's a tuff one :
On my report i have several records grouped by the week
number. Question : How to say that, week number is from 22-
12-03 and 28-12-03. I want to leave the information that
my week number is for example nº 50. But i also want to
say that, that week number is from data 1 till date 2.
Any ideas?
Tks in advance
Pedro


.
Click to expand...
 
P

Pedro

Hi again Evi and many tks for your reply.
Here is: i have a table where i input some value and a
date. Yuo may have only two records for that week number
but i would like to say, somewhere in my report that, that
week starts on some day and ends at ...7 days later.
I have my report already grouped by week number and now i
would like, even i only have 2 or just one record for that
week number that, that week starts on some day and ends at
7 days later. Hope my english was clear enough he he he
Tks in advance
Pedro

-----Original Message-----
If you want a date field to put in the current date when you enter a new
record then in Table Design, in the Default Value row type:
Date()

I've found that having a Date table is a big advantage. (Just don't call the
field with the date, 'Date' Access plays up if you do that because Date is a
function. Call it ADate or something).

I don't know if I do understand you correctly but be patient with me and
keep explaining and one of the group will get there in the end.

If you want to enter all the dates for a whole year into your date table,
first set up your table so that it will only accept a date once. Do that in
Table Design View by clicking the date field and go down to where it says
'Indexed' and choose 'Yes (No Duplicates)

Then here is some code that will input all the rest of the dates for any
year you choose.

Sub FillInDates()
'fills in the dates for the year
Dim MyYr As Integer
Dim MyTbl As String
Dim TotDays As Integer
Dim b As Integer
Dim MySql As String
Dim StDate As Date
Dim MyDateField As String
MyTbl = InputBox("What is the name of your date table?")
MyDateField = InputBox("What is the name of your date field in the table?")
MyTbl = MyTbl & "(" & MyDateField & ")"
'Name of table and Date field in brackets
MyYr = InputBox("Which year? eg 2005")
'Calculate if the year is a leap year'
If MyYr Mod 4 = 0 Then
'a leap year
TotDays = 366
Else
TotDays = 365
End If
DoCmd.SetWarnings False
For b = 1 To TotDays
MySql = "INSERT INTO " & MyTbl & " VALUES (" & Format (DateSerial(MyYr, 1,
b), "0") & ")"
DoCmd.RunSQL MySql
Next b
DoCmd.SetWarnings True
End Sub

Paste the code into a blank module in your database and click in it and
press the Go/Continue button.


If you want to know the date 7 days after any date then in another field in
your query put

NextDate: [YourDateField]+7

If you want your dates grouped into weeks then in a query based on your date
table put

Wk: DatePart("ww",[YourDateField])

You can group by this field in your report.
Evi




Hi Evi and many tks for your reply, but now that's
working, i would like that the [MyDateField] should be not
my date input but THE date. Let me explain : My date input
does not have all the dates i need. And i need on that
MyDateField, all dates, wich means always between 7 days
difference. Maybe i should have a table for those dates. I
hope you understand my english, he he he.
Tks in advance
Pedro
-----Original Message-----
Firstly, don't forget to group by Year first, then by week number (New Year
will be here soon)

In the Group header for your WeekNumbers put a text box and type in it:

=Min([MyDateField]) & " to " & Max([MyDateField])

(Replace MyDateField with the real name of your date field)

Or did you actually want the Start date of eg the 30th week in the year and
the end date of the 30th week in the year?
Evi


Hi everyone,
Here's a tuff one :
On my report i have several records grouped by the week
number. Question : How to say that, week number is from 22-
12-03 and 28-12-03. I want to leave the information that
my week number is for example nº 50. But i also want to
say that, that week number is from data 1 till date 2.
Any ideas?
Tks in advance
Pedro


.
Click to expand...


.
Click to expand...
 
E

Evi

Do you mean that for a date you want to know the date of the Monday just
before it and the date of the Sunday just after it?

Does your week go
from Monday to Sunday
or
from Sunday to Saturday
or
from Monday to Friday.


Lets say your week is from Monday to Sunday
Add 2 new fields to the query based on your Date table.

MyMon: ([BookDate]+1)-DatePart("w",[BookDate],2)
MySun: MySun: ([BookDate]+1)-DatePart("w",[BookDate],2)+6

In your report's Group Header for the Week, put a text box with

=[MyMon] & " to " & [MySun]

and each week will let you know the start and the end date of that week with
the first day as Monday and the last day as Sunday.

What is your native language, Pedro? I bet you speak English much, much
better than I could your language.

Evi



Hi again Evi and many tks for your reply.
Here is: i have a table where i input some value and a
date. Yuo may have only two records for that week number
but i would like to say, somewhere in my report that, that
week starts on some day and ends at ...7 days later.
I have my report already grouped by week number and now i
would like, even i only have 2 or just one record for that
week number that, that week starts on some day and ends at
7 days later. Hope my english was clear enough he he he
Tks in advance
Pedro

-----Original Message-----
If you want a date field to put in the current date when you enter a new
record then in Table Design, in the Default Value row type:
Date()

I've found that having a Date table is a big advantage. (Just don't call the
field with the date, 'Date' Access plays up if you do that because Date is a
function. Call it ADate or something).

I don't know if I do understand you correctly but be patient with me and
keep explaining and one of the group will get there in the end.

If you want to enter all the dates for a whole year into your date table,
first set up your table so that it will only accept a date once. Do that in
Table Design View by clicking the date field and go down to where it says
'Indexed' and choose 'Yes (No Duplicates)

Then here is some code that will input all the rest of the dates for any
year you choose.

Sub FillInDates()
'fills in the dates for the year
Dim MyYr As Integer
Dim MyTbl As String
Dim TotDays As Integer
Dim b As Integer
Dim MySql As String
Dim StDate As Date
Dim MyDateField As String
MyTbl = InputBox("What is the name of your date table?")
MyDateField = InputBox("What is the name of your date field in the table?")
MyTbl = MyTbl & "(" & MyDateField & ")"
'Name of table and Date field in brackets
MyYr = InputBox("Which year? eg 2005")
'Calculate if the year is a leap year'
If MyYr Mod 4 = 0 Then
'a leap year
TotDays = 366
Else
TotDays = 365
End If
DoCmd.SetWarnings False
For b = 1 To TotDays
MySql = "INSERT INTO " & MyTbl & " VALUES (" & Format (DateSerial(MyYr, 1,
b), "0") & ")"
DoCmd.RunSQL MySql
Next b
DoCmd.SetWarnings True
End Sub

Paste the code into a blank module in your database and click in it and
press the Go/Continue button.


If you want to know the date 7 days after any date then in another field in
your query put

NextDate: [YourDateField]+7

If you want your dates grouped into weeks then in a query based on your date
table put

Wk: DatePart("ww",[YourDateField])

You can group by this field in your report.
Evi




Hi Evi and many tks for your reply, but now that's
working, i would like that the [MyDateField] should be not
my date input but THE date. Let me explain : My date input
does not have all the dates i need. And i need on that
MyDateField, all dates, wich means always between 7 days
difference. Maybe i should have a table for those dates. I
hope you understand my english, he he he.
Tks in advance
Pedro
-----Original Message-----
Firstly, don't forget to group by Year first, then by week number (New Year
will be here soon)

In the Group header for your WeekNumbers put a text box and type in it:

=Min([MyDateField]) & " to " & Max([MyDateField])

(Replace MyDateField with the real name of your date field)

Or did you actually want the Start date of eg the 30th week in the year and
the end date of the 30th week in the year?
Evi


Hi everyone,
Here's a tuff one :
On my report i have several records grouped by the week
number. Question : How to say that, week number is from 22-
12-03 and 28-12-03. I want to leave the information that
my week number is for example nº 50. But i also want to
say that, that week number is from data 1 till date 2.
Any ideas?
Tks in advance
Pedro


.
Click to expand...


.
Click to expand...
 
P

Pedro

Evi,
My week goes from Mon to Sun and the above formula is
working perfectly. You're the best. Thank you very much.
About my native language, i'm portuguese and i also can
speak/write french and spanish, all easy languages for a
latin i mean.
Tks again Evi
Pedro
-----Original Message-----
Do you mean that for a date you want to know the date of the Monday just
before it and the date of the Sunday just after it?

Does your week go
from Monday to Sunday
or
from Sunday to Saturday
or
from Monday to Friday.


Lets say your week is from Monday to Sunday
Add 2 new fields to the query based on your Date table.

MyMon: ([BookDate]+1)-DatePart("w",[BookDate],2)
MySun: MySun: ([BookDate]+1)-DatePart("w",[BookDate],2)+6

In your report's Group Header for the Week, put a text box with

=[MyMon] & " to " & [MySun]

and each week will let you know the start and the end date of that week with
the first day as Monday and the last day as Sunday.

What is your native language, Pedro? I bet you speak English much, much
better than I could your language.

Evi



Hi again Evi and many tks for your reply.
Here is: i have a table where i input some value and a
date. Yuo may have only two records for that week number
but i would like to say, somewhere in my report that, that
week starts on some day and ends at ...7 days later.
I have my report already grouped by week number and now i
would like, even i only have 2 or just one record for that
week number that, that week starts on some day and ends at
7 days later. Hope my english was clear enough he he he
Tks in advance
Pedro

-----Original Message-----
If you want a date field to put in the current date when you enter a new
record then in Table Design, in the Default Value row type:
Date()

I've found that having a Date table is a big advantage. (Just don't call the
field with the date, 'Date' Access plays up if you do that because Date is a
function. Call it ADate or something).

I don't know if I do understand you correctly but be patient with me and
keep explaining and one of the group will get there in the end.

If you want to enter all the dates for a whole year into your date table,
first set up your table so that it will only accept a date once. Do that in
Table Design View by clicking the date field and go down to where it says
'Indexed' and choose 'Yes (No Duplicates)

Then here is some code that will input all the rest of the dates for any
year you choose.

Sub FillInDates()
'fills in the dates for the year
Dim MyYr As Integer
Dim MyTbl As String
Dim TotDays As Integer
Dim b As Integer
Dim MySql As String
Dim StDate As Date
Dim MyDateField As String
MyTbl = InputBox("What is the name of your date table?")
MyDateField = InputBox("What is the name of your date field in the table?")
MyTbl = MyTbl & "(" & MyDateField & ")"
'Name of table and Date field in brackets
MyYr = InputBox("Which year? eg 2005")
'Calculate if the year is a leap year'
If MyYr Mod 4 = 0 Then
'a leap year
TotDays = 366
Else
TotDays = 365
End If
DoCmd.SetWarnings False
For b = 1 To TotDays
MySql = "INSERT INTO " & MyTbl & " VALUES (" & Format (DateSerial(MyYr, 1,
b), "0") & ")"
DoCmd.RunSQL MySql
Next b
DoCmd.SetWarnings True
End Sub

Paste the code into a blank module in your database and click in it and
press the Go/Continue button.


If you want to know the date 7 days after any date then in another field in
your query put

NextDate: [YourDateField]+7

If you want your dates grouped into weeks then in a query based on your date
table put

Wk: DatePart("ww",[YourDateField])

You can group by this field in your report.
Evi




Hi Evi and many tks for your reply, but now that's
working, i would like that the [MyDateField] should be not
my date input but THE date. Let me explain : My date input
does not have all the dates i need. And i need on that
MyDateField, all dates, wich means always between 7 days
difference. Maybe i should have a table for those dates. I
hope you understand my english, he he he.
Tks in advance
Pedro
-----Original Message-----
Firstly, don't forget to group by Year first, then by week number (New Year
will be here soon)

In the Group header for your WeekNumbers put a text box and type in it:

=Min([MyDateField]) & " to " & Max([MyDateField])

(Replace MyDateField with the real name of your date field)

Or did you actually want the Start date of eg the 30th week in the year and
the end date of the 30th week in the year?
Evi


Hi everyone,
Here's a tuff one :
On my report i have several records grouped by the week
number. Question : How to say that, week number is from 22-
12-03 and 28-12-03. I want to leave the information that
my week number is for example nº 50. But i also want to
say that, that week number is from data 1 till date 2.
Any ideas?
Tks in advance
Pedro


.
Click to expand...


.
Click to expand...


.
Click to expand...
 
E

Evi

I'm so pleased it all works. My Portugese is non-existent and there is no
way I could ask a database question in French so you get top marks for your
English.
Evi

Evi,
My week goes from Mon to Sun and the above formula is
working perfectly. You're the best. Thank you very much.
About my native language, i'm portuguese and i also can
speak/write french and spanish, all easy languages for a
latin i mean.
Tks again Evi
Pedro
-----Original Message-----
Do you mean that for a date you want to know the date of the Monday just
before it and the date of the Sunday just after it?

Does your week go
from Monday to Sunday
or
from Sunday to Saturday
or
from Monday to Friday.


Lets say your week is from Monday to Sunday
Add 2 new fields to the query based on your Date table.

MyMon: ([BookDate]+1)-DatePart("w",[BookDate],2)
MySun: MySun: ([BookDate]+1)-DatePart("w",[BookDate],2)+6

In your report's Group Header for the Week, put a text box with

=[MyMon] & " to " & [MySun]

and each week will let you know the start and the end date of that week with
the first day as Monday and the last day as Sunday.

What is your native language, Pedro? I bet you speak English much, much
better than I could your language.

Evi



Hi again Evi and many tks for your reply.
Here is: i have a table where i input some value and a
date. Yuo may have only two records for that week number
but i would like to say, somewhere in my report that, that
week starts on some day and ends at ...7 days later.
I have my report already grouped by week number and now i
would like, even i only have 2 or just one record for that
week number that, that week starts on some day and ends at
7 days later. Hope my english was clear enough he he he
Tks in advance
Pedro

-----Original Message-----
If you want a date field to put in the current date when you enter a new
record then in Table Design, in the Default Value row type:
Date()

I've found that having a Date table is a big advantage. (Just don't call the
field with the date, 'Date' Access plays up if you do that because Date is a
function. Call it ADate or something).

I don't know if I do understand you correctly but be patient with me and
keep explaining and one of the group will get there in the end.

If you want to enter all the dates for a whole year into your date table,
first set up your table so that it will only accept a date once. Do that in
Table Design View by clicking the date field and go down to where it says
'Indexed' and choose 'Yes (No Duplicates)

Then here is some code that will input all the rest of the dates for any
year you choose.

Sub FillInDates()
'fills in the dates for the year
Dim MyYr As Integer
Dim MyTbl As String
Dim TotDays As Integer
Dim b As Integer
Dim MySql As String
Dim StDate As Date
Dim MyDateField As String
MyTbl = InputBox("What is the name of your date table?")
MyDateField = InputBox("What is the name of your date field in the table?")
MyTbl = MyTbl & "(" & MyDateField & ")"
'Name of table and Date field in brackets
MyYr = InputBox("Which year? eg 2005")
'Calculate if the year is a leap year'
If MyYr Mod 4 = 0 Then
'a leap year
TotDays = 366
Else
TotDays = 365
End If
DoCmd.SetWarnings False
For b = 1 To TotDays
MySql = "INSERT INTO " & MyTbl & " VALUES (" & Format (DateSerial(MyYr, 1,
b), "0") & ")"
DoCmd.RunSQL MySql
Next b
DoCmd.SetWarnings True
End Sub

Paste the code into a blank module in your database and click in it and
press the Go/Continue button.


If you want to know the date 7 days after any date then in another field in
your query put

NextDate: [YourDateField]+7

If you want your dates grouped into weeks then in a query based on your date
table put

Wk: DatePart("ww",[YourDateField])

You can group by this field in your report.
Evi




Hi Evi and many tks for your reply, but now that's
working, i would like that the [MyDateField] should be not
my date input but THE date. Let me explain : My date input
does not have all the dates i need. And i need on that
MyDateField, all dates, wich means always between 7 days
difference. Maybe i should have a table for those dates. I
hope you understand my english, he he he.
Tks in advance
Pedro
-----Original Message-----
Firstly, don't forget to group by Year first, then by week number (New Year
will be here soon)

In the Group header for your WeekNumbers put a text box and type in it:

=Min([MyDateField]) & " to " & Max([MyDateField])

(Replace MyDateField with the real name of your date field)

Or did you actually want the Start date of eg the 30th week in the year and
the end date of the 30th week in the year?
Evi


Hi everyone,
Here's a tuff one :
On my report i have several records grouped by the week
number. Question : How to say that, week number is from 22-
12-03 and 28-12-03. I want to leave the information that
my week number is for example nº 50. But i also want to
say that, that week number is from data 1 till date 2.
Any ideas?
Tks in advance
Pedro


.
Click to expand...


.
Click to expand...


.
Click to expand...
 
Top