CLng Calcluation on " " text fields

B

Bettergains

Hello: in the examples below "Test_ID" is a text field and some of these are
empty. I am trying to test for empty / null fields and if null, convert to 0
(preferably 00000). My attempts below are returning #errors. I would prefer
to NOT to have to do this in two columns (ie, cvar them first, then clng
them) which DOES providde the correct result. Any ideas?

RD: IIf(CLng([Test_ID])=" ",Null,CLng([Test_ID]))

RD: iif(CLng(Test_ID) is null, null, CLng([Test_ID])))

RD: iif(CLng(Test_ID) is null, 0, CLng([Test_ID])))
 
L

Larry

Why not just use:

IF ISNULL(TEST_ID) THEN
TEST_ID = "00000"
END IF

The problem with your code is that CLng is trying to convert a value to a
Long number but null or " " is not a valid long number.

Thanks,
Larry
 
D

Dirk Goldgar

Bettergains said:
Hello: in the examples below "Test_ID" is a text field and some of
these are empty. I am trying to test for empty / null fields and if
null, convert to 0 (preferably 00000). My attempts below are
returning #errors. I would prefer to NOT to have to do this in two
columns (ie, cvar them first, then clng them) which DOES providde the
correct result. Any ideas?

RD: IIf(CLng([Test_ID])=" ",Null,CLng([Test_ID]))

RD: iif(CLng(Test_ID) is null, null, CLng([Test_ID])))

RD: iif(CLng(Test_ID) is null, 0, CLng([Test_ID])))
Click to expand...

How about (untested):

RD: IIf(Len([Test_ID] & "")=0,"00000",[Test_ID])

I'm assuming that RD should be a text field; otherwise just

RD: CLng(IIf(Len([Test_ID] & "")=0,0,[Test_ID]))

Not that, if [Test_ID] doesn't allow zero-length strings, you can use
the Nz() function:

RD: Nz([Test_ID], "00000")
 
B

Bettergains

Thanks, Dirk. They all work...mystery solved. (Text was the way I went in
the end.)

Dirk Goldgar said:
Bettergains said:
Hello: in the examples below "Test_ID" is a text field and some of
these are empty. I am trying to test for empty / null fields and if
null, convert to 0 (preferably 00000). My attempts below are
returning #errors. I would prefer to NOT to have to do this in two
columns (ie, cvar them first, then clng them) which DOES providde the
correct result. Any ideas?

RD: IIf(CLng([Test_ID])=" ",Null,CLng([Test_ID]))

RD: iif(CLng(Test_ID) is null, null, CLng([Test_ID])))

RD: iif(CLng(Test_ID) is null, 0, CLng([Test_ID])))
Click to expand...

How about (untested):

RD: IIf(Len([Test_ID] & "")=0,"00000",[Test_ID])

I'm assuming that RD should be a text field; otherwise just

RD: CLng(IIf(Len([Test_ID] & "")=0,0,[Test_ID]))

Not that, if [Test_ID] doesn't allow zero-length strings, you can use
the Nz() function:

RD: Nz([Test_ID], "00000")

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Top