Date / String / Data problem

[welshone]

Hello,

I have to write reports for a database I am not allowed to amend, (if I could I couls solve the problem straight away).
The problem is with Dates, the date fields on the database are VarChars (strings).
when I try to use parameters to put in a start and end date it works ocassionally, but not all the data is pulled through.
I think itds because when I type from 01/01/80 to 01/01/90,
it will search between 80 and 90, but not between the 01's.
hope that makes sense. ?

anyway, does anybody know how I can solve this problem ?

 

[SmpTraining]

I would convert the VarChars to Date format before comparing them with your date parameter fields.

This will take a bit of string manipulation using formulas like Mid, Left & Right depending on the format of the VarChar field but will result in a reliable solution.

 

[welshone]

Don't suppose you know where I can find examples of this ?

 

[SmpTraining]

Try this one I've just knocked-up.

// Formula to convert a VarChar Date field into a Crystal Date Field
//
// We are assuming the VarChar field has a format of DD/MM/YYYY

StringVar VC_Date := "30/10/2001";

Date ( ToNumber(Right(VC_Date,4)), ToNumber(Mid(VC_Date,4,2)), ToNumber(Left(VC_Date,2)) )

You can comment out the StringVar and use your varchar date field instead of VC_Date. Using this formula will allow you to treat the field as a true date field.

 

[welshone]

Thats Excellent.
Just one last thing, How can I use a formula as a parameter ?

 

[SmpTraining]

Eh?

If I understand your problem correctly you are having a date range entered as a set of parameter fields along with the DfEE number. You then select only the students for a given DfEE number where their DOB falls within the date ranges submitted.

If this is the case, you need to change the two parameter fields to make them both DATETYPE fields and apply the formula against the VARCHAR date FIELD.

Once you've done this, its a simple task of using the SELECT EXPERT to specify a selection against the formula field where dates are 'between' the two parameter fields.

You may argue that the parameters need to remain STRINGTYPE in order for users to type DD/MM/YY. If this is the case, then we will need to create additional copies of the formula and apply them to both parameter fields too.

 

[welshone]

I see.

however, When I do that and run the report I get an error : the string is non-numeric. ?

Date ( ToNumber(Right({VAd.DOB},4)), ToNumber(Mid({VAd.DOB},4,2)), ToNumber(Left({VAd.DOB},2)) )

 

[FoxG]

Hi,

{@formula} >= {?dateparam1) and {@formula} <= {?dateparam2)

or

{@formula} in {?param_daterange}

depending on how you wish to define your date parameters.

Hth,
Geoff

PS. If you date doesn't include the century you will need to include it in the first value to your date function
ie.
Date ( ToNumber(&quot;19&quot;+Right(VC_Date,2)),
ToNumber(Mid(VC_Date,4,2)),
ToNumber(Left(VC_Date,2)) )

You may need to manually cater for Y2K !

 

[SmpTraining]

The error message is a little strange as the formula does not return a numeric value - nor should it! It returns a date. The question therefore is what are we comparing this DATE field to? If you include your selection criteria as found under ...

Report -> Edit Selection Formula -> Record

... we can start to diagnose the problem.

 

[welshone]

In the selection criteria is :

{VAdmission.FileName} = {?DfEE} and
{@VartoDate} in {?start Param} to {?End Param}


and VartoDate formula =
Date ( ToNumber(Right({VAdmission.DOB},4)), ToNumber(Mid({VAdmission.DOB},4,2)), ToNumber(Left({VAdmission.DOB},2)) )

and the start param and end param have been changed to value tye = Date.

 

[SmpTraining]

All seems fine. Back to some basics here.

1. Is VAdmission.DOB of the format DD/MM/YYYY ?
2. Try including VartoDate on the report - Does it display as a properly formatted date?

If neither of these highlight the solution, perhaps you'd like to email me the report? It must be something very simple and communicating over this forum isn't the most efficient sometimes.

If you do want to do that, email me at Steve@NetworkEssentials.co.uk and we'll nail it!

Regards.