• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1684
  • Last Modified:

Crystal Reports date range formula

Hi

I'm having a problem getting a correct  report using the following selection formula for Crystal Reports using VB.Net

strSelectionfrm = "{BTUC.DATE_PAID}>= date('" & Format(DateTimePicker1.Value, "DD/MM/YYYY") & "')"
        strSelectionfrm = strSelectionfrm & " and "
        strSelectionfrm = strSelectionfrm & "{BTUC.DATE_PAID}<= Date('" & Format(DateTimePicker1.Value, "DD/MM/YYYY") & "')"

Open in new window


There seems to be a problem with formatting the dates, but I've now got a headache trying to figure it out. I don't get an error, but the report shows ALL records

Any help would be appreciated.

Al
0
Blue_Steel
Asked:
Blue_Steel
  • 7
  • 3
  • 2
  • +1
1 Solution
 
peter57rCommented:
You cannot do date comparisons in ddmmyyyy format.  It doesn't work.
25112011 would be found to be less than 27032011 whereas in terms of dates it should be greater.

 You should use yyyymmdd
0
 
mlmccCommented:
What is the rest of the code you are using to call the report.

I agree you cannot compare dates if formatted as peter suggests.  The formula you are using will convert them back to dates.  You are formatting them only to get them into a format that the DATE function accepts.

Can you have NULL dates?

mlmcc
0
 
Blue_SteelAuthor Commented:
Hi guys

This is the latest code I have tried. Null dates aren't allowed.

        Dim report As New RptSubsAudit
        Dim strSelectionfrm As String

        strSelectionfrm = "{BTUC.DATE_PAID}>= date('" & Format(DateTimePicker1.Value, "YYYY/MM/DD") & "')"
        strSelectionfrm = strSelectionfrm & " and "
        strSelectionfrm = strSelectionfrm & "{BTUC.DATE_PAID}<= Date('" & Format(DateTimePicker1.Value, "YYYY/MM/DD") & "')"

        With Form2
            .CrystalReportViewer1.ReportSource = report
            .ShowDialog()
        End With

Open in new window


Thanks
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Blue_SteelAuthor Commented:
PS I don't know if it's relevant, but I'm pulling the data from an Access databse.
0
 
Blue_SteelAuthor Commented:
PPS In trying to rush things, please note that the second datetimepicker is datetimepicker2, not 1.
Corrected code is as follows

        Dim report As New RptSubsAudit
        Dim strSelectionfrm As String

        strSelectionfrm = "{BTUC.DATE_PAID}>= date('" & Format(DateTimePicker1.Value, "YYYY/MM/DD") & "')"
        strSelectionfrm = strSelectionfrm & " and "
        strSelectionfrm = strSelectionfrm & "{BTUC.DATE_PAID}<= Date('" & Format(DateTimePicker2.Value, "YYYY/MM/DD") & "')"

        With Form2
            .CrystalReportViewer1.ReportSource = report
            .ShowDialog()
        End With

Open in new window


Same result - no error, but report shows all records
0
 
vastoCommented:
Your filter in strSelectionfrm  may be good , but it should be passed to the report.
You need to set report selection formula = strSelectionfrm
0
 
Blue_SteelAuthor Commented:
I'm afraid you've lost me there vasto. The same code above with different selection formulas creates numerous reports with no problems. It's just the date criteria in this one.

I'm at the stage of pulling my hair out, but I haven't got any.
0
 
Blue_SteelAuthor Commented:
Sorry vasto, had a blonde moment there. That's what happens when you cut and paste :(

I've set it, and now I finally get an error. Whoopee.

As expected, the error is "Bad date format".

Thanks for your patience guys.
0
 
mlmccCommented:
I suspected you weren't passing the formula to the report that us why I asked to see the rest of the code

Date expects the string to be September 20, 1999

Try this way

        strSelectionfrm = "{BTUC.DATE_PAID}>= date(" & Format(DateTimePicker1.Value, "YYYY,MM,DD") & ")"
        strSelectionfrm = strSelectionfrm & " and "
        strSelectionfrm = strSelectionfrm & "{BTUC.DATE_PAID}<= Date(" & Format(DateTimePicker2.Value, "YYYY,MM,DD") & ")"

mlmcc

0
 
Blue_SteelAuthor Commented:
I got an error with that code too mimcc, but after my last post I persevered and finally got it working by simply taking out the formatting of the date.
This is the code that works :
        Dim report As New RptSubsAudit
        Dim strSelectionfrm As String

        strSelectionfrm = "{BTUC.DATE_PAID}>= date('" & DateTimePicker1.Value & "')"
        strSelectionfrm = strSelectionfrm & " and "
        strSelectionfrm = strSelectionfrm & "{BTUC.DATE_PAID}<= Date('" & DateTimePicker2.Value & "')"

        report.RecordSelectionFormula = strSelectionfrm
        With Form2
            .CrystalReportViewer1.ReportSource = report
            .ShowDialog()
        End With

Open in new window


I'm so relieved. Need to deliver to client in the morning.

Thanks for the help guys.

Al
0
 
vastoCommented:
Good that it works now. All credits to mlmcc.
The hard part was to udentify that the initial problem was comming from the code not from the formula.
0
 
Blue_SteelAuthor Commented:
Managed to solve this myself, thanks to bouncing ideas off the other guys
0
 
mlmccCommented:
The generally accepted practice is to award points to comments that helped you resolve the issue.

You can accept your comment as the solution but there were comments that helped you solve the problem.

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 7
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now