Formula for Dates Not Working
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22837
Printed Date: 19 Apr 2024 at 10:19am
Topic: Formula for Dates Not Working
Posted By: BoltzGirl
Subject: Formula for Dates Not Working
Date Posted: 15 Nov 2019 at 11:23am
I am using Crystal Reports 2013 v14
I created a report that I wanted to pull my data based on two different date scenarios.
1. the {TBL.ChgDateTime} is actually in my {@Beg date} {@End date}
2. the {TBL.ChgDateTime} if the {@InvDate} +5 days
I wrote it like this, using another formula for my InvDate that is:
If isnull ({TBL.InvDate}) then CurrentDate
So here is my Report Selection Criteria that is not working:
{TBL.ChgDateTime} in {@Beg date} to {@End date} and
({TBL.ChgDateTime} < {@InvDate} +5) and
{TBL.CHGFNC} like ['CHG INVOICE TYPE: TSS TO OGR','CHG INVOICE TYPE: POS TO TIN'
I get data when I eliminate the #2 step, but I need that criteria in this report as well. So I need to look at if the data falls within my Beg/End Dates AS WELL as if the data falls > my Invoice Date + 5 days.
TIA
-------------
Always appreciate the help!
|
Replies:
Posted By: kevlray
Date Posted: 15 Nov 2019 at 12:33pm
|
The code looks correct. But as you said. You need to see if the data works with the criteria.
|
Posted By: DBlank
Date Posted: 18 Nov 2019 at 8:13am
|
what are @beg and @end formulas?
|
Posted By: BoltzGirl
Date Posted: 18 Nov 2019 at 10:18am
Those are my beginning and end date formulas that are in my header.
BEGDATE =
dateVar EndMonth := Date(Year(DataDate),Month(DataDate),01)-1;
If {?BegDate} = Date(1800,01,01) Then
(Date(Year(EndMonth),Month(EndMonth),01))
else
if {?BegDate} = Date(1850,01,01) then
minimum(lastfullweek)
else
if {?BegDate} = Date(1900,01,01) then currentdate -1
else
If {?BegDate} = Date(1925,01,01) Then
dateadd('q',datediff('q',date(1900,1,1),today)-1,date(1900,1,1))
Else
{?BegDate}
ENDDATE =
If {?EndDate} = Date(1800,01,01) Then
Date(Year(DataDate),Month(DataDate),01)-1
else
if {?EndDate} = Date(1900,01,01) then currentdate - 1
else
if {?EndDate} = Date(1850,01,01) then
maximum(lastfullweek) else
If {?EndDate}= Date(1925,01,01) Then
Switch (Month(DataDate) in 1 to 3,Date(Year(DataDate),01,01),
Month(DataDate) in 4 to 6,Date(Year(DataDate),03,31),
Month(DataDate) in 7 to 9,Date(Year(DataDate),06,30),
Month(DataDate) in 10 to 12,Date(Year(DataDate),09,30))
Else
{?EndDate}
-------------
Always appreciate the help!
|
Posted By: DBlank
Date Posted: 18 Nov 2019 at 10:36am
Really hard to tell as I don't know the content of your data but my guess is that you are making the two criteria actually conflict in a way that no row will meet both conditions.
place all of the fields into the detail section
{TBL.ChgDateTime}
{@Beg date}
{@End date}
({@InvDate} +5)
-- the @Beg and @End will change on your input param so try multiple options
Query the data with no selection criteria and place and look at the rows to see how your formulas are returning values for each row to see if your selection criteria is valid across both options.
|
|