Solved

zero records returned when start and end date are the same

Posted on 2012-04-05
3
286 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Reports - Display Last Invoice in Group 15 72
Cyrstal Reports formula to check IsNull 8 37
Field in crystal 2011 is being cut off. 9 41
Crystal Report sorting 6 16
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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
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 …

856 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