kristibigo
asked on
Access DatePart or DateAdd or Both?
We have in a table a "Created" date. We want to return a date reflecting only the previous workday. Meaning, if the date fell on a Sunday, we only want to see the results of the Friday's date. Therefore, whenever we run this query, it will reflect dates from only the weekday.
A couple things to note: We have it configured to a long date (showing the date, hour, and minutes). We have to have this long date feature for other reasons.
When we use the DateAdd or DatePart feature, we seem to get results in the year 1899 or 1900. What are we doing wrong?
This feature is needed so we can run reports daily to reflect the previous work day's productivity.
A couple things to note: We have it configured to a long date (showing the date, hour, and minutes). We have to have this long date feature for other reasons.
When we use the DateAdd or DatePart feature, we seem to get results in the year 1899 or 1900. What are we doing wrong?
This feature is needed so we can run reports daily to reflect the previous work day's productivity.
ASKER
We were hoping for something a little more simplified than having to use VBA. We were hoping to use something in the query itself - a function. e.g., DateDiff("d",DatePart("w", Date()-1,7 )>2),DateP art("w",Da te(),7)>2) (copied from something else we researched).
However, we don't want the Date difference (resulting in a number), we want the result to be the actual date.
That is, March 13 2011 - 1 (workday) = March 11, 2011 (or 3/11/2011) - Friday's date.
However, we don't want the Date difference (resulting in a number), we want the result to be the actual date.
That is, March 13 2011 - 1 (workday) = March 11, 2011 (or 3/11/2011) - Friday's date.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This still gives us dates from the 1899 year.
We tried your function in the Field section as follows:
Submitted: CDate(DateDiff("d",DatePar t("w",[Cre ated]-1,7) >2,DatePar t("w",[Cre ated],7)>2 ))
Because when we put the CDate(DateDiff("d",DatePar t("w",[Cre ated]-1,7) >2,DatePar t("w",[Cre ated],7)>2 )) in the Criteria, we didn't get any results.
We tried your function in the Field section as follows:
Submitted: CDate(DateDiff("d",DatePar
Because when we put the CDate(DateDiff("d",DatePar
ASKER
Sorry, we put the following in the Criteria:
CDate(DateDiff("d",DatePar t("w",Date ()-1,7)>2, DatePart(" w",Date(), 7)>2))
And this gave us zero results.
CDate(DateDiff("d",DatePar
And this gave us zero results.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Made error in accepting solution.
ASKER
Addendum: Upon further review, we discovered the most recent note criteria above does not give us the results we anticipated. However, the following did work when running data on Sunday:
Field: Created
Criteria: IIf(Weekday([Master_Pipeli ne].[Creat ed])=1,Dat eAdd("d",- 2,[Master_ Pipeline]. [Created]) ,IIf(Weekd ay([Master _Pipeline] .[Created] )=7,DateAd d("d",-1,[ Master_Pip eline].[Cr eated]),[M aster_Pipe line].[Cre ated])) And >=Date()-2 And <=Date()
Explanation: If Weekday equals Sunday, show Friday, If Weekday equals Saturday, show Friday, else show the Created date; And where date is greater or equal to two days ago And less or equal to today (to show only the most recent workday's production).
Please reply if you see any issues.
Field: Created
Criteria: IIf(Weekday([Master_Pipeli
Explanation: If Weekday equals Sunday, show Friday, If Weekday equals Saturday, show Friday, else show the Created date; And where date is greater or equal to two days ago And less or equal to today (to show only the most recent workday's production).
Please reply if you see any issues.
OK, I see now what you are after.
I guess it could be simplified a lot by having this criteria for [Master_Pipeline].[Created ]:
DateAdd("d",IIf(Weekday(Da te(),2)>5, 5-Weekday( Date(),2), 0),Date())
which just subtracts 1 or 2 from today's date should today be Saturday or Sunday. The trick is to use Monday as first day of the week.
/gustav
I guess it could be simplified a lot by having this criteria for [Master_Pipeline].[Created
DateAdd("d",IIf(Weekday(Da
which just subtracts 1 or 2 from today's date should today be Saturday or Sunday. The trick is to use Monday as first day of the week.
/gustav
ASKER
Problem Solved.
https://www.experts-exchange.com/questions/23601535/Calculate-a-date-x-business-days-before-a-given-date-no-weekends-no-holidays-Access.html?sfQueryTermInfo=1+10+30+access+busi+dai+previou+work