making date from a field of text

[JasonKaufman]

I am removing date from a string by using the following formula:

(Left({PO1_PurchaseOrderEntryHeader.ShipToAddress1},8))

information in the field could be as 04/14/06 or as 04/14/06C and so i use the formula to leave it as 04/14/06 either way.

However, when i use this new formula (as its formula name) within another formula that determines if this value is within the parameters of a date range, Crystal says "A String is required here" when evaluating

if {@Ship Date} in {?Date Range} then

where {?Date Range} requires a date entry when filling in parameter. This {?Date Range} is used in other parts of main formula and those other fields are Date specific and dont create a problem.

So question is (finally):
How do i extract date information from field and convert it into date-identified field?

thanks for reading and considering my problem

Jason

 

[lbass]

Try changing your {@shipdate} to:

cdate((Left({PO1_PurchaseOrderEntryHeader.ShipToAddress1},8)))

-LB

 

[JasonKaufman]

LB,

tried your suggestion, but it comes back after trying to run and says:

Bad Date Formatting

Any other thoughts??

 

[lbass]

If the date format is always MM/dd/yy then the above should work. Is there variation in how the date is entered in this string?

You could try:

date(2000+val(mid({table.string},7,2)),val(left({table.string},2)),val(mid({table.string},4,2)))

If you run into the same problem, then I think you have to check your field for variation in the date display.

-LB

 

[Madawc]

Do a test version of the report, which displays the actual value of

(Left({PO1_PurchaseOrderEntryHeader.ShipToAddress1},8))

It sounds like some of the data is not what you think it is.

Maybe check

 if not isnull({PO1_PurchaseOrderEntryHeader.ShipToAddress1}) and
{PO1_PurchaseOrderEntryHeader.ShipToAddress1} not = ""

before truing to convert


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[JasonKaufman]

with a little help from Crystal 11 complete reference book that i received last night, i've been able to makes all my formulas work even with extracting date by @Ship = Left ({PO1_PurchaseOrderEntryHeader.ShipToAddress1},8)
then
if isdate ({@Ship}) then DateValue ({@Ship}) else {PO1_PurchaseOrderEntryHeader.RequiredExpireDate}

so if i have junk in the first 8 characters when pulled out in @Ship, 'isdate' will prove false and replace with a usable date in requiredexpiredate

from there plugged that into next formula:
if {PO1_PurchaseOrderEntryHeader.TermsCode} = "00" then
if {@Ship Test} in {?Date Range} then {@$ Amount}

and so on for all terms then plug into final formula:
if {PO1_PurchaseOrderEntryHeader.TermsCode} = "00" then {@00}
else
if {PO1_PurchaseOrderEntryHeader.TermsCode} = "04" then {@04}
else
if {PO1_PurchaseOrderEntryHeader.TermsCode} = "06" then {@06}
else
if {PO1_PurchaseOrderEntryHeader.TermsCode} = "15" then {@15}
else
if {PO1_PurchaseOrderEntryHeader.TermsCode} = "30" then {@30}
else
if {PO1_PurchaseOrderEntryHeader.TermsCode} = "45" then {@45}
else
if {PO1_PurchaseOrderEntryHeader.TermsCode} = "50" then {@50}
else
if {PO1_PurchaseOrderEntryHeader.TermsCode} = "60" then {@60}

while its a little longwinded in getting there, all values check out with a variety of date ranges used.

thanks to all who contributed and took the time to reply.

Jason