IIF Query for Numeric Values in Text Field - #ERROR

[CFJOHN1]

I’m attempting to flag interest rate spread errors and omissions in a file
from a sales database using IIF statements I adapted from another database
(I’m a novice at this). The field I’m querying is text format and contains
both alpha and numeric values. When I test for a null value the IIF works
fine, but when I test for a numeric value I get an #ERROR.

Spread Error1: IIf(([Tbl 1 Eligible Closed Deposit Opps Appended]!Spread Is
Null),1,0)

Spread Error2: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]="NIB" And
[Tbl 1 Eligible Closed Deposit Opps Appended]!Spread<4),1,0)

Spread Error3: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]="NIB" And
[Tbl 1 Eligible Closed Deposit Opps Appended]!Spread>6),1,0)

Spread Error4: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]<>"NIB" And
[Tbl 1 Eligible Closed Deposit Opps Appended]!Spread<0.1) Or ([CCC Fall 2007
Eligible Products]![IB/NIB]<>"NIB" And [Tbl 1 Eligible Closed Deposit Opps
Appended]!Spread>6),1,0)

I originally had Spread Error 2 & 3 in a single “and†IIF and split them to
see if I could isolate the problem, but no luck.

Any ideas? I can't control the formatting in the source application, can
only work with a file extract. I'm working with Access 2003.

 

[Allen Browne]

There are 2 fields froom table [CCC Fall 2007 Eligible Products] in your
expression:
[IB/NIB]
[Spread]
If you open the table in design view, are *both* these fields Text type?

If so, the quotes around the NIB value correct, but you will also need
quotes around the "4" on the Spread field. This probably won't work as you
expect: when you use text comparisons, 10 is less than 2 (because the first
character - the one - is less than the first character of the second value -
the two.) To correct this you would need to use Val() to get the value. But
Val() can't handle nulls, so you need to use Nz() inside the Val().

Of course, the simpler alternate would be to change Spread in the table so
it is a Number type field.

 

[CFJOHN1]

Thanks Allen. I'll work with this and see what I can accomplish. Curt
Johnson

There are 2 fields froom table [CCC Fall 2007 Eligible Products] in your
expression:
[IB/NIB]
[Spread]
If you open the table in design view, are *both* these fields Text type?

If so, the quotes around the NIB value correct, but you will also need
quotes around the "4" on the Spread field. This probably won't work as you
expect: when you use text comparisons, 10 is less than 2 (because the first
character - the one - is less than the first character of the second value -
the two.) To correct this you would need to use Val() to get the value. But
Val() can't handle nulls, so you need to use Nz() inside the Val().

Of course, the simpler alternate would be to change Spread in the table so
it is a Number type field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I’m attempting to flag interest rate spread errors and omissions in a file
from a sales database using IIF statements I adapted from another database
(I’m a novice at this). The field I’m querying is text format and
contains
both alpha and numeric values. When I test for a null value the IIF works
fine, but when I test for a numeric value I get an #ERROR.

Spread Error1: IIf(([Tbl 1 Eligible Closed Deposit Opps Appended]!Spread
Is
Null),1,0)

Spread Error2: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]="NIB" And
[Tbl 1 Eligible Closed Deposit Opps Appended]!Spread<4),1,0)

Spread Error3: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]="NIB" And
[Tbl 1 Eligible Closed Deposit Opps Appended]!Spread>6),1,0)

Spread Error4: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]<>"NIB" And
[Tbl 1 Eligible Closed Deposit Opps Appended]!Spread<0.1) Or ([CCC Fall
2007
Eligible Products]![IB/NIB]<>"NIB" And [Tbl 1 Eligible Closed Deposit Opps
Appended]!Spread>6),1,0)

I originally had Spread Error 2 & 3 in a single “and†IIF and split them
to
see if I could isolate the problem, but no luck.

Any ideas? I can't control the formatting in the source application, can
only work with a file extract. I'm working with Access 2003.