Solved

zero records returned when start and end date are the same

Posted on 2012-04-05
3
283 Views
Last Modified: 2012-04-26
When I enter a date range that uses the same for start and end values I get zero records returned al though if I loo in the db I can find a few records that match that criteria.

When I look at the SQL query in Crystal, I see that the record selection date range changes, and the <= show only as < 

Seems this happens automatically on crystal.

How can I avoid that?

Thanks.
0
Comment
Question by:metropia
3 Comments
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 166 total points
ID: 37813189
Use a stored procedure.

Also be aware of the times . . . if you have both start and end date @ 12:00 AM then it will only return anything that is exactly at that time.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 167 total points
ID: 37814682
Your records probably have a time associated with the date so you need to compare just the date or use

{DateField} >= {?DateParameter} AND {DateField} <= {?DateParameter} + 1

mlmcc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 167 total points
ID: 37815185
Interesting point about the <= being changed to <.  That seems to just be the way that CR handles a <= datetime test.  In the query, it adds 1 second to the time and replaces the <= with <.  I don't know why it does that.  My best guess would be some kind of question/problem with precision, or something like that.  So it adds a second and uses < instead.  I don't know if it always does that.  It may depend on your datasource.  In my case I was using a test report that reads an Excel file.

 So
 1) I don't think there's anything you can do to avoid it.  _Maybe_ if you use a different datasource.  I kind of doubt that it would make any difference, but I can't rule it out.
 2) It shouldn't be a problem.  CR is adding 1 second, so the results should be the same.


 The most likely explanation for not finding the records would seem to be what the others suggested, that the times are not within your range.

 James
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now