Display First day of week

G

Guest

I have a report with data for every day of the week beginning with the
10/20/05 format and then my other record data. I currently use the
=Format([Date],"ww") to display the week when I summarize on my report (on
Date Footer). Using the same [Date] feild how do I display the first day of
the week (Monday) in the date fomat "10/17/05" on the same summary line in
the Date Fotter section? Thx.
 
J

James A. Fortune

Dan said:
I have a report with data for every day of the week beginning with the
10/20/05 format and then my other record data. I currently use the
=Format([Date],"ww") to display the week when I summarize on my report (on
Date Footer). Using the same [Date] feild how do I display the first day of
the week (Monday) in the date fomat "10/17/05" on the same summary line in
the Date Fotter section? Thx.
Click to expand...

Here's how I would go about it. I can use the optional parameter of the
Weekday function or just lay out what I want:

I'd like to format something like:
DateAdd("d", SomeNumber, [DateField])

To get SomeNumber:
Weekday, SomeNumber
2 => 0
3 => -1
4 => -2
5 => -3
6 => -4
7 => -5
1 => -6

SomeNumber = -(X + 5) Mod 7 works. After checking that Access doesn't
do anything unexpected with the Mod function (it doesn't):

=Format(DateAdd("d", -(Weekday([DateField]) + 5) Mod 7, [DateField]),
"mm/dd/yy")

should do it. I used a different name for the date field since Date is
a reserved word in Access. Just replace DateField in both places with
the new name you are using.

James A. Fortune
 
K

Ken Snell [MVP]

Put this function in a regular module:


Public Function DateOfSpecificWeekDay(ByVal OriginalDate As Date, _
ByVal intWeekDay As Integer) As Date
' ** THIS FUNCTION RETURNS THE DATE OF THE SPECIFIC DAY OF THE WEEK
' ** IN WHICH THE ORIGINAL DATE IS.
' ** intWeekDay = 1 is Sunday, 2 is Monday, etc.
On Error Resume Next
DateOfSpecificWeekDay = DateAdd("d", -DatePart("w", OriginalDate, _
1) + intWeekDay, OriginalDate)
Err.Clear
End Function


Then use it as the Control Source in the textbox to show you the desired
date:
=DateOfSpecificWeekDay(Date(), 2)


Or you could just use this expression as the Control Source of your textbox:
= DateAdd("d", -DatePart("w", Date(), 1) + 2, Date())
 
G

Guest

James, thanks your solution worked great. Dan
--
Dan


James A. Fortune said:
Dan said:
I have a report with data for every day of the week beginning with the
10/20/05 format and then my other record data. I currently use the
=Format([Date],"ww") to display the week when I summarize on my report (on
Date Footer). Using the same [Date] feild how do I display the first day of
the week (Monday) in the date fomat "10/17/05" on the same summary line in
the Date Fotter section? Thx.
Click to expand...

Here's how I would go about it. I can use the optional parameter of the
Weekday function or just lay out what I want:

I'd like to format something like:
DateAdd("d", SomeNumber, [DateField])

To get SomeNumber:
Weekday, SomeNumber
2 => 0
3 => -1
4 => -2
5 => -3
6 => -4
7 => -5
1 => -6

SomeNumber = -(X + 5) Mod 7 works. After checking that Access doesn't
do anything unexpected with the Mod function (it doesn't):

=Format(DateAdd("d", -(Weekday([DateField]) + 5) Mod 7, [DateField]),
"mm/dd/yy")

should do it. I used a different name for the date field since Date is
a reserved word in Access. Just replace DateField in both places with
the new name you are using.

James A. Fortune
Click to expand...
 
G

Guest

Brilliant. I will give it a go. Dan
--
Dan


Ken Snell said:
Put this function in a regular module:


Public Function DateOfSpecificWeekDay(ByVal OriginalDate As Date, _
ByVal intWeekDay As Integer) As Date
' ** THIS FUNCTION RETURNS THE DATE OF THE SPECIFIC DAY OF THE WEEK
' ** IN WHICH THE ORIGINAL DATE IS.
' ** intWeekDay = 1 is Sunday, 2 is Monday, etc.
On Error Resume Next
DateOfSpecificWeekDay = DateAdd("d", -DatePart("w", OriginalDate, _
1) + intWeekDay, OriginalDate)
Err.Clear
End Function


Then use it as the Control Source in the textbox to show you the desired
date:
=DateOfSpecificWeekDay(Date(), 2)


Or you could just use this expression as the Control Source of your textbox:
= DateAdd("d", -DatePart("w", Date(), 1) + 2, Date())


--

Ken Snell
<MS ACCESS MVP>


Dan said:
I have a report with data for every day of the week beginning with the
10/20/05 format and then my other record data. I currently use the
=Format([Date],"ww") to display the week when I summarize on my report (on
Date Footer). Using the same [Date] feild how do I display the first day
of
the week (Monday) in the date fomat "10/17/05" on the same summary line in
the Date Fotter section? Thx.
Click to expand...
Click to expand...
 
G

Guest

This function works wonderfully! What I'm trying to do next is to have an
excel sheet linked to my query containing the above formula. However, the
MSQuery browser doesn't like the formula and says "data type mismatch".

is there a way to manipulate the above query to allow me to have excel link
to it properly ?

Dan said:
Brilliant. I will give it a go. Dan
--
Dan


Ken Snell said:
Put this function in a regular module:


Public Function DateOfSpecificWeekDay(ByVal OriginalDate As Date, _
ByVal intWeekDay As Integer) As Date
' ** THIS FUNCTION RETURNS THE DATE OF THE SPECIFIC DAY OF THE WEEK
' ** IN WHICH THE ORIGINAL DATE IS.
' ** intWeekDay = 1 is Sunday, 2 is Monday, etc.
On Error Resume Next
DateOfSpecificWeekDay = DateAdd("d", -DatePart("w", OriginalDate, _
1) + intWeekDay, OriginalDate)
Err.Clear
End Function


Then use it as the Control Source in the textbox to show you the desired
date:
=DateOfSpecificWeekDay(Date(), 2)


Or you could just use this expression as the Control Source of your textbox:
= DateAdd("d", -DatePart("w", Date(), 1) + 2, Date())


--

Ken Snell
<MS ACCESS MVP>


Dan said:
I have a report with data for every day of the week beginning with the
10/20/05 format and then my other record data. I currently use the
=Format([Date],"ww") to display the week when I summarize on my report (on
Date Footer). Using the same [Date] feild how do I display the first day
of
the week (Monday) in the date fomat "10/17/05" on the same summary line in
the Date Fotter section? Thx.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

I've not worked with MSQuery, so I cannot give a firm answer. However, you
could replace the function call in the query with the entire expression that
is used in the function (I'm assuming that you want to use Sunday as the
first day of the week, so I put a 1 in for the intWeekDay value in the
expression):

TheWeekDayNumber: DateAdd("d", -DatePart("w", [DateFieldName], 1) + 1,
[DateFieldName])

--

Ken Snell
<MS ACCESS MVP>



Aaron said:
This function works wonderfully! What I'm trying to do next is to have an
excel sheet linked to my query containing the above formula. However, the
MSQuery browser doesn't like the formula and says "data type mismatch".

is there a way to manipulate the above query to allow me to have excel
link
to it properly ?

Dan said:
Brilliant. I will give it a go. Dan
--
Dan


Ken Snell said:
Put this function in a regular module:


Public Function DateOfSpecificWeekDay(ByVal OriginalDate As Date, _
ByVal intWeekDay As Integer) As Date
' ** THIS FUNCTION RETURNS THE DATE OF THE SPECIFIC DAY OF THE WEEK
' ** IN WHICH THE ORIGINAL DATE IS.
' ** intWeekDay = 1 is Sunday, 2 is Monday, etc.
On Error Resume Next
DateOfSpecificWeekDay = DateAdd("d", -DatePart("w", OriginalDate, _
1) + intWeekDay, OriginalDate)
Err.Clear
End Function


Then use it as the Control Source in the textbox to show you the
desired
date:
=DateOfSpecificWeekDay(Date(), 2)


Or you could just use this expression as the Control Source of your
textbox:
= DateAdd("d", -DatePart("w", Date(), 1) + 2, Date())


--

Ken Snell
<MS ACCESS MVP>


I have a report with data for every day of the week beginning with the
10/20/05 format and then my other record data. I currently use the
=Format([Date],"ww") to display the week when I summarize on my
report (on
Date Footer). Using the same [Date] feild how do I display the first
day
of
the week (Monday) in the date fomat "10/17/05" on the same summary
line in
the Date Fotter section? Thx.
Click to expand...
Click to expand...
Click to expand...
 
R

raskew via AccessMonster.com

Today is Tuesday 7 Aug 2007. Try this to obtain the Sunday of this week:

? date() - weekday(date()) + 1
8/5/2007

Bob said:
I've not worked with MSQuery, so I cannot give a firm answer. However, you
could replace the function call in the query with the entire expression that
is used in the function (I'm assuming that you want to use Sunday as the
first day of the week, so I put a 1 in for the intWeekDay value in the
expression):

TheWeekDayNumber: DateAdd("d", -DatePart("w", [DateFieldName], 1) + 1,
[DateFieldName])
This function works wonderfully! What I'm trying to do next is to have an
excel sheet linked to my query containing the above formula. However, the
Click to expand...
[quoted text clipped - 38 lines]
Click to expand...
 
G

Guest

That's exactly what I've done. The query works perfectly with Access, but
when I try using the data/import external data/ New database query, I get the
"data type mismatch" error.

Ken Snell (MVP) said:
I've not worked with MSQuery, so I cannot give a firm answer. However, you
could replace the function call in the query with the entire expression that
is used in the function (I'm assuming that you want to use Sunday as the
first day of the week, so I put a 1 in for the intWeekDay value in the
expression):

TheWeekDayNumber: DateAdd("d", -DatePart("w", [DateFieldName], 1) + 1,
[DateFieldName])

--

Ken Snell
<MS ACCESS MVP>



Aaron said:
This function works wonderfully! What I'm trying to do next is to have an
excel sheet linked to my query containing the above formula. However, the
MSQuery browser doesn't like the formula and says "data type mismatch".

is there a way to manipulate the above query to allow me to have excel
link
to it properly ?

Dan said:
Brilliant. I will give it a go. Dan
--
Dan


:

Put this function in a regular module:


Public Function DateOfSpecificWeekDay(ByVal OriginalDate As Date, _
ByVal intWeekDay As Integer) As Date
' ** THIS FUNCTION RETURNS THE DATE OF THE SPECIFIC DAY OF THE WEEK
' ** IN WHICH THE ORIGINAL DATE IS.
' ** intWeekDay = 1 is Sunday, 2 is Monday, etc.
On Error Resume Next
DateOfSpecificWeekDay = DateAdd("d", -DatePart("w", OriginalDate, _
1) + intWeekDay, OriginalDate)
Err.Clear
End Function


Then use it as the Control Source in the textbox to show you the
desired
date:
=DateOfSpecificWeekDay(Date(), 2)


Or you could just use this expression as the Control Source of your
textbox:
= DateAdd("d", -DatePart("w", Date(), 1) + 2, Date())


--

Ken Snell
<MS ACCESS MVP>


I have a report with data for every day of the week beginning with the
10/20/05 format and then my other record data. I currently use the
=Format([Date],"ww") to display the week when I summarize on my
report (on
Date Footer). Using the same [Date] feild how do I display the first
day
of
the week (Monday) in the date fomat "10/17/05" on the same summary
line in
the Date Fotter section? Thx.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

I have created a query in an ACCESS file that uses the expression as a
calculated field. I then used EXCEL as you indicated to return the results
of that query, and did not get a "data type mismatch" error. I tried it with
and withou sorting/filtering options when linking to the query from EXCEL.

Can you give us the actual SQL statement of the ACCESS query, and the exact
steps that you used to link to the query in EXCEL?
--

Ken Snell
<MS ACCESS MVP>





Aaron said:
That's exactly what I've done. The query works perfectly with Access, but
when I try using the data/import external data/ New database query, I get
the
"data type mismatch" error.

Ken Snell (MVP) said:
I've not worked with MSQuery, so I cannot give a firm answer. However,
you
could replace the function call in the query with the entire expression
that
is used in the function (I'm assuming that you want to use Sunday as the
first day of the week, so I put a 1 in for the intWeekDay value in the
expression):

TheWeekDayNumber: DateAdd("d", -DatePart("w", [DateFieldName], 1) + 1,
[DateFieldName])

--

Ken Snell
<MS ACCESS MVP>



Aaron said:
This function works wonderfully! What I'm trying to do next is to have
an
excel sheet linked to my query containing the above formula. However,
the
MSQuery browser doesn't like the formula and says "data type mismatch".

is there a way to manipulate the above query to allow me to have excel
link
to it properly ?

:

Brilliant. I will give it a go. Dan
--
Dan


:

Put this function in a regular module:


Public Function DateOfSpecificWeekDay(ByVal OriginalDate As Date, _
ByVal intWeekDay As Integer) As Date
' ** THIS FUNCTION RETURNS THE DATE OF THE SPECIFIC DAY OF THE WEEK
' ** IN WHICH THE ORIGINAL DATE IS.
' ** intWeekDay = 1 is Sunday, 2 is Monday, etc.
On Error Resume Next
DateOfSpecificWeekDay = DateAdd("d", -DatePart("w", OriginalDate, _
1) + intWeekDay, OriginalDate)
Err.Clear
End Function


Then use it as the Control Source in the textbox to show you the
desired
date:
=DateOfSpecificWeekDay(Date(), 2)


Or you could just use this expression as the Control Source of your
textbox:
= DateAdd("d", -DatePart("w", Date(), 1) + 2, Date())


--

Ken Snell
<MS ACCESS MVP>


I have a report with data for every day of the week beginning with
the
10/20/05 format and then my other record data. I currently use the
=Format([Date],"ww") to display the week when I summarize on my
report (on
Date Footer). Using the same [Date] feild how do I display the
first
day
of
the week (Monday) in the date fomat "10/17/05" on the same summary
line in
the Date Fotter section? Thx.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

So, after researching further, it appears that when my date I am looking up
is null, the expression returns an error, which gives me a "data type
mismatch" error when exporting to excel...

after I put in an if statement to catch this, it works like a charm.

Thanks much Ken

Ken Snell (MVP) said:
I have created a query in an ACCESS file that uses the expression as a
calculated field. I then used EXCEL as you indicated to return the results
of that query, and did not get a "data type mismatch" error. I tried it with
and withou sorting/filtering options when linking to the query from EXCEL.

Can you give us the actual SQL statement of the ACCESS query, and the exact
steps that you used to link to the query in EXCEL?
--

Ken Snell
<MS ACCESS MVP>





Aaron said:
That's exactly what I've done. The query works perfectly with Access, but
when I try using the data/import external data/ New database query, I get
the
"data type mismatch" error.

Ken Snell (MVP) said:
I've not worked with MSQuery, so I cannot give a firm answer. However,
you
could replace the function call in the query with the entire expression
that
is used in the function (I'm assuming that you want to use Sunday as the
first day of the week, so I put a 1 in for the intWeekDay value in the
expression):

TheWeekDayNumber: DateAdd("d", -DatePart("w", [DateFieldName], 1) + 1,
[DateFieldName])

--

Ken Snell
<MS ACCESS MVP>



This function works wonderfully! What I'm trying to do next is to have
an
excel sheet linked to my query containing the above formula. However,
the
MSQuery browser doesn't like the formula and says "data type mismatch".

is there a way to manipulate the above query to allow me to have excel
link
to it properly ?

:

Brilliant. I will give it a go. Dan
--
Dan


:

Put this function in a regular module:


Public Function DateOfSpecificWeekDay(ByVal OriginalDate As Date, _
ByVal intWeekDay As Integer) As Date
' ** THIS FUNCTION RETURNS THE DATE OF THE SPECIFIC DAY OF THE WEEK
' ** IN WHICH THE ORIGINAL DATE IS.
' ** intWeekDay = 1 is Sunday, 2 is Monday, etc.
On Error Resume Next
DateOfSpecificWeekDay = DateAdd("d", -DatePart("w", OriginalDate, _
1) + intWeekDay, OriginalDate)
Err.Clear
End Function


Then use it as the Control Source in the textbox to show you the
desired
date:
=DateOfSpecificWeekDay(Date(), 2)


Or you could just use this expression as the Control Source of your
textbox:
= DateAdd("d", -DatePart("w", Date(), 1) + 2, Date())


--

Ken Snell
<MS ACCESS MVP>


I have a report with data for every day of the week beginning with
the
10/20/05 format and then my other record data. I currently use the
=Format([Date],"ww") to display the week when I summarize on my
report (on
Date Footer). Using the same [Date] feild how do I display the
first
day
of
the week (Monday) in the date fomat "10/17/05" on the same summary
line in
the Date Fotter section? Thx.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Aaron said:
So, after researching further, it appears that when my date I am looking
up
is null, the expression returns an error, which gives me a "data type
mismatch" error when exporting to excel...
Click to expand...

Yes said:
after I put in an if statement to catch this, it works like a charm.
Click to expand...

Good work!
Thanks much Ken
Click to expand...

You're welcome.
 

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

Pivot table define Monday as first day of week 3
Get date for start of week 3
Week Number 2
Finding the date of the first day of the week using "ww" to ID Wee 3
Date for 1st day of the week 1
Calculate day of week in form & store in table 2
Day of Week 3
Day of the week 3

Top