Help With Date Value for Saturday

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have looked everywhere, and cannot find what I'm looking for, Hope someone
here can help me.
I have a form which ask for a date, I would like to take that date and add
the amount of days needed to get to Saturday, and put that date in another
Text Box.
If date entered is 11/02/2007 it would appear in another text box as
11/06/2007
11/03/2007 would be 11/06/2007 I know Saturday is day 6, but I can't
figgure out
how to make it work. Is it possible?
What I want is to get all records for a week which ends on Saturday, I have
a report which calls all records with Sat. date. I also don't want the user
to have to enter 2 dates as the between function does.
Thanks for any help
Roger
 
If date entered is 11/02/2007 it would appear in another text box as
11/06/2007
11/03/2007 would be 11/06/2007 I know Saturday is day 6, but I can't
figgure out
how to make it work. Is it possible?
What I want is to get all records for a week which ends on Saturday, I have
a report which calls all records with Sat. date. I also don't want the user
to have to enter 2 dates as the between function does.
Click to expand...

If that's the only reason to change the date in the table... don't. You can
still use BETWEEN with only one prompt:

BETWEEN DateAdd("d", - Weekday([Enter date:]), [Enter date:]) AND DateAdd("d",
8 - Weekday([Enter date:]), [Enter date:])

John W. Vinson [MVP]
 
John W. Vinson said:
If date entered is 11/02/2007 it would appear in another text box as
11/06/2007
11/03/2007 would be 11/06/2007 I know Saturday is day 6, but I can't
figgure out
how to make it work. Is it possible?
What I want is to get all records for a week which ends on Saturday, I have
a report which calls all records with Sat. date. I also don't want the user
to have to enter 2 dates as the between function does.
Click to expand...

If that's the only reason to change the date in the table... don't. You can
still use BETWEEN with only one prompt:

BETWEEN DateAdd("d", - Weekday([Enter date:]), [Enter date:]) AND DateAdd("d",
8 - Weekday([Enter date:]), [Enter date:])

John W. Vinson [MVP]
Click to expand...

Thanks John
What I have is a table with 2 different dates one date is Saturday each week
The Other is the Actual day of the week. the event took place. I want to be
able to enter the actual date and have the end of week date in another text
box or directly into the table, both dates are used in a report.
 
Thanks John
What I have is a table with 2 different dates one date is Saturday each week
The Other is the Actual day of the week. the event took place. I want to be
able to enter the actual date and have the end of week date in another text
box or directly into the table, both dates are used in a report.
Click to expand...

If the Saturday date can always be derived from the other date field then it
*should not exist* in your table, period! You can display the next-Saturday
date in a textbox on a Form or Report by using a Control Source:

=DateAdd("d", Weekday([datefield]) - 7, [datefield])

This can also be used as a calculated field in a query if you want to sort or
search by it.

John W. Vinson [MVP]
 
John W. Vinson said:
Thanks John
What I have is a table with 2 different dates one date is Saturday each week
The Other is the Actual day of the week. the event took place. I want to be
able to enter the actual date and have the end of week date in another text
box or directly into the table, both dates are used in a report.
Click to expand...

If the Saturday date can always be derived from the other date field then it
*should not exist* in your table, period! You can display the next-Saturday
date in a textbox on a Form or Report by using a Control Source:

=DateAdd("d", Weekday([datefield]) - 7, [datefield])

This can also be used as a calculated field in a query if you want to sort or
search by it.

John W. Vinson [MVP]
Click to expand...
Thanks John
I didn't think it would be a good idea, but it would save mistakes
 
Back
Top