Report In Date Ranges
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9359
Printed Date: 28 Apr 2024 at 9:23pm
Topic: Report In Date Ranges
Posted By: paul_birm
Subject: Report In Date Ranges
Date Posted: 11 Mar 2010 at 2:59am
|
Hi ALL members...
I am running CR ver 6, 7 and 9 and need to write a report in ver6 (
 ) that pulls some data for specific dates.ie all sales between 01 Jan 2010 and 31 Jan 2010.
is there a way that i can get my user to input those specific dates....
there MUST be somehow...
all help appreciated.
Many thanks
Paul
Birmingham UK
 ------------- Paul Birmingham England {A friend in need is a pain in the a***} |
Replies:
Posted By: razzak
Date Posted: 11 Mar 2010 at 3:27am
|
create two date time parameters
go to select expert
e.g
write formula like .
({table1.photo_shot_date}>=CDate({?photo shot date from})
and {table1.photo_shot_date}<=CDate({?Photo shot date to})
this might solve your problem. ------------- Razzak Sayyed |
Posted By: paul_birm
Date Posted: 11 Mar 2010 at 7:16am
|
Thanks Razzak
I have a formulae:-
({salesorders.sdate}>=Cdate({?Report Date From})
and {salesorders.sdate}<=Cdate({Report Date End}) and when I Try to save the forumula, i get the CR error
"A Number, Currency Amount, Boolean or String is expected Here"
any more ideas please?
Paul
------------- Paul Birmingham England {A friend in need is a pain in the a***} |
Posted By: jkwrpc
Date Posted: 11 Mar 2010 at 1:27pm
|
Could be a data typing issue. I would suggest making sure your parameters have been correctly typed as a date. If you database for some reason has the date as a string then you would need to convert your parameter from a date to a string. I believe your solution is in matching these correctly.
Hope this is of some help
Regards,
John W.
http://www.CustomReportWriters.net - www.CustomReportWriters.net
|
Posted By: paul_birm
Date Posted: 12 Mar 2010 at 12:44am
|
Thanks John
my datatype on sdate field on salesorders table is 'datetime' so should be ok ???
any other ideas please people...  ------------- Paul Birmingham England {A friend in need is a pain in the a***} |
Posted By: paul_birm
Date Posted: 12 Mar 2010 at 12:55am
|
ok, to summerise, I am running CR ver 6.
I want a report to be run between two dates ( equivlent to a field called sdate in my CR)
i hve gone into Select Expert and added a NEW item so, it reads...
Salesorders.sdate is (1st dropdown) formula (2nd dropdown) and the formula is thus...
({salesorders.sdate}>=Cdate({?Report Date From})
and {salesorders.sdate}<=Cdate({Report Date End}) i click on OK and get the error ( no number) "A Number, currency amount, boolean or string is expected here"
i have checked my ERP system datatypes and thy are set to datetime.
Please, Please, Please help. I really need this report ....
thanks in advance
------------- Paul Birmingham England {A friend in need is a pain in the a***} |
Posted By: DBlank
Date Posted: 12 Mar 2010 at 4:58am
|
Never used 6 so I am guessing here.
In vXI I would bypass the select expert GUI and just use the statement:
{salesorders.sdate} in Cdate({?Report Date From}) to Cdate({?Report Date End})
If you still get the error where is the cursor indicating the expected # to be?
|
Posted By: razzak
Date Posted: 14 Mar 2010 at 7:43pm
|
Please remove Cdate() and try
e.g
({salesorders.sdate}>={?Report Date From}
and {salesorders.sdate}<={?Report Date End}) try This. ------------- Razzak Sayyed |
Posted By: paul_birm
Date Posted: 15 Mar 2010 at 1:01am
|
thanks Razzak
however, when in select expert gui, go into new and then make salesorders.sodate...is...formula with the following formula
({salesorders.sdate}>={?Report Date From}
and {salesorders.sdate}<={?Report Date End}) (exact copy and paste) i now get the error ... THis field name is not known the cursor stays where ever i last left it so i cant see WHICH fileld is Not KNOWN...
 getting really fed up with this now..... ------------- Paul Birmingham England {A friend in need is a pain in the a***} |
Posted By: kevlray
Date Posted: 15 Mar 2010 at 7:59am
| Interesting, so CR cannot find either {salesordes.sdate}, {?Report Date From} or {?Report Date End}. I would double check the spelling of each of these (especially the parmeters). I have sometimes had to resort to deleting my parameters and recreating them because I had somehow gotten a 'hidden' character in the name. |
Posted By: paul_birm
Date Posted: 16 Mar 2010 at 3:54am
|
Originally posted by kevlray
Interesting, so CR cannot find either {salesordes.sdate}, {?Report Date From} or {?Report Date End}. I would double check the spelling of each of these (especially the parmeters). I have sometimes had to resort to deleting my parameters and recreating them because I had somehow gotten a 'hidden' character in the name. Thanks for that, have check and DOUBLE checked that the tables are spelt correctly. Have also tried this on a machine with CR9 and get the same error. have also sorted some data in SQL QA and yep THAT works on the salesorders.sdate
ok, to summerise AGAIN.
i) I have my report and it pulls back 'lots' of data
ii) I click on SELECT EXPERT ( fingers and Marbles icon)
iii) select the New Tab
iv) select 'salesorders.sdate' from the field chooser box
v) make the two drop-down boxes read IS and FORMULA
vi) put the following sql into the formual box
({salesorders.sdate}>=Cdate({?Report From})
and {salesorders.sdate}<=Cdate({?Report End}) vii) click on OK
Viii) get the error :-
"A number, currency amount, boolean or string is expected here"
ix) click on the OK button
X) the cursor is still flashing after the last character where it was when i clicked on teh OK button in vii
!!!!
 ------------- Paul Birmingham England {A friend in need is a pain in the a***} |
