Display First day of week

[Dan]

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.
--
Dan

[James A. Fortune]

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

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

--

Ken Snell
<MS ACCESS MVP>

"Dan" <D...@discussions.microsoft.com> wrote in message
news:79D0A0B6-5FC6-4BF7...@microsoft.com...

[Dan]

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

[Dan]

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

[Aaron]

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 ?

[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" <Aa...@discussions.microsoft.com> wrote in message
news:038D0FC9-1551-4086...@microsoft.com...

[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

Ken Snell (MVP) wrote:
>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

>[quoted text clipped - 38 lines]

>>> > > line in
>>> > > the Date Fotter section? Thx.

--
Message posted via http://www.accessmonster.com

[Aaron]

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

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" <Aa...@discussions.microsoft.com> wrote in message

news:29429737-D60A-4747...@microsoft.com...

[Aaron]

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

"Aaron" <Aa...@discussions.microsoft.com> wrote in message

news:2443C826-96AB-4EB8...@microsoft.com...

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

Yes, that indeed will cause that error < smile >.

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

Good work!

>
> Thanks much Ken

You're welcome.