Macro Run-Time Error

[christimess]

I created the following macro:
Sub Percent ()
X=VAL((Field$("OVERTIME_HOURS")) / VAL(FIELD$("AL_HOURS"))
DerivedField Str$(X)
End Sub
"OVERTIME_HOURS" is a derived field
"AL_HOURS" is a column from the AL_CHK_HRS_ERN table.
When I test the macro it states it is compiled successfully.

When I go to run the report I get the following error message at the end:
ReportBasic Macro Error
Run-Time Error in Macro: Percent,
Procedure: Percent Overflow

Thanks!

 

[CharlesCook]

Check AL_HOURS to make sure it is not null or 0

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[ReportSmithing]

Have your last statement read: DerivedField(X) - since you're doing a numeric calc. Plus, you will want to add multiplication to your query and multiply the whole formula by 100 in order to obtain the percentage.

Also, check and see if there are any nulls you're dealing with in your queary - you can place some if/then statements to exclude your nulls which will cause your overflow too.

 

[christimess]

Hi,
Thanks for the help. I think what I need is an NVL function, however where I am stuck is that if the value is 0 then what should I be changing it to to make the above macro work?
Thanks!

 

[CharlesCook]

x / 0 = ERROR

x / Null = Error

x / 1 = x

I would use 1 OR

DerivedField(0)

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining