Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Formula for Dates Not Working Post Reply Post New Topic
Author Message
BoltzGirl
Groupie
Groupie
Avatar

Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
Quote BoltzGirl Replybullet Topic: Formula for Dates Not Working
    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!
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 18 Nov 2019 at 8:13am
what are @beg and @end formulas?
IP IP Logged
BoltzGirl
Groupie
Groupie
Avatar

Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
Quote BoltzGirl Replybullet 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!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.048 seconds.