Format textdatefield to be used in Datediff function? 1

[frontside]

I have a datefield that´s formatted like text (Ex. 20021204). I need to use this field in a Datediff calculation, I guess I have to change the format before I can use it?(I´ve done it using Access)
What I want is
1. 20021204 (original textfield)
2. 2002-12-04 (formatted so the datediff function understand)
3. Datediff using the new formatted field

Please let me know if I´m thinking completely wrong here!
I´m not that used to the functions in CR but I found some functions that might do the trick.

DateValue (CDate ({PUPROTRA.D3611_Date1}[1 to 8]))
(I found this searching the forum, could I use this?)

Datediff ({PUPROTRA.D3611_Date1},{PUPROTRA.D3611_Date2})

I would love some help

Mikael "Sweden"

 

[goranm]

Hello "Sweden"!

Try it this way:

DateVar Date1 := CDate(ToNumber(mid({PUPROTRA.D3611_Date1},1,4)),ToNumber(mid({PUPROTRA.D3611_Date1},5,2)),ToNumber(mid({PUPROTRA.D3611_Date1},7,2)));

DateVar Date2 := CDate(ToNumber(mid({PUPROTRA.D3611_Date2},1,4)),ToNumber(mid({PUPROTRA.D3611_Date2},5,2)),ToNumber(mid({PUPROTRA.D3611_Date2},7,2)));

And when you use the DateDiff function you must use an argument to tell wich kind of differense you want i.e. DateDiff("d", Date1, Date2)

/Goran

 

[frontside]

Thanks Goran

That was exactly what I was looking for and It worked great.
I´m doing the same Report in Access aswell in CR and Crystal Report had a much better(easier) way of handling the format/datediff problem.

Thanks again (well worth a star)

Mikael "Sweden"

 

[frontside]

I have just one small problem left, the code was great until I changed the selections to get all data. I keep getting the message "the string is non-numeric".

My thought was that it was caused by empty(null) values in my data so I put the a restriction in the date fields to "is not equal to" and then a empty box (I dont know if this is the right way to take those empty datafields away).
I then moved both the datefields and the datediff field into the report(I dont refresh the data) and it worked with no problem but when I refresh the data with those fields in the report I get the error message.

When I narrow the date range I dont get any error messages, this (I think) proves that there is some data that the datediff/format code cant handle?

What could be the reason for the error message?
Any ideas


Mikael "Sweden"




"Any damn fool can make something complex. It takes a genius to make something simple."
Woody Guthrie

 

[dgillz]

Mikael,

if the string is non-numeric, that means you have text or spaces in your field somewhere. Write a quick query on this field, with a record selection formula of "NumericText({YourField})=false" and see what it returns, then post back with some results. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[frontside]

Thanks for the help

with the query "Numeric Text({date1})=false"
I get 600 lines….. all with empty {date1} fields

with the query "Numeric Text({date2})=false"
I get 9 lines…..all with empty {date2} fields + many other empty fields but those should not effect my result, since they are not in the datediff calculation.

I did another test with
{date1} is equal to “ “ and I get 600 lines
and
{date2} is equal to “ “ gives me 9 lines

I thought those tests proved that it was the empty date fields that was the problem so I did another query with both {date1} and {date2} with (is not equal to “ “) but I still get the error message like before (after a few hundred records).

Mikael "Sweden"

 

[dgillz]

Under file, options, check the "convert null field value to default" checkbox, and run you report again and see if that works. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[frontside]

I still get the same error message

any ideas?

 

[dgillz]

Create a null test in your code as follows:

If Isnull{{yourfield}) then date(1950,1,1) else <<convert your firld to a date logic>>

See if that works. If so you can subsitute something else for jan 1, 1950. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com