March 14, 2008 at 1:23 pm
Hi Guys,
Here I am stuck with Date Validation.I have a report which displays certain information based on StartDate and EndDate parameter selected by user.Both parameters are of DateTime data type.Currently I have a query that displays customer information based on the startdate and enddate selected by user.All works well..Rather simple actually.
My Problem is How do I validate the date selected for both StartDate and EndDate are between '01/01/2008' and '12/31/2008'.If yes, then display report.Else Let user know to select date between Year range of 2008.Any ideas ?.....Pls advise me .....
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
March 16, 2008 at 1:24 pm
I don't know directly of a way to validate the parameter apart from building a dataset of valid values, this isn't ideal because what you have then is a drop down list against the parameter that includes all 365 dates in 2008 in a single drop down (not a calendar format).
So......
The way I would approach this is to allow the user to select dates in the normal way, and display a text box instead of the report when the dates are outside of valid ranges.
For example:
Set up your parameters as normal.
On you main dataset, place an if statement at the start so that it will only return data when the dates are valid.
On your report, create a text box that has a text value in it saying "Please select dates between 1.1.2008 & 31.12.2008"
The text box should have a visibility expression on it so that it only shows when the dates are outside of the valid range.
You main report table also has a visibility expression on it (which is the reverse of the above).
Although it seems long winded, it's actually fairly simple.
Good luck,
Nigel.
Nigel West
UK
March 18, 2008 at 2:16 pm
Hi would you please give me a detailed explanation on the steps to be taken.Where in my query do I add an if statment.My query is used to display customer information between startdate and enddate parameter as below.How do I go about adding only values between '01/01/2008' and '12/31/2008', and how do I populate an error message for user asking them to enter the correct year for startdate and enddate.
Query within Dataset to return Customer info:-
Select Name,Address from dbo.Customer where
convert(char(10), Finalized_Date, 101)
BETWEEN @StartDate AND @EndDate
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
March 18, 2008 at 4:33 pm
Hi You could do the following
IF @StartDate > '01 JAN 2008' AND @EndDate <= '31 DEC 2008'
BEGIN
Select Name,Address from dbo.Customer where
convert(char(10), Finalized_Date, 101)
BETWEEN @StartDate AND @EndDate
END
ELSE
BEGIN
Select 'Dates not valid' AS Name, '' AS Address
END
This should cover both things for you, in the first instance you should get your list of customers when the dates are valid, when the dates are not valid then you will get the message "Dates Not Valid" as the first (and only) customer name.
I am sure you can develop this a little further as you want.
Good luck,
Nigel.
Nigel West
UK
March 19, 2008 at 8:37 am
Hi Nigel,
Thanks for your response.Here is what I have done so far.I have 3 tables on the report, therefore ..
1)Do I add the below,to all 3 datasets?
IF @StartDate >= '01 JAN 2008' AND @EndDate <= '31 DEC 2008'
BEGIN
select Name,Address
from dbo.Customer
where Date BETWEEN @StartDate AND @EndDate
END
ELSE
BEGIN
Select 'Dates in 2008 Range'
END
Upon executing the above in dataset..no matter if the dates don't fall in the Range of year 2008.I still receive the name and address details.What am I doing wrong here ?
2)Added a txt box to report, in Visibilty Expression.I have added the following
=IIF Parameters!StartDate.Value '12/31/2008' [Im not sure if this is correct]
3)Where do I set the visibilty for the report ?
I have never done this before,Truely appreciate your help.Thanks!
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
March 19, 2008 at 8:47 am
1)Do I add the below,to all 3 datasets?
IF @StartDate >= '01 JAN 2008' AND @EndDate <= '31 DEC 2008'
BEGIN
select Name,Address
from dbo.Customer
where Date BETWEEN @StartDate AND @EndDate
END
ELSE
BEGIN
Select 'Dates in 2008 Range'
END
The last part of this should read Select 'Dates in 2008 Range' AS Name, '' AS Address
It is important that both parts of the IF statement are returning the same possible column names to the data set.
Upon executing the above in dataset..no matter if the dates don't fall in the Range of year 2008.I still receive the name and address details.What am I doing wrong here ?
2)Added a txt box to report, in Visibilty Expression.I have added the following
=IIF Parameters!StartDate.Value '12/31/2008' [Im not sure if this is correct]
The visibility expression should read something like IIF(Parameters!StartDate.Value '31 DEC 2008',TRUE,FALSE)
Of course, this is based on the requirement to HIDE the text box if the dates are outside valid dates, if the requirement is the reverse of this then you should swap the TRUE/FALSE parts of the expression.
3)Where do I set the visibilty for the report ?
Not sure what you mean by the visibility for the report, basically what I am doing here with the dataset is returning only a single value as a name that will have an error message inside it, so, if you have a table showing Name & Address, the first NAME will be the error message, you shouldn't therefore need to have any visibility expressions being used.
I have never done this before,Truely appreciate your help.Thanks!
Nigel West
UK
March 19, 2008 at 8:50 am
Sorry, somehow the visibility expression went slightly wrong.....
IIF(Parameters!StartDate.Value '31 DEC 2008',TRUE,FALSE)
I still think you shouldn't need to use it though, the dataset should really take care of it for you.
Nigel West
UK
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply