DATE CONVERSION FROM STRING OF NUMBERS 2

[BHERNANDEZ]

I have worked every formula and idea I have found. I need to convert a string of numbers into a date format. My dates llok like 20030307. I have tried about 20 different formulas and I always come up against errors. Too many arguments. The ) is missing. The remaining text does not look like a part of the string. The string is missing. You need a string here. etc, etc. Does someone have a solution for me? I am working with CR 8.0, using crstal syntax.

B

 

[JoeCrystal]

Dim strDate As String

strDate = "20030307"

Formula = DateValue(ToNumber(Mid(strDate,1,4)),ToNumber(Mid(strDate,5,2)),ToNumber(Mid(strDate,7,2)))

 

[Madawc]

Try left(yourname, 4) to snip out the year. In separate formula fields, use mid(yourname, 5, 2) for the month and right(yourname, 2) for the day.

I think that Crystal, which is built on SQL, holds a date as a binary number and is fussy about what it will accept as a real date. Madawc Williams
East Anglia
Great Britain

 

[BHERNANDEZ]

jOE,

I used your formula. The text STRDATE does not highlight in blue. So I tried a few ways, but all I got was the ) is missing. I checked it to make sure I had it down correctly and it was.

Madawc,

I used a few formulas like that yesterday and came up with the same type of errors.

B

 

[synapsevampire]

Try:

Date(val(left(strDate,4)),val(Mid(strDate,5,2)),val(Mid(strDate,7,2)))

-k

 

[synapsevampire]

Sorry:

Cdate(val(left(strDate,4)),val(Mid(strDate,5,2)),val(Mid(strDate,7,2)))

-k

 

[BHERNANDEZ]

SYNAPSE,

I TRIED BOTH. THE ERROR WAS I AM MISSING A ) AND THE CURSOR BLINKS IN FRONT OF THE FIRST strdate.

B

tHANKS TO EVERYONE WHO HAS TRIED TO HELP ME SO FAR. I JUST HAD MY PROGRAMMER IN HERE AND HE COULDN'T FIGURE IT OUT. WE THINK THE PROBLE COMES FROM THE DATES HAVING DIFFERENT FORMATS BECAUSE I AM PULLING INFORMATION BACK TO 1992. WE HAVE UPGRADED OUR PRGRAM AT LEAST FIVE TIMES SINCE THEN.

 

[synapsevampire]

Copy and paste this into a formula:

Cdate(val(left("20011224",4)),val(Mid("20011224",5,2)),val(Mid("20011224",7,2)))

That should work.

Now replace the "20011224" with {yourtable.yourstringdate}

You shouldn't get a paren missing if the format is wrong, it will tell you that the date is invalid.

-k

 

[BHERNANDEZ]

SYNAPSE,

I DID AS YOU SUGGESTED. I RECEIVED "A STRING IS REQUIRED HERE" ERROR. THE CURSOR WAS BLINKING IN FRONT OF {MYFILE}. MAYBE I AM MISSING SOMETHING???? AS YOU CAN TELL, I HAVEN'T ANY TRAINING ON THIS AND AM HAVING TO TEACH MYSELF. (VERY HARD!!!)

B

 

[synapsevampire]

This does NOT contain anything like {myfile}, so I've no idea what you're doing.

Cdate(val(left("20011224",4)),val(Mid("20011224",5,2)),val(Mid("20011224",7,2)))

The test is to select from the menu:

Insert->Field Object->Right Click Formula Fields->New

Paste in exactly what I pasted here.

Now place that formula on the report.

It should display correctly.

Right click the formula->Edit Field Object

Now replace the "20011224" with {yourtable.field} as it is demonstrated above.

Note that you can select the field and it will place it into your formula wherever the cursor is.

-k

 

[lyanch]

If the date format has changed over time, and you are using date as a parameter, you will need to test for (and be able to tell apart) every variation. If not this will fail when it reaches something in a different format.. a different length.. null or blank.

lisa

 

[BHERNANDEZ]

LISA,

YOU ARE CORRECT. THIS IS THE PROBLEM I SEEM TO BE ENCOUNTERING. ALL FORMULAS GIVEN TO ME WORK WITHOUT MY DATAFIELD FILE NAME IN THEM. I MAY HAVE TO KEEP DOING MY REPORT THE OLD FASHIONED WAY UNTIL I GET THE NEW VERSION OF MACOLA THAT HAS CRYSTAL INTEGRATED INTO IT.

THANKS SO MUCH EVERYONE FOR YOUR HELP! IT IS GREATLY APPRECIATED!

B

 

[synapsevampire]

Bizarre that it gives you a missing paren error...

An alternative might be to create a SQL Expression, or more than one, depending upon what these different formats are.

The SQL Expression could evaluate the format and return a properly formatted date.

Here's an example wher you might check the length using SQL Server:

case len(chvAddr1)
when 8 then substring(chvAddr1,1,4)
else
chvAddr1
end

This would need to be fleshed out, but it might provide a solution.

-k