Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Command retuns nothing when using a date parameter Post Reply Post New Topic
Author Message
Chronicide
Newbie
Newbie


Joined: 19 Jul 2011
Online Status: Offline
Posts: 10
Quote Chronicide Replybullet Topic: Command retuns nothing when using a date parameter
    Posted: 19 Jun 2012 at 4:21am
Hey everyone,
 
I have a very simple report that get's it data from an MS SQL 2005 Server via a Crystal Report command:
 
SELECT COUNT(*) FROM Files_ WHERE DATE_Submitted IS NOT NULL
 
This works fine, and the results are the same whether I run the report or if I run the SQL on the server directly. My issue is that the moment I try to add a date paremeter, the command returns 0 as the count. If I hardcode dates in, it works fine... but data parameters just don't work.
 
SELECT COUNT(*) FROM Files_ WHERE DATE_Submitted BETWEEN '2011/01/01' AND '2012/12/31'
 
This returns '42' in both the report and against the SQL database directly.
 
SELECT COUNT(*) FROM Files_ WHERE DATE_Submitted BETWEEN '{?Start}' AND '{?End}'
 
This causes an error: failed to retreive data, incorrect syntax near '2012'
 
SELECT COUNT(*) FROM Files_ WHERE DATE_Submitted BETWEEN {?Start} AND {?End}
 
This doesn't cause an error, but it returns '0' for the count
 
SELECT COUNT(*) FROM Files_ WHERE DATE_Submitted BETWEEN CONVERT(VARCHAR,{?Start},111) AND CONVERT(VARCHAR,{?End},111)
 
Here I was trying to force the date parameters into strings, but it also returned '0' for the count.
 
Nothing I've tried works. Does anyone know how to get this to work... Even using strings for parameters doesn't work. Again, if I hardcode the data values in the command, it works, but I need these values to be parameters... I would greatly appreciate any help that you guys could offer. Thanks!
 
- Scott
 
 


Edited by Chronicide - 19 Jun 2012 at 4:22am
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 19 Jun 2012 at 4:33am

Do one of the following;

Remove the ' from the sql command, ie;
 
SELECT COUNT(*) FROM Files_ WHERE DATE_Submitted BETWEEN {?Start} AND {?End}
 
OR
 
Make the datatype of your parameters, "string", that way you can leave the SQL statement as is.
 
Regards,
Ryan.


Edited by rkrowland - 19 Jun 2012 at 4:33am
IP IP Logged
Chronicide
Newbie
Newbie


Joined: 19 Jul 2011
Online Status: Offline
Posts: 10
Quote Chronicide Replybullet Posted: 19 Jun 2012 at 4:36am
Your first suggestion was the third example in my OP... which returns 0 as the count... using the string parameters was also mentioned near the bottom, and this too returned 0. What is odd is that if I hardcode the dates in the command, it works, but when I use string parameters with the same values and in the same format as the hardcoded version it returns 0.
 
To be more specific, I've tried both of the following commands with both date and string parameters:
 
SELECT COUNT(*) FROM Files_ WHERE DATE_Submitted BETWEEN '{?Start}' AND '{?End}'
 
SELECT COUNT(*) FROM Files_ WHERE DATE_Submitted BETWEEN {?Start} AND {?End}
 
These either fail, or return 0. Hardcoding the values:
 
SELECT COUNT(*) FROM Files_ WHERE DATE_Submitted BETWEEN '2011/01/01' AND '2012/12/31'
 
returns 42... as it should.


Edited by Chronicide - 19 Jun 2012 at 4:41am
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 19 Jun 2012 at 9:52pm
Try the following, create 2 parameters, one string and one date.
 
Place this in your command;
 
SELECT
'{?str}' as 'String quoted',
{?str} as 'String unquoted',
'{?date}' as 'Date qutoed',
{?date} as 'Date unquoted'
 
Enter a date for each parameter and then preview them on your report - something is obviously not working correctly as I do this on a daily basis without any problems.
 
Both the {?date} and the '{?str}' should display what you need.
 
Thanks,
Ryan.


Edited by rkrowland - 19 Jun 2012 at 9:53pm
IP IP Logged
Chronicide
Newbie
Newbie


Joined: 19 Jul 2011
Online Status: Offline
Posts: 10
Quote Chronicide Replybullet Posted: 20 Jun 2012 at 4:53am
Hi there, thanks for your response.
 
I've found out what the issue was... Even though I created the start date parameter first, then the end date, the order in the 'enter parameter values' form was end date first, then start date... so it returns no records because I was mixing up my parameter values when typing them in.
 
I feel a bit stupid right now, but I thought that I'd post my 'solution' in case someone else spent as many days as I did trying to work this out.
 
Arg.
 
Again, thanks for everyone's help.
 
- Scott
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 20 Jun 2012 at 5:13am
Haha I'm not certain if this is always the case but I'm pretty sure when parameters are used in a command they appear in alphabetical order in the dialog.
 
Ryan.
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.016 seconds.