Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: yeartodat formula Post Reply Post New Topic
Page  of 2 Next >>
Author Message
deancast
Newbie
Newbie


Joined: 20 Jul 2010
Online Status: Offline
Posts: 6
Quote deancast Replybullet Topic: yeartodat formula
    Posted: 20 Jul 2010 at 4:56am

I am building a report for a fire department that consists of some data about the hydrant testing. I want the list to show each of the hydrants and only there last test date and results.

This is the current formula i am using, but when I put this in the detail section it retuens all the test dates for each of the hydrants.
 
if Date({FlowTest.DATE}) <> date(1900,01,01) then totext(Date({FlowTest.DATE})) else ''
 
I only want it to return the most recent test date and data
 
Any help greatly appreciated
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Jul 2010 at 5:06am
if it is just an issue of viewing
Your select statmetn would be
{FlowTest.DATE} in yeartodate
 
group on the hydrant identifyer field (unique record per hydrant)
sort descending on flowdate
palce the fields you want to view in group header
suppress details
IP IP Logged
deancast
Newbie
Newbie


Joined: 20 Jul 2010
Online Status: Offline
Posts: 6
Quote deancast Replybullet Posted: 23 Jul 2010 at 7:44am
Ok, that worked but now let me go one step further. We need for the report to prompt for a year ie 2009 and then return the data of the hydrants only done for that year.
So I tried this but I get an error when checking formula
if Year ({FlowTest.DATE})= {?Date} then totext (Date({FlowTest.Date})) else ''
{?Date} is the prompt field
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Jul 2010 at 8:00am
depends on wht type of field your param  {?Date} is.
Also try not to use if then statemetn in your select expert. It just wants a boolean result
so if your para is a date type
year({?date})=year({flowtest.date})
 
if the param is numeric make sure you do a min value that forces teh user to enter the lead 19xx or 20xx.
{?date}=year({flowtest.date})
IP IP Logged
deancast
Newbie
Newbie


Joined: 20 Jul 2010
Online Status: Offline
Posts: 6
Quote deancast Replybullet Posted: 23 Jul 2010 at 8:31am
When I use
year({?date})=year({flowtest.date}) in the selectexpert formula it tells me it needs a date value for {?Date}
Thats when message i get when trying to save the fomula
All I really want it the parm to prompt for the year then retune the hydrants that were tested in that year
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Jul 2010 at 8:39am
what data type is your date param?
and what values are you letting the users enter?


Edited by DBlank - 23 Jul 2010 at 8:39am
IP IP Logged
deancast
Newbie
Newbie


Joined: 20 Jul 2010
Online Status: Offline
Posts: 6
Quote deancast Replybullet Posted: 23 Jul 2010 at 8:46am
date type
All I want then to do is enter 2009 for example to list ALL the hydrants tested in 2009  etc.....
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Jul 2010 at 9:04am
if it were a date type you would not be geeting the error you posted.
I think you did it a string and let them type in 2009.
if so try
tonumber(?date}=year({flowtest.date})
IP IP Logged
deancast
Newbie
Newbie


Joined: 20 Jul 2010
Online Status: Offline
Posts: 6
Quote deancast Replybullet Posted: 23 Jul 2010 at 10:08am

I have a parm called Date, it is set as string but when I try to aded

tonumber({?date}=year({flowtest.date})) to the select expert formula it tells me A Date is required her when I try to save it. Remember the field FlowTest.date is a date type field which i can not chang
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Jul 2010 at 10:29am
sorry the parenths were wrong
tonumber({?date})=year({flowtest.date})


Edited by DBlank - 23 Jul 2010 at 10:29am
IP IP Logged
Page  of 2 Next >>
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.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.