Converting to a Date from a Number

[NoExperienceInUtah]

I am using Crystal Reports with Softpro's ProForm. I have NEVER programmed before and need some help.

ProForm stores its dates in an odd format. It stores them as a number in the 20030430 format (April 30, 2003.) I am trying to create reports, but I don't want to have to teach my employees that when they are prompted for a date, they have to enter it like that. I'd like them to be able to put 04/03/2003 in the prompt and have the formula take care of the rest. I'm sure there is a formula that will take a standard date and convert it into the above formula so that it can be used with the database.

I don't know that I quite explained that right, but hopefully someone out there will understand what I need and be able to help. THANK YOU!

 

[dgillz]

Go to the crystal decisions website and download the numbertodate() UFL. This function takes an 8 digit integer in a YYYYMMDD format and converts into a real date.

Here is the link:

http://support.crystaldecisions.com/communityCS/FilesAndUpdates/ufltdate.exe.asp

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[NoExperienceInUtah]

I am REALLY new to Crystal Reports (I've had it for two days now.) How do I add that function that you pointed me to to my report?

Also, when I run the report, it asks me for a date range, but I can't get the range I input to search the dates in the database because they are all in numeric format. What do I do to make them mesh?

Thanks for your patience.

 

[dgillz]

To add the function in crystal you download it and install it. Did you do this? If so the function will appear in the formula editor window under "additional functions".

Your report should ask for a date range, however, you need to tie the date range parameter into your record selection formula in order for this to select the proper records. Your record selection formula should look like the following:

Numbertodate({YourNumericDateField}) in {?DateRangeParameter}

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[synapsevampire]

Or you can create your own formulas as I'm not sure that dgillz solution will pass the SQL processing down to the database:

@startdate
val(totext(year(minimum({?StartDate})),0,"&quot+
iif(len(totext(month(minimum({?StartDate})),0,"&quot) = 1,"0"+totext(month(minimum({?StartDate})),0,"&quot,totext(month(minimum({?StartDate})),0,"&quot)+
iif(len(totext(day(minimum({?StartDate})),0,"&quot) = 1,"0"+totext(day(minimum({?StartDate})),0,"&quot,totext(day(minimum({?StartDate})),0,"&quot))

@enddate
val(totext(year(maximum({?StartDate})),0,"&quot+
iif(len(totext(month(maximum({?StartDate})),0,"&quot) = 1,"0"+totext(month(maximum({?StartDate})),0,"&quot,totext(month(maximum({?StartDate})),0,"&quot)+
iif(len(totext(day(maximum({?StartDate})),0,"&quot) = 1,"0"+totext(day(maximum({?StartDate})),0,"&quot,totext(day(maximum({?StartDate})),0,"&quot))

In the record selection formula use:

{table.datestring} >= @startdate
and
{table.datestring} <= @enddate

-k

 

[dgillz]

SynapseVampire,

I have a question. How can I tell if the selection formula will pass thru to SQL?

I have seen many of your posts regarding this subject but it is time for me to get to understand this concept fully.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[kenhamady]

Once you extract the function from the zip file, you should copy the DLL to the Windows\Crystal folder. Then the next time you open CR it will read this function into the list of available functions.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Reebo99]

Synapse,

In you solution for @StartDate and @EndDate, would

Date(ToText(Minimum({?StartDate}),&quot;####,##,##&quot) and
Date(ToText(Maximum({?StartDate}),&quot;####,##,##&quot)

Work ok?

Reebo
Scotland (Sunny with a Smile)

 

[synapsevampire]

dgillz: Check the Database->Show SQL Query

Everything in the Record Selection Formula should be reflected there.

Reebo: I don't think so, though you could use:

val(ToText(minimum({?startdate}),&quot;yyyyMMdd&quot)

I've had some bad experiences when trying to get Crystal to pass SQL when I used a formatting mask in the totext, but I tested and the above works.

-k

 

[NoExperienceInUtah]

Synapse,
I tried inserting your formula into my Report, but when I try to save it, it tells me that the summary/running total fields can't be created. What does that mean?

 

[synapsevampire]

I'm not using a summary or running total, so I've no idea why yours isn't working, please post what you tried.

-k

 

[lyanch]

Set your parameter to be a date.. in the selection formulat


{table.datestring} >= (datepart(&quot;yyyy&quot;,@startdate) * 10000) + (datepart(&quot;m&quot;,@startdate) * 100) + datepart(&quot;d&quot;,@startdate)
and
{table.datestring} <= (datepart(&quot;yyyy&quot;,@enddate) * 10000) + (datepart(&quot;m&quot;,@enddate,) * 100) + datepart(&quot;d&quot;,@enddate)

Lisa

 

[synapsevampire]

Or:

{table.datestring} >= val(ToText(minimum({?startdate}),&quot;yyyyMMdd&quot)
and
{table.datestring} <= val(ToText(maximum({?startdate}),&quot;yyyyMMdd&quot)

-k

 

[NoExperienceInUtah]

I didn't think I was using summary or running total fields. How can I stop from using them? They aren't anywhere on my report.

 

[synapsevampire]

You need to post the offending formula that you're trying to save.

-k

 

[NoExperienceInUtah]

I copied exactly what you had posted and created a new formula and pasted it into it.

val(totext(year(minimum({?StartDate})),0,&quot;&quot+
iif(len(totext(month(minimum({?StartDate})),0,&quot;&quot) = 1,&quot;0&quot;+totext(month(minimum({?StartDate})),0,&quot;&quot,totext(month(minimum({?StartDate})),0,&quot;&quot)+
iif(len(totext(day(minimum({?StartDate})),0,&quot;&quot) = 1,&quot;0&quot;+totext(day(minimum({?StartDate})),0,&quot;&quot,totext(day(minimum({?StartDate})),0,&quot;&quot))

 

[synapsevampire]

Do you have a parameter called StartDate?

You might instead go into the record selection formula (Report->Edit Selection Formula->Record) and place something like:

{table.datestring} >= val(ToText(minimum({?startdate}),&quot;yyyyMMdd&quot)
and
{table.datestring} <= val(ToText(maximum({?startdate}),&quot;yyyyMMdd&quot)

Replacing {table.datestring} with the name of the field in your database.

-k

 

[NoExperienceInUtah]

I do have a paramater called startdate. It's a date field, I'll try your second suggestion. Thanks for your patience...This is all extremely new to me.

 

[synapsevampire]

Perhaps the startdate parameter you had created wasn't a type range.

-k

 

[NoExperienceInUtah]

Synapse,
I get the same error if I try to insert the shorter formula into the selection formula. Any more thoughts?