Help with formula (1 Viewer)

[Rob_Radley]

Hi, I'm new to crystal reports and I am having trouble with a if then statement. here is what I am entering
if ({SUMPNPOD.ODSDAT} = 0) then ({SUMPNPOD.ODSDAT}) else stringvar x := totext({SUMPNPOD.ODSDAT},"000000");date(2000 + val(right(x,2)),val(left(x,2)),val(mid(x,3,2)))
Crystal is telling me I have an error and that a "number is required here" (Here being after the else).
What I have is a file date field in mmddyy format. The formula after the else works to convert to crystal date format. However if the field is =0 the formula breaks. So I am trying to tell it that if the date field =0 then print the 0, otherwise convert the field value to a crystal date.
Any ideas?
Thanks

 

[boblarson]

A date field will not equal zero. It can be null or have a date. All you need in your formula is this:

if Not IsNull({SUMPNPOD.ODSDAT}) then totext({SUMPNPOD.ODSDAT},"000000");date(2000 + val(right(x,2)),val(left(x,2)),val(mid(x,3,2)))


The formula will then return the result. No variables required.

 

[Rob_Radley]

Thanks for the quick reply. I am still having a problem though.

if not isnull({SUMPNPOD.ODSDAT}) then "" else stringvar x := totext({SUMPNPOD.ODSDAT},"000000");date(2000 + val(right(x,2)),val(left(x,2)),val(mid(x,3,2)))

this is what I put in. when I tell it to check the formula it says no errors found. Then I save. When I refresh the report data it breaks and tells me "month number must be between 1 and 12"
her is an exaple of the data

SKU#DATEODQTY20200507002020086090825202008025202231609087002022310700206000609081400206000014002060096090815002060090150020800460208602080046090890208004015020801260208302080126090830208012060
The quantity field is a total on order by sku, then date. If the date field = 0 then it is listing the total on order for all dates of that sku.
I was hoping that this way I would be able to put each week and the total as columns on a cross tab report.
Any ideas?
Thanks

 

[boblarson]

What is x? Shouldn't it be

val(left({SUMPNPOD.ODSDAT},2) etc.

 

[Rob_Radley]

wow, that file data did not print right
let me try again.

[FONT=Times New Roman][SIZE=3]Sku#                Date              ODQTY[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]202005               0                   700[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]202008            60908              25[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]202008            0                      25[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]202231            60908              100                                                                              [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]202231            60208              100                  [/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]202231            0                      200[/SIZE][/FONT]

 

[Rob_Radley]

I am not sure. I am very new to this. I found this formula on another help page for converting dates in a mmddyy format to a crytal date format. This is the exact formulat I have used when there are no 0 dates and it has worked every time.

stringvar x := totext({SUMPNPOD.ODSDAT},"000000");date(2000 + val(right(x,2)),val(left(x,2)),val(mid(x,3,2)))

 

[boblarson]

Okay, so your date field actually does have a zero because it really isn't a date field at all. So, you should use:

if {SUMPNPOD.ODSDAT}<> 0 then
stringvar x := totext({SUMPNPOD.ODSDAT},"000000");date(2000 + val(right(x,2)),val(left(x,2)),val(mid(x,3,2))

 

[Rob_Radley]

Thanks a bunch, that did the trick.

 

[boblarson]

Whew! Glad we finally got that worked out for you