Changing parameter input from 05/10/2003 to 20030510 1

[NoExperienceInUtah]

Ok, I'm going to try this another way.

I have a parameter which is a string input field. My end users will be inputing Dates into it in the 05/10/2003 format. I need a formula which will convert that string into a number field in the 20030510 format.

Any help?

 

[lbass]

Here's a conversion formula:

val(totext(cdate({yourstringdate}),"yyyyMMdd&quot)

There might be a simpler one.

But, I read your other thread, and it seems to me that you should set up the parameters as date parameters--for user ease--and then write your record selection formula something like this:

{yournumberfield} in (val(totext({?startdate},"yyyyMMdd&quot)) to (val(totext({?enddate},"yyyyMMdd&quot))

This probably doesn't get passed to the server though.

-LB

 

[ocs]

Assuming {?datepar} is the parameter and the input date format is mm/dd/yyyy (ex: 12/31/2002),
val(mid({?datepar},7,4)+left({?datepar},2)
+mid({?datepar},4,2)) should fit the bill.

 

[dgillz]

Utah,

There is no need to do this just because your database stores its dates ina YYYYMMDD format.

Instead, use the NumberToDate() function as follows:

NumbertoDate({YourNumericDateField}) in {?DateParameter}

Much simpler. NumberToDate() is a UFL which you must download from the crystal decisions website. 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]

dgillz, will that pass through to my SQL query? That's the issue I am having now. I have a formula that will do it, but it doesn't get through to the query.

 

[dgillz]

I doubt it, but it will not stop your report from running. Why must is pass thru to SQL?

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

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[synapsevampire]

Why use a string type parm?

If it's from an SP, then do your conversion back in the SP, otherwise, use a date type parm as it does validation on what's entered.

The formula for the record selection that you seek for a string parm is:

{table.field} = totext(cdate({?dateparm}),"yyyyMMdd&quot)

If you have a date range, use:

{table.field} >= totext(cdate(minimum({?dateparm})),"yyyyMMdd&quot)
and
{table.field} <= totext(cdate(maximum({?dateparm})),&quot;yyyyMMdd&quot)

If you change to using a date parm, use:

{table.field} >= totext(minimum({?dateparm}),&quot;yyyyMMdd&quot)
and
{table.field} <= totext(maximum({?dateparm}),&quot;yyyyMMdd&quot)

-k

 

[NoExperienceInUtah]

Dgillz,
It has to pass through to SQL because I am designing a report to be run on a program called Proform which uses the SQL query to run its report. I have gotten the report to run great in Crystal Reports, but ProForm only sees the SQL query.

 

[NoExperienceInUtah]

Synapse,
Will that still work if my {table.field} is stored as a number? or do I need a tonumber function instead?

 

[synapsevampire]

Define what you mean by the SQL Query.

Do you mean that Proform has a canned piece of SQL that is executed, then the recordset is bound to the report, or?

BTW, my previous post was tested and will pass to the database.

-k

 

[synapsevampire]

If you find that it doesn't pass, post what's in your record selection and the type of database field (date or datetime), we'll straighten it out.

-k

 

[NoExperienceInUtah]

I'll try to explain it better. I am trying to basically create a &quot;To Do&quot; list. In my program, there is a table called TrackItems where they can assign tasks to each other and themselves, but there's not a report made that will bring up their &quot;to do's&quot; for each day. I have the following fields that I'm working with.

{TrackItems.CpDt} as a number field
{TrackItems.Stat} a string field
{TrackItems.Who} a string field
{TrackItems.DuDt} a number field

my record selection reads as follows.

{TrackItems.CpDt} = 0 and
{TrackItems.Stat} in [&quot;Requested&quot;, &quot;Required&quot;] and
{TrackItems.Who} = {?Name of User} and
{TrackItems.DuDt} in {@Start Date} to {@EndDate}

The first three Items are great. I have a param for who that works just fine, but because DuDt is stored as a an 8 digit nubmer rather than a date, I have to use the @start date and @enddate formulas so that my users don't have to input their dates like 20030506.

I have tried a few different formulas, but can't find one that will pass to the SQL Query.

Right now I have this:

@startdate
val(mid({?Start Date},7,4)+left({?Start Date},2)
+mid({?Start Date},4,2))

@enddate
val(mid({?End Date},7,4)+left({?End Date},2)
+mid({?End Date},4,2))

They work brilliantly in Crystal Reports, but when I run the report from proform the date field doesn't pull up right and if I view the SQL Query from Crystal, I get this:

SELECT
Search.&quot;ByrName&quot;, Search.&quot;SlrName&quot;,
TrackItems.&quot;FirmFile&quot;, TrackItems.&quot;Stat&quot;, TrackItems.&quot;What&quot;, TrackItems.&quot;Who&quot;, TrackItems.&quot;DuDt&quot;, TrackItems.&quot;CpDt&quot;
FROM
{ oj &quot;ProForm&quot;.&quot;dbo&quot;.&quot;Search&quot; Search INNER JOIN &quot;ProForm&quot;.&quot;dbo&quot;.&quot;TrackItems&quot; TrackItems ON
Search.&quot;FirmFile&quot; = TrackItems.&quot;FirmFile&quot;}
WHERE
TrackItems.&quot;CpDt&quot; = 0 AND
(TrackItems.&quot;Stat&quot; = 'Required' OR
TrackItems.&quot;Stat&quot; = 'Requested') AND
TrackItems.&quot;Who&quot; = 'Alisha'

The DuDt formula from the record selection doen't pass through. I don't really have a preference as to what type of parm I use for the ?startdate and ?enddate as long as I can get it to work.

Is this enough information? tell me if you need anything else.

 

[NoExperienceInUtah]

ok....I'm smoking weed or something. The formulas that I have in right now work. The parameter is a string, so it doesn't make sure that they put the date in in the right format, but it at least works. If you have any suggestions on how to improve it, I would love them. BTW, Thank you OCS for the formulas to use with a string parm in @startdate and @enddate.

 

[synapsevampire]

OK, make sure it passes it to the database too, that's key to performance.

-k