This question is related to this one, however I thought I'd create a new post since it's not the exact same issue, and the ideas on it were just being repeated.
I have a selection formula for my Crystal Report. It's supposed to select data where the Stage field in one table is 6, and the PaymentDate field in another is less than, or equal to, the value of the DateTimePicker control.
The code that I have below is working fine for most of the dates. However, say for example I have the following data in the database:
Sales_Headers.Stage = 6
Sales_Lines.PaymentDate = 28/01/2017 (January 28th, 2017)
When choosing a date of January 26th, up to January 31st, the data is only retrieved when the date is 28th or higher. However, if I then select a date of 1st February (Or the 1st of any month to be precise), it is returning the date as 02/01/2017, or, 2nd January 2017, so the data isn't shown.
Why is it changing for only the 1st of each month? All other dates are being read correctly, as dd/MM/yyyy, but on the first, it's using the MM portion as the dd portion.
I've tried:
Dim dateTo As Date = dtpCRTo.Value.AddDays(1).Date
dateTo = Format(dateTo, "dd/MM/yyyy")
If cmbCRSupplier.Value = "" Then
selectionFormula = "{Sales_Headers.Stage} = '6' AND {Sales_Lines.PaymentDate} < #" & dateTo & "#"
Then I tried this in the form_Load event, as well as in the Value_Changed event of the DateTimePicker:
Dim dateFormat As String
dt.Format = DateTimePickerFormat.Custom
dt.CustomFormat = "dd/MM/yyyy"
dateFormat = dt.Text
The final thing I tried was just to have no formatting code, and just using:
If cmbCRSupplier.Value = "" Then
selectionformula = "{Sales_Headers.Stage} = '6' AND {Sales_Lines.PaymentDate} <= #" & dtpCRTo.Value.Date & "#"
But it was the same result for all of them.
As well as the method @Siva was talking about (Always a good way, that way you can create the formula there and use the syntax that it's looking for), there is the way you're trying, to do it in VB.NET.
As I just mentioned, the syntax is important, and this is what is causing your issue.
I've not seen anyone try to use DateTimes in this method before, using `#value#.
The correct way to format dates into a RecordSelectionFormula (again, this is something you'll have seen if you'd have created it in Crystal itself), is to DATE(yyyy, MM, dd).
So, the correct way to syntax this is to use:
selectionFormula = "{Sales_Headers.Stage} = '6' AND {Sales_Lines.PaymentDate} <= DATE(" & _
dtpCRTo.Value.Date.Year & "," & dtpCRTo.Value.Date.Month & "," & dtpCRTo.Value.Date.Day & ")"
Use this method to insert your dates into selection formulas in the future, that way you can't get it wrong.
If this doesn't work, then you need to check your Region and Local Date/Time settings in Control Panel, to ensure they're set correctly.