Defining variable account numbers

[system]

Hi,

I’ve trying to define a variable account number for customers to pay into.

We have a field called ZVaccNum that we have populated for those that need to pay into that account number. We also have a default account number.

I have defined a Formula Field called “ACCDefault” and all it contains is “AC: ######”

I’ve defined another formula field called “ACC” and it contains the following code.

Version 1
if {CUSTOMERS.ZVAccNum} > 0 then “AC: {CUSTOMERS.ZVAccNum}”
else {@ACCDefault}

Version 2
if {CUSTOMERS.ZVAccNum} = 0 then {@ACCDefault}
else “AC: {CUSTOMERS.ZVAccNum}”

Both versions both return the value “AC: ######” listed in ACCDefault

I need it to return either the value in the field ZVAccNum if the field is greater than 0 or @ACCDefault.

Can anyone help?

---

IT-Joe (BOB member since 2019-02-28)

[system]

Is ZCAccNum a number field in the database or is it a string field? Also, can it have a null value? If it could be null you can change the formula like this:

if isnull({CUSTOMERS.ZVAccNum}) or {CUSTOMERS.ZVAccNum} = 0 then {@ACCDefault} else "AC: " + ToText({CUSTOMERS.ZVAccNum}, 0, ‘’)

(this assumes that ZVAccNum is a number field.)

Another way to do this would be to set the Null Handling option in the formula editor to use default values for nulls.

-Dell

---

hilfy (BOB member since 2007-04-16)

[system]

Hi Hilfy,

Sorry for the late reply. It’s been a bit busy the last couple of weeks.

The field is a number field in the database. SQL DB showing it as INT.

I’ve checked the data in the field and for non-populated fields, the data comes up as 0 otherwise it is populated.

I’ve tried your syntax above and it still didn’t pick up the correct details.

I’m new to Crystal and so not sure where you’re referring to when you refer to the Null Handling option.

Thanks

---

IT-Joe (BOB member since 2019-02-28)

[system]

Sorry for my own late reply!

The option for null handling is in two places:

1. In the Formula Editor there are two menu bars. In the bottom menu bar there are two drop-down lists. The one on the right has two options: “Exceptions For Nulls” and “Default Values for Nulls”. This option only affects the current formula.

2. Go to File>>Options>>Formula Editor. Toward the bottom of the screen is a drop-down list for “Null Treatment” that has the same two options. Changing this setting will affect all new formulas created after the setting was changed.

-Dell

---

hilfy (BOB member since 2007-04-16)