Posted By: jkwrpc
Date Posted: 16 Mar 2010 at 4:25am
|
One last thing and I know you said the parameter is set to datatype datetime, change it to simply a type 'date'.
I had a similar problem a while back and this was the fix in my case. It is the reason I offered it before.
Regards,
John W.
http://www.CustomReportWriters.net - www.CustomReportWriters.net
|
Posted By: DBlank
Date Posted: 16 Mar 2010 at 4:57am
|
You are also missing an end parenth in the statement from your last post:
({salesorders.sdate}>=Cdate({?Report From})
and {salesorders.sdate}<=Cdate({?Report End})) |
Posted By: paul_birm
Date Posted: 16 Mar 2010 at 6:18am
 yeah thanks
i noticed the missing ). however it WAS in the formalua and it STILL doesnt work !
Thanks
Paul
------------- Paul Birmingham England {A friend in need is a pain in the a***} |
Posted By: paul_birm
Date Posted: 16 Mar 2010 at 6:26am
|
Originally posted by jkwrpc
John,
One last thing and I know you said the parameter is set to datatype datetime, change it to simply a type 'date'. I had a similar problem a while back and this was the fix in my case. It is the reason I offered it before.
Regards,
John W.
http://www.CustomReportWriters.net - www.CustomReportWriters.net I cant change the data type - is an ERP System thats running on an SQL dbase. this Crystal Report only enquires on it - I am not allowed to change the Datatypes.
HOWEVER, I did try changing the datatypes on the test environment but that didnt work either !!!
------------- Paul Birmingham England {A friend in need is a pain in the a***} |
Posted By: paul_birm
Date Posted: 23 Mar 2010 at 2:02am
|
i have the chance to 'upgrade' this report to CR Ver 9 - does any body know if this will help at all ????? ------------- Paul Birmingham England {A friend in need is a pain in the a***} |
Posted By: DBlank
Date Posted: 23 Mar 2010 at 3:58am
|
Maybe.
FYI-I think that John was telling you to change the data type for the parameter not the DB field.
I would first start with breaking this into parts to see if you can tease out what it is choking on.
Try and make it just equal to one parameter.
{salesorders.sdate}={?Report From} If it chokes try
{salesorders.sdate}=cdate({?Report From})
if it chokes try make it = one actual date.
{salesorders.sdate}=date(2010,1,1)
See if you can get any of these to work and build out from there.
You should still end up with something like:
{salesorders.sdate} in Cdate({?Report From}) to Cdate({?Report End})
but trying it part by part might help you figure out where it is going wrong. |
Posted By: jkwrpc
Date Posted: 23 Mar 2010 at 4:02am
|
The change to the datatype is not in the database, it is in the parameter on the report.
For some reason my problem was resolved when I changed the CR parameter type to Date from DateTime. When I was using the CDate conversion in the formula it did not work. You would need to edit your CR parameter to make the change. Also I don't know if CR V9 does this but in the later versions when there is an error in the formula the formula editor opens up. If you look to the left in the tree window you can see the values being passed through the formula. If that is happening in V9 you can see the data and that may help troubleshoot the issue. Apart from those two things, I am out of ideas. If you are able to get it resolved let us know the solution so we can all learn. Sorry I could not be of more help. Regards, John W. www.CustomReportWriters.net |
Posted By: paul_birm
Date Posted: 23 Mar 2010 at 6:00am
|
great, thanks will give that a try !
Paul ------------- Paul Birmingham England {A friend in need is a pain in the a***} |
Posted By: paul_birm
Date Posted: 23 Mar 2010 at 10:55pm
|
ok, Firstly, DBlank & John W - thanks for those answers...
the data type of the field on the report is already set to date.
Dblank, from your suggestions, this...
i) {salesorders.sdate}={?Report From} - returned error "This field name is not known"
ii) {salesorders.sdate}=cdate({?Report From}) - returned error "A Number, Currency, boolean or string is expected here
iii) {salesorders.sdate}=date(2010,1,1) - Hey This Worked ... mmmmm!!!
[ I can also get it to work on > 2010,1,1 etc..]
iv) {salesorders.sdate} in Cdate({?Report From}) to Cdate({?Report End}) - this threw up the same error as in ii)
so we are definately getting somewhere, but I am not sure where we are getting .....
 any more suggestions.... PLEASE !!!
{salesorders.sdate} in date({?From}) to date({?To}) ------------- Paul Birmingham England {A friend in need is a pain in the a***} |
Posted By: paul_birm
Date Posted: 23 Mar 2010 at 11:23pm
|
AHA!!!!
I've had a Eureka Moment - had a look at my Parameter fields and there are BOTH spelt wrong! !!!
doh!
so when I got the error "This field is not known" that SHOULD have been a nudge for me
 ------------- Paul Birmingham England {A friend in need is a pain in the a***} |
Posted By: paul_birm
Date Posted: 24 Mar 2010 at 12:11am
|
Many thanks to all those who helped me on this incident regards
Paul ------------- Paul Birmingham England {A friend in need is a pain in the a***} |