date related error

[Former Member]

I am trying to take a field, that is a date, that represents the date of the last order from a particular customer and to that add a calculated number of days, a number, expected til receipt of the next order.

So the formula is {@Last Total Ord Date}+({@New Total Days}). This occasionally gives me an error that states "the number of days is out of range".

An instance in which it does NOT give the error is with a customer whose last order date was 10/31/11 and whose number of days til next order is 121. It correctly gives me the result of 02/29/12.

The error is resulting with the next customer, in an instance where that customer's last order date was 11/16/11 and whose number of days til next order is 60.

Awfully confused here......sorry if I appear to be the newbie that I am.

Help would be greatly appreciated. Thank you in advance!

Ted

[JWiseman]

hey Ted,

in order for the issue to be debugged we'd need the syntax for both formulas that are being referenced.

jamie

[Former Member]

The formula for the last order date field is:

if {#Last Ord date 500}>={#Last Ord date 1000} and

{#Last Ord date 500}>={#Last Ord date 350}then

{#Last Ord date 500}

else

if {#Last Ord date 1000}>={#Last Ord date 500}and

{#Last Ord date 1000}>={#Last Ord date 350}then

{#Last Ord date 1000}

else

if {#Last Ord date 350}>={#Last Ord date 500}and

{#Last Ord date 350}>={#Last Ord date 1000}then

{#Last Ord date 350}

The formula for the days til next order field is:

if {@Ord. Interval 500}=10000 and

{@Ord. Interval 1000}=10000 and

{@Ord. Interval 350}=10000 then

180

else if

{@Ord. Interval 500}<1 and

{@Ord. Interval 1000}<1 then

{@Ord. Interval 350}

else if {@Ord. Interval 1000}<1 and

{@Ord. Interval 350}<1 then

{@Ord. Interval 500}

else if {@Ord. Interval 500}<1 and

{@Ord. Interval 350}<1 then

{@Ord. Interval 1000}

[JWiseman]

sorry, but it's going to be next to impossible to debug any formulas that we can't see. you've got formulas referencing formulas referencing other formulas.

to make sure the first formula works you can change the syntax to something like the following to get rid of IF THEN ELSE statements...

maximum (numbervar array ma:= [{#Last Ord date 500}, {#Last Ord date 1000}, {#Last Ord date 350}])

i would also recommend to go to the File menu > Report Options > and check on Convert Nulls to default for both fields and formulas.

you can also try tracing back the error in the formula debugger...you can look at all of the individual values in the debugger and perhaps figure it out there.

jw

[Former Member]

JW, i entered the following and received an error message stating "a number array is required here" with the first field being highlighted {#Last Ord date 500}.

maximum(numbervar array ma:= {#Last Ord date 500}, {#Last Ord date 1000}, {#Last Ord date 350})

[abhilash_kumar]

Hi,

Here's how you can get rid of the array error. Use this formula:

WhilePrintingRecords;
numbervar array ma;
ma := [tonumber({#Last Ord date 500}),tonumber({#Last Ord date 1000}),tonumber({#Last Ord date 350})];
Maximum(ma);

And, for the 'Out of range error', have you tried the dateadd() function? So, here's how you would use it:

dateadd("d",{@New Total Days},{@Last Total Ord Date})

If this throws the same error, follow Jamie's suggestions to see what the formula has accumulated until the error. The top left area of the formula window would show the variables and their accumulated values.

I suspect that the {@New Total Days} is returning a much higher value on a few occasions. Drop that formula on the details section or wherever it's appropriate and see if it is indeed returning 60.

-Abhilash