Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access DatePart or DateAdd or Both?

Posted on 2011-03-12
10
Medium Priority
?
848 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:kristibigo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
10 Comments
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 35117635
0
 

Author Comment

by:kristibigo
ID: 35117737
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),DatePart("w",Date(),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.
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 1000 total points
ID: 35118021
Just wrap your expression in CDate:

CDate(DateDiff("d",DatePart("w",Date()-1,7)>2),DatePart("w",Date(),7)>2))

/gustav
0
Application Discovery Service in AWS

In the era of the cloud, customers migrating away from their existing on-premise infrastructure. This requires lots of planning, strategies, and effort to identify their existing resources and determine how best to migrate.  Datacenter migrations happen in four phases -

 

Author Comment

by:kristibigo
ID: 35118322
This still gives us dates from the 1899 year.

We tried your function in the Field section as follows:

Submitted: CDate(DateDiff("d",DatePart("w",[Created]-1,7)>2,DatePart("w",[Created],7)>2))

Because when we put the CDate(DateDiff("d",DatePart("w",[Created]-1,7)>2,DatePart("w",[Created],7)>2)) in the Criteria, we didn't get any results.
0
 

Author Comment

by:kristibigo
ID: 35118331
Sorry, we put the following in the Criteria:

CDate(DateDiff("d",DatePart("w",Date()-1,7)>2,DatePart("w",Date(),7)>2))

And this gave us zero results.
0
 

Assisted Solution

by:kristibigo
kristibigo earned 0 total points
ID: 35118479
We figured it out. The solution would be to have the criteria set to >=DateAdd("w",-1,Date()) And <Date() .
0
 

Author Comment

by:kristibigo
ID: 35118509
Made error in accepting solution.
0
 

Author Comment

by:kristibigo
ID: 35123977
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_Pipeline].[Created])=1,DateAdd("d",-2,[Master_Pipeline].[Created]),IIf(Weekday([Master_Pipeline].[Created])=7,DateAdd("d",-1,[Master_Pipeline].[Created]),[Master_Pipeline].[Created])) 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.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35125728
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(Date(),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
0
 

Author Closing Comment

by:kristibigo
ID: 35154540
Problem Solved.
0

Featured Post

Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question