Creating a date value

[Guest]

I have the numbers one through seven. The numbers are in a field in a table
that has several hundred records. The numbers represent Sunday through
Saturday. I need an expression that will change every week to give me the
date that the day of the week is on. For example, this week, when I put in 1,
the value returned will be April 15, 2007. Next week when I put in 1, the
value returned will be April 22, 2007. When I put in 2 this week, April 16,
2007 will be returned. Next week, April 23, 2007 and so on and so forth. This
expression would be in a query, I guess. It seems like it should be doable
but I have a tension headache from trying. Any help would be appreciated.

 

[John W. Vinson]

I have the numbers one through seven. The numbers are in a field in a table
that has several hundred records. The numbers represent Sunday through
Saturday. I need an expression that will change every week to give me the
date that the day of the week is on. For example, this week, when I put in 1,
the value returned will be April 15, 2007. Next week when I put in 1, the
value returned will be April 22, 2007. When I put in 2 this week, April 16,
2007 will be returned. Next week, April 23, 2007 and so on and so forth. This
expression would be in a query, I guess. It seems like it should be doable
but I have a tension headache from trying. Any help would be appreciated.

Assuming a) that you want the calculated value to show the date during the
current week and b) that you count Sunday as the first day of the week,

DateAdd("d", [numberfield]-Weekday(Date()), Date())

Note that the same table record will show a different date every week, but
that's what you asked...

John W. Vinson [MVP]

 

[strive4peace]

GetDateInWeek
---

Hello,

define a function in a general module:

'~~~~~~~~~~~~~~~~~
Function GetDateInWeek(pDayNum As Integer) As Date

Dim mDay As Integer
mDay = Weekday(Date)

GetDateInWeek = Date - mDay + pDayNum

End Function
'~~~~~~~~~~~~~~~~~

WHERE
Weekday(Date) --> returns 1-7 for Sun-Sat
Date - mDay --> represents the Saturday before the week starts

then, when you add 1 to 7, you will get the date for Sun-Sat

~~~

in a query, you can do this:

field --> GetDateInWeek : GetWeekStart([DayNumberField])

on a form or report:

=GetDateInWeek ([DayNumber_controlname])



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Thanks to you both. This discussion group has been incredibly helpful in so
many ways. I really appreciate the time you take to answer silly questions
like mine!

GetDateInWeek
---

Hello,

define a function in a general module:

'~~~~~~~~~~~~~~~~~
Function GetDateInWeek(pDayNum As Integer) As Date

Dim mDay As Integer
mDay = Weekday(Date)

GetDateInWeek = Date - mDay + pDayNum

End Function
'~~~~~~~~~~~~~~~~~

WHERE
Weekday(Date) --> returns 1-7 for Sun-Sat
Date - mDay --> represents the Saturday before the week starts

then, when you add 1 to 7, you will get the date for Sun-Sat

~~~

in a query, you can do this:

field --> GetDateInWeek : GetWeekStart([DayNumberField])

on a form or report:

=GetDateInWeek ([DayNumber_controlname])



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I have the numbers one through seven. The numbers are in a field in a table
that has several hundred records. The numbers represent Sunday through
Saturday. I need an expression that will change every week to give me the
date that the day of the week is on. For example, this week, when I put in 1,
the value returned will be April 15, 2007. Next week when I put in 1, the
value returned will be April 22, 2007. When I put in 2 this week, April 16,
2007 will be returned. Next week, April 23, 2007 and so on and so forth. This
expression would be in a query, I guess. It seems like it should be doable
but I have a tension headache from trying. Any help would be appreciated.

 

[strive4peace]

not a silly question at all!

you are welcome happy to help

John's solution is more compact (and also more efficient) than mine but
both get the same result

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks to you both. This discussion group has been incredibly helpful in so
many ways. I really appreciate the time you take to answer silly questions
like mine!

GetDateInWeek
---

Hello,

define a function in a general module:

'~~~~~~~~~~~~~~~~~
Function GetDateInWeek(pDayNum As Integer) As Date

Dim mDay As Integer
mDay = Weekday(Date)

GetDateInWeek = Date - mDay + pDayNum

End Function
'~~~~~~~~~~~~~~~~~

WHERE
Weekday(Date) --> returns 1-7 for Sun-Sat
Date - mDay --> represents the Saturday before the week starts

then, when you add 1 to 7, you will get the date for Sun-Sat

~~~

in a query, you can do this:

field --> GetDateInWeek : GetWeekStart([DayNumberField])

on a form or report:

=GetDateInWeek ([DayNumber_controlname])



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I have the numbers one through seven. The numbers are in a field in a table
that has several hundred records. The numbers represent Sunday through
Saturday. I need an expression that will change every week to give me the
date that the day of the week is on. For example, this week, when I put in 1,
the value returned will be April 15, 2007. Next week when I put in 1, the
value returned will be April 22, 2007. When I put in 2 this week, April 16,
2007 will be returned. Next week, April 23, 2007 and so on and so forth. This
expression would be in a query, I guess. It seems like it should be doable
but I have a tension headache from trying. Any help would be appreciated.

 

[Storrboy]

I have the numbers one through seven. The numbers are in a field in a table
that has several hundred records. The numbers represent Sunday through
Saturday. I need an expression that will change every week to give me the
date that the day of the week is on. For example, this week, when I put in 1,
the value returned will be April 15, 2007. Next week when I put in 1, the
value returned will be April 22, 2007. When I put in 2 this week, April 16,
2007 will be returned. Next week, April 23, 2007 and so on and so forth. This
expression would be in a query, I guess. It seems like it should be doable
but I have a tension headache from trying. Any help would be appreciated.

Is the result being stored? If not, I'm not sure I understand how this will
work between different records? What I mean is, if reacord 1 represents last
week and record 2 this week, how will you know what week to base the
expression on for use in a query? Are there other date related fields to
take into account?

 

[Jim Bunton]

How about:
? Format(dateAdd("d",-(datepart("w",Date(),vbSunday)-1),Date()),"dddd dd
mmmm yyyy")

Substituting the day of the week (i.e. Sun = 1 Mon = 2 . . . ) where 1
appears in the above. YOU MUST LEAVE THE MINUS IN
e.g. substituting -2 for -1 gives the date for Monday this week
vbSunday is a VB constant using vbMonday would be used if Monday = 1 Tuesday
= 2 and so on.
So I guess you need to put [yourColumnName] where -1 appears.

[you can play about with this in the 'immediate' window of access and, in
future, work out how to combine th edatae functions to do what you want by
doing bits and peices in the immediate window then copying and pasting to
build up mthe final combination.