Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Data Connectivity
Message Icon Topic: Report In Date Ranges Post Reply Post New Topic
<< Prev Page  of 3 Next >>
Author Message
paul_birm
Newbie
Newbie
Avatar

Joined: 10 Jul 2008
Online Status: Offline
Posts: 25
Quote paul_birm Replybullet 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
 
!!!!
 
Shocked
 
 
Paul
Birmingham
England
{A friend in need is a pain in the a***}
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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}))
IP IP Logged
paul_birm
Newbie
Newbie
Avatar

Joined: 10 Jul 2008
Online Status: Offline
Posts: 25
Quote paul_birm Replybullet Posted: 16 Mar 2010 at 6:18am
Wink yeah thanks
i noticed the missing ).  however it WAS in the formalua and it STILL doesnt work !
 
 
Thanks
 
Paul
Shocked
Paul
Birmingham
England
{A friend in need is a pain in the a***}
IP IP Logged
paul_birm
Newbie
Newbie
Avatar

Joined: 10 Jul 2008
Online Status: Offline
Posts: 25
Quote paul_birm Replybullet Posted: 16 Mar 2010 at 6:26am
Originally posted by jkwrpc

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.
John,
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. Wink
 
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***}
IP IP Logged
paul_birm
Newbie
Newbie
Avatar

Joined: 10 Jul 2008
Online Status: Offline
Posts: 25
Quote paul_birm Replybullet 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***}
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.


Edited by DBlank - 23 Mar 2010 at 3:59am
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet 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
IP IP Logged
paul_birm
Newbie
Newbie
Avatar

Joined: 10 Jul 2008
Online Status: Offline
Posts: 25
Quote paul_birm Replybullet Posted: 23 Mar 2010 at 6:00am
great, thanks will give that a try !
 
PaulWink
Paul
Birmingham
England
{A friend in need is a pain in the a***}
IP IP Logged
paul_birm
Newbie
Newbie
Avatar

Joined: 10 Jul 2008
Online Status: Offline
Posts: 25
Quote paul_birm Replybullet 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 ..... Confused
 
 
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***}
IP IP Logged
<< Prev Page  of 3 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.