Date must end on a weekday 3

[qster]

Does anyone know the formula or Date function to use at future date 30 days?
The key here is that the last date or day 31 must be a weekday and not a weekend date.

i.e. The start date can be anydate, but after 30 days, the next date showing must be a weekday.

 

[PWise]

i have in almost all my databases a digits table just 1 field with numbers from 1 till 10000.....

so this is what i will do

SELECT TOP 1 Min(Date()+30+[DigitID]) AS FirstWeekDay
FROM Digits
GROUP BY Digits.DigitID
HAVING (((Digits.DigitID)<=3) AND ((DatePart("w",Date()+30+[DigitID])) Between 2 And 6));

 

[qster]

I put in the basic:
=DateAdd("w",30,[StartDate])

where StartDate is date that is inputted to future date 30 days.

Now.. where would I place this code?
As an Expression or Code in MS Access?

 

[PWise]

the code that i gave you will be a msaccess query
where to put this depends on
where do you need this

on a form ,report ect..

 

[qster]

I have it on a MS Access Form right now.
There is no Table data or queries.

Would I have to import data into tables?

 

[lespaul]

ok, you are going to add 30 days (if you change the "w" to a "d" - the way you have it, it's going to add 30 weeks to the date entered!) to the StartDate, but there's no guarantee that it will return a weekday. You'll need an additional check to see if it's M - F.





Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[qster]

It works with either "d" (day) or "w" (weekday) right now.

"ww" is week

 

[lespaul]

my bad!

 

[PWise]

is this the control source of control on a form
if so create a table called Digits
add a field called Digitid type number and set it as the primary key save it and add numbers 1,2,3

create a new query and switch to sql view and paste this sql statement

SELECT  Min(forms!formname!startdate+30+[DigitID]) AS FirstWeekDay
FROM Digits
GROUP BY Digits.DigitID
HAVING (((Digits.DigitID)<=3) AND ((DatePart("w",forms!formname!startdate+30+[DigitID])) Between 2 And 6));

and save this as qryFirstWeekDay

and put this in the control source of the control

=dmin("FirstWeekDay","qryFirstWeekDay")

 

[qster]

Yes, this is a form.
I tried and created a table named "Tbl_Digits"
Added a Field Named "DigitID" and put type as a "Number" and set as a primary key. then I added 1, 2 and 3 in the table.

Created a new SQL query:

SELECT Min(forms!"FutureDates"![StartDate]+30+[DigitID]) AS FirstWeekDay
FROM Tbl_Digits
GROUP BY Tbl_Digits.DigitID
HAVING (((Tbl_Digits.DigitID)<=3) And ((DatePart("w",forms!["FutureDates"]!startdate+30+[DigitID])) Between 2 And 6));

and named the query "qryFirstWeekDay"
Then put in the control source of the control for the From:

=dmin("FirstWeekDay","qryFirstWeekDay")

Unfortunately, I still get an #Error output.
Any suggestions? or am I typing in something wrong?

 

[CautionMP]

qster,
[tt][StartDate] + 30 + Choose(Weekday([StartDate] + 30), 1, 0, 0, 0, 0, 0, 2)[/tt]?

It will work as the control source in a form or as a calculated field in a query.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[PWise]

take the quotes """" off forms!"FutureDates"![StartDate]


forms!FutureDates![StartDate]

 

[PWise]

Great post CautionMP
erans a Star

Pwise

 

[qster]

CautionMP,
This formula works great.
Question, would I use the same formula or something close to it if I want to expand the criteria to be a year instead of 30 days? Keep in mind of any leap years with 366 days instead of the normal 365.

 

[qster]

Pwise,
Thanks for noticing the quotes. I have removed them and the query works now. However for some strange reason it doesn't show the dates unless I go to Form Desisn and back to Form view. Odd.

Also... how would I change the query to use a 1 year range instead of a 30 day?

Thanks in advance

 

[CautionMP]

qster
Yes.
[tt]DateAdd("yyyy",1,[StartDate]) + Choose(Weekday(DateAdd("yyyy",1,[StartDate])), 1, 0, 0, 0, 0, 0, 2)[/tt]

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[qster]

Thank you again.

Here is another one..
Say I can't use specific months. (i.e. Winter months) and I want to be able to calculate a year from a date, but it doesn't include Dec., Jan. and Feb. So a year from a date (say Sep. 1, 2006) futuring dating it for a year should return Mar. 1, 2008 as it skips out on Dec., Jan. and Feb of 2007. Cant this be done?

Also would I have to create separate codes to account for holidays that fall on a workday? Of if it falls on a weekend and the following Monday is the holiday.

 

[CautionMP]

qster,
Yes, anything can be done if you can determine the logic for it.

So far all the solutions have been centered around calculations using the Expression Builder and built-in functions which can easily be added as the control source for query and form fields.

When you start talking about calculations of increasing compexity you could probably keep going down the built-in function route, but I think you need to look at creating your own user defined function in VBA.

I would recomend doing some searches in Microsoft: Access Modules (VBA Coding) Forum to see what others have done along these lines, and if you get stuck post your questions there.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)