Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

date previous selection

Status
Not open for further replies.
kalpsv

kalpsv

Programmer
Jun 12, 2002
58
IN
I have to create a report.
First column is from 1.4 of previous year to 31.3. of
this year.
Next column is from 1.4 of this year to end of previous
month
for all this i am using only two fields
vouchdt yyyy-mm-dd
subscription currency

please help me
 
Sort by date Sort by votes
How about you create a couple of formulas like these:

//Last Year's April to Current Year's March

if {vouchdt} >= date(year(currentdate)-1,4,1) and
{vouchdt} <= date(year(currentdate),3,31)
then {field}

//Current Year's April to End of Last Month


if month(currentdate) in [4,6,9,11]
then
if {vouchdt} >= date(year(currentdate),4,1) and
{vouchdt} <= date(year(currentdate),month
(currentdate)-1,30)
then {field}
else
if month(currentdate) in [5,7,8,10,12]
then
if {vouchdt} >= date(year(currentdate),4,1) and
{vouchdt} <= date(year(currentdate),month(currentdate)-1,31)
then {field}

That do ya?

Naith
 
Upvote 0 Downvote
Oops.

I screwed up on you.

In the two places where I say:

&quot;if month(currentdate) in...&quot;

change it to:

&quot;if month(currentdate)-1 in...&quot;

Sorry about that,

Naith
 
Upvote 0 Downvote
If your &quot;date&quot; is actually an 8 digit integer in a YYYYMMDD format, try incorporating the NumbertoDate() function. This is available as a download on the crystal decisions website. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com
 
Upvote 0 Downvote
it didnot work:
i typed the following formula the err was &quot; A ) was missing&quot;
near year{ ppftxn.vouchdt} on the first line

if {PPFTXN.VOUCHDT} >= date(year{PPFTXN.VOUCHDT}-1,4,1) and
{PPFTXN.VOUCHDT} <= date(year{PPFTXN.VOUCHDT},3,31)
then {PPFTXN.SUBSCRIPTION}
and then I tried with current date then also it gives the same error
what is meant by current date
Pls help me

 
Upvote 0 Downvote
Try this:

if {PPFTXN.VOUCHDT} >= date(year({PPFTXN.VOUCHDT})-1,4,1) and {PPFTXN.VOUCHDT} <= date(year({PPFTXN.VOUCHDT}),3,31)
then {PPFTXN.SUBSCRIPTION}


Notice the extra pair of () around the year() function argument.
Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com
 
Upvote 0 Downvote
if i select a range of dates from VB 1.e. 1.4.2002
to 30.6.2002
then the figure is correct..Thank you
but if I select 1.4.2002 to 30.4.2002 the previous
month figures should be zero (subscription) because
when the report is generated for the month of april
then the figure of last month should not be refklected
because it goes to the previous year figures.
 
Upvote 0 Downvote
what is your formula for the previous month? Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com
 
Upvote 0 Downvote
This is my formula for previous month

//Current Year's April to End of Last Month
if month({PPFTXN.VOUCHDT}-1) in [4,6,9,11]
then
if {PPFTXN.VOUCHDT} >= date(year({PPFTXN.VOUCHDT}),4,1) and
{PPFTXN.VOUCHDT} <= date (year({PPFTXN.VOUCHDT}),month({PPFTXN.VOUCHDT}-1),30)
then
{@subs}
else
if month({PPFTXN.VOUCHDT})-1 in [1,3,5,7,8,10,12]
then
if {PPFTXN.VOUCHDT} >= date(year({PPFTXN.VOUCHDT}),4,1) and
{PPFTXN.VOUCHDT} <= date (year({PPFTXN.VOUCHDT}),month({PPFTXN.VOUCHDT}-1),31)
then
{@subs}
else
if month({PPFTXN.VOUCHDT})-1 in [2]
then
if {PPFTXN.VOUCHDT} >= date(year({PPFTXN.VOUCHDT}),4,1) and
{PPFTXN.VOUCHDT} <= date (year({PPFTXN.VOUCHDT}),month({PPFTXN.VOUCHDT}-1),28)
then
{@subs}
 
Upvote 0 Downvote
to Naith
your last year selection formula works fine
thank you for the same
but the data returned is zero
instead of value
the formula used by me is
//FORMULA FOR LAST YEAR
if {PPFTXN.VOUCHDT} in (date(year({PPFTXN.VOUCHDT}-1),04,01)) to
(date (year({PPFTXN.VOUCHDT}),month({PPFTXN.VOUCHDT}),28))
then
{TXN.SUBSCRIPTION}
KINDLY HELP ME SIR
 
Upvote 0 Downvote
I think you are making your formulas too tough, I always like to keep it simple:

//for the &quot;year&quot; ending 3/31/02
If {Datefield} in Date(2001,4,1) to date(2002,3,31) then {Amount} else 0

//for thear year ending 3/31/02
If {Datefield} in Date(2000,4,1) to date(2001,3,31) then {Amount} else 0

//record selection formula, which if you hide the details
//section you do not even have to have
{Datefield} in date(2000,4,1) to Date(2002,3,31)




Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com
 
Upvote 0 Downvote
I cannot hardcode the year 2001,2002
i need to have only the field vouchdt
either in selection formula or in CR
 
Upvote 0 Downvote
Hey Kal,

I lost track of this thread, which is why I appeared to have abandoned you there. Sorry! ;)

