date question-(allanon)

[JINGRAM]

Sorry usually when I reply to my own thread I don't get an answer -
this is regarding the question I asked earlier today

I tried the DateAdd("w",((WeekDay([BDT],vbMonday)-1)*-1,[BDT])

I actually want it to return the first day of the week (monday) I don't want to have to type in a date for it to return - I have about 300 records with diferent [BDT]'s

For example: if I do - DateAdd("w",-7,[BDT]) it gives exactly that -
Say my BDT is 5/5/02 then in the projected date column it say =4/28/02 which is 7 days priors - but the problem is no matter what its going to give me 7 days ( that day is a sunday) so what I want is it to automatically give me monday - so for any date that is in the week of 5/6/02 I want it to give me the previous monday?
Does that make sense?
Sorry sometimes I confuse myself when I am trying to get my thoughts down -
Thanks
JINGRAM

 

[DataChick]

This may not be the most efficient way to accomplish what you are looking for but this is what I would do:

Do an If that evaluates each day of the week -

Basically, If(Weekday([BDT],2)=2 {Tues}, DateAdd("d",-8,[BDT]),If(Weekday([BDT],2)=3 {Wed}, DateAdd("d",-9,[BDT])...

and so on like that. It should give you the results you were looking for.

Hope this helps.

Lisa

 

[allanon]

I am not sure where your problem is. If I put todays date in there it returns 13/05/2002. If I put tomorrow it returns 13/05/2002. I am missing something here. I set up a form with two fields, one input, one output and a button. I click the button and the date in the input field is processed and I get output.

Here is the calc I was playing with in case I messed it up in copying it.

Me.txtOutDate = DateAdd("w", ((WeekDay(Me.txtInDate, vbMonday) - 1) * -1), Me.txtInDate)

This is the original. Just insert your datafield and it should work fine...

OK... I just tried it on a select statement for processing lots of records and this is what I needed to do to have it work:

SELECT W.RES_BEGIN,
DateAdd("w", ((WeekDay(W.RES_BEGIN, 2) - 1) * -1), W.RES_BEGIN) AS CALCDATE
FROM WORKING W

The above statement works good. Here is my output:
RES_BEGIN CALCDATE
01/01/2002 31/12/2001
19/01/2002 14/01/2002
21/02/2002 18/02/2002
11/03/2002 11/03/2002

Each calcdat is the monday before the res_begin

 

[WildHare]

JINGRAM : The following simple function will return the DATE of the MONDAY of the week the passed date is in:

Public Function FDOW(SomeDate As Date) As Date
' -----------------------------------
' return: date of the MONDAY of the week
' input : any valid date
' -------------------------------------
Dim wkdy As String

wkdy = Format(SomeDate, "ddd&quot

Select Case wkdy
Case "mon"
FDOW = SomeDate
Case "tue"
FDOW = SomeDate - 1
Case "wed"
FDOW = SomeDate - 2
Case "thu"
FDOW = SomeDate - 3
Case "fri"
FDOW = SomeDate - 4
Case "sat"
FDOW = SomeDate - 5
End Select

End Function


It's kinda ugly 'cause all it does is subtract days based on the Day of the week, but it seems to work:

FDOY(5/16/02) = 5/13/02

FDOY(6/1/02) = 5/27/02

The only thing I didn't mess with is Sunday - would you want the NEXT day, or the monday of the 'previous' week?? IOW, on 5/12/02, would you want to see 5/13 or 5/6?



How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm