| Author |
Message |
Spartanx117
Newbie
Joined: 05 Dec 2012
Online Status: Offline
Posts: 5
|
  
Topic: Need a date formula
Posted: 05 Dec 2012 at 8:10am |
|
I am trying to make my current reports fill the information
needed automatically with a just a click of the refresh button, but I'm not
there yet. I have to change my date values either daily for some fields, weekly,
or monthly. Here is the following of what I would like my code to do.
If
its the 1st of a month, then pull previous months data, else pull current month.
(Right now I would not be worried about a circumstance where the last of the
month ended on a friday, and then started back at work on a monday, where the
1st of the month day would of pulled data from friday for the final month
report, but didnt because of how the date fell, this could be tackled later or
now depending on how bad the code is.)
I also have a code that pull
previous workers data, so a Date()-1 for any day that wasn't monday, and if
monday then Date()-3. I know the above date function syntax isnt valid, just
used it as an example.
Also if there was a way to right the Date- code
above in access for an if statement(I pull a single report from access that also
requires this) with their syntax that would be appreciated.
I am using
crystal reports 2011, and access 2010.
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Dec 2012 at 8:18am |
request 1
in the select expert use
datepart("d",currentdate)=1 and table.datefield in lastfullmonth
or
datepart("d",currentdate)>1 and table.datefield in MTD
I don't understand your other requests exactly...but maybe...
datepart("w",currentdate)=2 and daetdiff('d',table.datefield,currentdate)=3
or
datepart("w",currentdate)<>2 and daetdiff('d',table.datefield,currentdate)=1
Not sure at all what you are asking for the 3rd thing
Edited by DBlank - 05 Dec 2012 at 8:18am
|
| IP Logged |
|
Spartanx117
Newbie
Joined: 05 Dec 2012
Online Status: Offline
Posts: 5
|
Posted: 05 Dec 2012 at 9:17am |
I'll try the request 1 code after replying to you. The second option was just a formula to show the previous day work data. So if tuesday show monday. if monday show friday. if wednesday show tuesday and etc. The 3rd request was for the code for request 2 to be written in microsoft access syntax(if known) because crystal syntax and access syntax are rarely the same. Thanks for your reply and I will get back to you on the results
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Dec 2012 at 9:24am |
datepart("w",currentdate)=2 and daetdiff('d',table.datefield,currentdate)=3
or
datepart("w",currentdate)<>2 and daetdiff('d',table.datefield,currentdate)=1
if used in the select expert would pull only the days data you wanted.
|
| IP Logged |
|
Spartanx117
Newbie
Joined: 05 Dec 2012
Online Status: Offline
Posts: 5
|
Posted: 06 Dec 2012 at 10:03am |
Im sure you already know you nailed it and its working the way I want. Thank you so much. To address the problem where a month would fall like this one date wise, would my following thinking be correct on this issue. I came in on monday the 3rd, and needed Nov 1-30 Data, but because of how the weekend fell, Id have to manually change the code to include the month of nov my self. Would I just implement If\Then statements with your code to attack that issue? Im not to sure on how to implement it yet, but I wanted to make sure my wheels were turning in the right direction. (The manual entry of the date parameter is easy, im just trying to eliminate any and all work! ) thanks
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Dec 2012 at 10:13am |
so you want to tweak the month one to be:
if the first monday iof the month falls on happens on the 1st, 2nd, or 3rd day of the month then pull the last month data other wise pull this months data?
|
| IP Logged |
|
Spartanx117
Newbie
Joined: 05 Dec 2012
Online Status: Offline
Posts: 5
|
Posted: 11 Dec 2012 at 8:13am |
|
Yes that it was I would like it to do. Would the forming of it be as follows, if w = 2 and d is 1 or 2 or 3 then pull last month, else current month while using datepart into datediff? Obviously i dunno if that code could or would work or how to write it, but is that the basis of it?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Dec 2012 at 9:00am |
I think this will work assuming you never run the report on a saturday
(datepart("d",currentdate)=1 and {table.datefield} in lastfullmonth)
or
(weekday(currentdate)=2 and datepart("d",currentdate)<=3 and {table.datefield} in lastfullmonth)
or
(datepart("d",currentdate)>3 and and {table.datefield} in MonthToDate)
or
(weekday(currentdate)>2 and datepart("d",currentdate)>1 and {table.datefield} in MonthToDate)
|
| IP Logged |
|
|
|