Can you post exactly what your current formula is, and comment on which section is giving you problems?

Thanks,

Naith
 
Upvote 0 Downvote
this was my question

I have to create a report.
First column is from 1.4 of previous year to 31.3. of
this year.
Next column is from 1.4 of this year to end of previous
month
for all this i am using only two fields
vouchdt yyyy-mm-dd
subscription currency

please help me

This was your reply

Naith (Programmer) Jun 12, 2002
How about you create a couple of formulas like these:

//Last Year's April to Current Year's March

if {vouchdt} >= date(year(currentdate)-1,4,1) and
{vouchdt} <= date(year(currentdate),3,31)
then {field}

//Current Year's April to End of Last Month


if month(currentdate) in [4,6,9,11]
then
if {vouchdt} >= date(year(currentdate),4,1) and
{vouchdt} <= date(year(currentdate),month
(currentdate)-1,30)
then {field}
else
if month(currentdate) in [5,7,8,10,12]
then
if {vouchdt} >= date(year(currentdate),4,1) and
{vouchdt} <= date(year(currentdate),month(currentdate)-1,31)
then {field}

That do ya?

Naith

then I had the following problem

if i select a range of dates from VB 1.e. 1.4.2002
to 30.6.2002
then the figure is correct..Thank you
but if I select 1.4.2002 to 30.4.2002 the previous
month figures should be zero (subscription) because
when the report is generated for the month of april
then the figure of last month should not be refklected
because it goes to the previous year figures.





 
Upvote 0 Downvote
Thanks, but I can already see what you and I first said at the beginning of this thread.

I was more after a copy of your formula as it stands at the moment.

Anyway, from what you've described, can you not put in a handler for April?

i.e. If minimum({?date range parameter}) >= date(year(currentdate),4,1) and maximum({?date range parameter}) <= date(year(currentdate),4,30) then 0
else
[insert formula I gave you].

Naith
 
Upvote 0 Downvote
This is for Naith
I am to have a report from 1st of April to the end of the
previous month i.e. upto March next year.
//Current Year's April to End of Last Month
If minimum({PPFTXN.VOUCHDT}) >= date(year({PPFTXN.VOUCHDT}),04,1) and
maximum({PPFTXN.VOUCHDT}) <= date(year({PPFTXN.VOUCHDT}),04,30) then 0
else


if month({PPFTXN.VOUCHDT}-1) in [6,9,11]
then
if {PPFTXN.VOUCHDT} >= date(year({PPFTXN.VOUCHDT}),04,01) and
{PPFTXN.VOUCHDT} <= date (year({PPFTXN.VOUCHDT}),month({PPFTXN.VOUCHDT}-1),30)
then
{@debits}
else
( if (month({PPFTXN.VOUCHDT}-1) in [1,3,5,7,8,10,12])
then
if ({PPFTXN.VOUCHDT}) >= (date(year({PPFTXN.VOUCHDT}),4,1)) and
({PPFTXN.VOUCHDT}) <= (date (year({PPFTXN.VOUCHDT}),month({PPFTXN.VOUCHDT}-1),31))
then
({@DEBITS}))

else
if month({PPFTXN.VOUCHDT})-1 in [2]
then
if {PPFTXN.VOUCHDT} >= date(year({PPFTXN.VOUCHDT}),4,1) and
{PPFTXN.VOUCHDT} <= date (year({PPFTXN.VOUCHDT}),month({PPFTXN.VOUCHDT}-1),28)
then
{@debits}



The above formula was used as suggested by you. the following problems are faced by me:
1.I am unable to have a subtotal after inserting the first
three lines I.e. min of vouchdt and max of vouchdt.
2. The formula works only for 6,8,10 where no of days
are thirty and for the reminaing 5,7,9 it does give me 0.
3. Am I correct in using the above formula for months 1,2,3.
Can you help me sir. I am suppose to deliver the project mid June and I am stuck with this report.
 
Upvote 0 Downvote
Kal,

I'm a bit confused by a couple of things:

Why have you replaced all the {currentdate}s with your fieldname? Put currentdate back in.

What do you mean by &quot;The formula works only for 6,8,10 where no of days are thirty&quot;. The months which have a maximum of 30 days are 6, 9, and 11. 4's excluded because it's April.

To total these formulas, you'll have to use formulas rather than the summary expert.

e.g.
//Formula1 -- adds up the total (detail section)
whileprintingrecords;
numbervar Sum_Form1;
Sum_Form1 := Sum_Form1 + {@FormulaName};

//Formula2 -- displays the total (group or report footer)
whileprintingrecords;
numbervar Sum_Form1;

//Formula3 -- resets the total (group or report header)
whileprintingrecords;
numbervar Sum_Form1 := 0;

Naith
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

T
  • Locked
  • Question
Date comparison Formula
Replies
2
Views
422
TPG_IT
T
D
  • Locked
  • Question
Comparing two Date Ranges
Replies
0
Views
268
doodledump
D
inkbird01
  • Locked
  • Question
Filters &gt; Record Selection for dates between
Replies
4
Views
286
pmax9999
pmax9999
PJ222
  • Locked
  • Question
is date within any date range in a dynamic array of date ranges
Replies
1
Views
155
PJ222
PJ222
ejastia
  • Locked
  • Question
date format spacing 1
Replies
4
Views
231
ejastia
ejastia

Part and Inventory Search

Sponsor

Back
Top