Date question

[Ryan]

Here is what I am trying to do, it is kind of hard to
explain so try to bear with me.

I would like to display a certain date.
- On each Sunday I would like this date to display
the date of the Previous Friday.

For example:
Suppose this sunday is the 1st of the Month.
WHen Sunday comes around I would like the date to display
the last friday of the previous month.

When the next sunday roles around I would like the date to
now show Friday the 6th.

How would I create a date function that can do this?

Thanks in advance,
Ryan

 

[Mark Phillipson]

Hi,

If format(date(),"dddd") = "Sunday" Then
Me.txtSomeTextBox = date() - 2
Else
Me.txtSomeTextBox = date()
End if

HTH

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/

 

[Ryan]

Mark,
Thanks for the fast reply, that is almost exactly what I
need. The only other thing is I would need that friday
date to remain in the textbox until the following sunday
when that date would change again.

Any suggestions?

Thanks,
Ryan

 

[PC Datasheet]

Ryan,

Put the following into the control source property of a textbox:
=[MyDate]-(Format$([MyDate],"w")+1)

You can put any date into MyDate and the textbox will display the date of the
previous friday.

 

[Ryan]

I am trying to get this to work but for some reason it is
not. I set MyDate to equal Date(). All I get in the
textbox is 'Name#'. Is there something I am doing wrong?

Ryan

-----Original Message-----
Ryan,

Put the following into the control source property of a textbox:
=[MyDate]-(Format$([MyDate],"w")+1)

You can put any date into MyDate and the textbox will display the date of the
previous friday.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Here is what I am trying to do, it is kind of hard to
explain so try to bear with me.

I would like to display a certain date.
- On each Sunday I would like this date to display
the date of the Previous Friday.

For example:
Suppose this sunday is the 1st of the Month.
WHen Sunday comes around I would like the date to display
the last friday of the previous month.

When the next sunday roles around I would like the date to
now show Friday the 6th.

How would I create a date function that can do this?

Thanks in advance,
Ryan

.

 

[PC Datasheet]

Ryan,

To use Date(), remove the square brackets. The square brackets indicate a field
where Date() is a function and not a field.

=Date()-(Format$(Date(),"w")+1)

Steve
PC Datasheet

I am trying to get this to work but for some reason it is
not. I set MyDate to equal Date(). All I get in the
textbox is 'Name#'. Is there something I am doing wrong?

Ryan

-----Original Message-----
Ryan,

Put the following into the control source property of a textbox:
=[MyDate]-(Format$([MyDate],"w")+1)

You can put any date into MyDate and the textbox will display the date of the
previous friday.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Here is what I am trying to do, it is kind of hard to
explain so try to bear with me.

I would like to display a certain date.
- On each Sunday I would like this date to display
the date of the Previous Friday.

For example:
Suppose this sunday is the 1st of the Month.
WHen Sunday comes around I would like the date to display
the last friday of the previous month.

When the next sunday roles around I would like the date to
now show Friday the 6th.

How would I create a date function that can do this?

Thanks in advance,
Ryan

.

 

[tina]

i took PC's solution

=[MyDate]-(Format$([MyDate],"w")+1)

and changed the reference to today's date, as

=Date()-(Format$(Date(),"w")+1)

putting that expression in the textbox's ControlSource
property did work for me; see if it'll work for you.

and thanks, PC, that little formula is a lot easier than
the function i wrote to get the correct date!

-----Original Message-----
I am trying to get this to work but for some reason it is
not. I set MyDate to equal Date(). All I get in the
textbox is 'Name#'. Is there something I am doing wrong?

Ryan

-----Original Message-----
Ryan,

Put the following into the control source property of a textbox:
=[MyDate]-(Format$([MyDate],"w")+1)

You can put any date into MyDate and the textbox will display the date of the
previous friday.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Here is what I am trying to do, it is kind of hard to
explain so try to bear with me.

I would like to display a certain date.
- On each Sunday I would like this date to display
the date of the Previous Friday.

For example:
Suppose this sunday is the 1st of the Month.
WHen Sunday comes around I would like the date to display
the last friday of the previous month.

When the next sunday roles around I would like the

date

to


.

.