isnull not working

[Jim's wife]

I imported a tab delimted text file and am now trying to select all fields
that have a blank field in fieldB.

isnull(fieldB)
nothing is returned
i tried fieldB = ""
= " "
I trimmed it and still nothing

what else can I try?
The fields that are blank have no spaces, they are just blank. What is the
character in there?

Thanks

[Scott McDaniel]

Check the Length of the field:

If len(YourField) = 0 Then

do whatever<<

End If

To correctly check IsNull:

If IsNull(YourField) Then
...whatever here
End If

--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP
"Jim's wife" <ta*****@mindspring.com> wrote in message
news:Kp********************@news4.srv.hcvlny.cv.ne t...

I imported a tab delimted text file and am now trying to select all fields
that have a blank field in fieldB.

isnull(fieldB)
nothing is returned
i tried fieldB = ""
= " "
I trimmed it and still nothing

what else can I try?
The fields that are blank have no spaces, they are just blank. What is the character in there?

Thanks

[Jim's wife]

That does not work either
"Scott McDaniel" <sc***@no.spam.thedatabaseplace.net> wrote in message
news:FP********************@comcast.com...

Check the Length of the field:

If len(YourField) = 0 Then

>>do whatever<< End If

To correctly check IsNull:

If IsNull(YourField) Then
..whatever here
End If

--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP
"Jim's wife" <ta*****@mindspring.com> wrote in message
news:Kp********************@news4.srv.hcvlny.cv.ne t...

I imported a tab delimted text file and am now trying to select all

fields that have a blank field in fieldB.

isnull(fieldB)
nothing is returned
i tried fieldB = ""
= " "
I trimmed it and still nothing

what else can I try?
The fields that are blank have no spaces, they are just blank. What is

the

character in there?

Thanks

[Lyle Fairfield]

"Jim's wife" <ta*****@mindspring.com> wrote in news:Kp6Qb.4034$O22.1076660
@news4.srv.hcvlny.cv.net:

what else can I try?

posting your code ...

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Bruce Pick]

Using Scott McDaniel's idea to test for length, I'd also use the Asc()
function to get the Ascii value of the character. If it doesn't show in
normal views, it is likely a non-printing character, but getting its
Ascii value will give you a handle on it.

In a query's field, testing a field named "EmailAddr":
TestEmail: IIf(Len([EmailAddr])>=0,Asc([EmailAddr]),999)

This shows 999 if null, or the character's ascii value if not null
(Asc() returns an integer)

You could also use:
TestEmail: IIf(Len([EmailAddr])>=0,Asc([EmailAddr]))
which will leave a null display if Length is not >= 0.

Bruce Pick

Jim's wife wrote:

I imported a tab delimted text file and am now trying to select all fields
that have a blank field in fieldB.

isnull(fieldB)
nothing is returned
i tried fieldB = ""
= " "
I trimmed it and still nothing

what else can I try?
The fields that are blank have no spaces, they are just blank. What is the
character in there?

Thanks

[Wayne Morgan]

Where are you trying to do this. In a query, you would choose fieldB in the
Field row and for the Criteria use

Is Null

as 2 words.

--
Wayne Morgan
MS Access MVP
"Jim's wife" <ta*****@mindspring.com> wrote in message
news:Kp********************@news4.srv.hcvlny.cv.ne t...

I imported a tab delimted text file and am now trying to select all fields
that have a blank field in fieldB.

isnull(fieldB)
nothing is returned
i tried fieldB = ""
= " "
I trimmed it and still nothing

what else can I try?
The fields that are blank have no spaces, they are just blank. What is the character in there?

Thanks

[Keith Wilby]

"Jim's wife" <ta*****@mindspring.com> wrote:

what else can I try?

How about telling us where you're trying to use IsNull - in a query? In a
VBA function?

[Bruce Pick]

I tested this on my own table successfully:

In a query's field, testing a field named "fieldB":
TestFieldB: IIf(Len([fieldB])>=0,Asc([fieldB]),999)

This shows 999 if null, or the character's ascii value if not null
(Asc() returns an integer)

You could also use:
TestFieldB: IIf(Len([fieldB])>=0,Asc([fieldB]))
which will leave a null display if Length is not >= 0.

Bruce Pick

Jim's wife wrote:

I imported a tab delimted text file and am now trying to select all fields
that have a blank field in fieldB.

isnull(fieldB)
nothing is returned
i tried fieldB = ""
= " "
I trimmed it and still nothing

what else can I try?
The fields that are blank have no spaces, they are just blank. What is the
character in there?

Thanks

[Art Yates]

Because of 'null propagation' use:
IIf(Len([fieldB] & ' ' )>=0,Asc([fieldB]),999)
appending something to a null field, converts the null to a zero length
string
"Bruce Pick" <br********@comcast.net> wrote in message
news:Ps********************@comcast.com...

I tested this on my own table successfully:

In a query's field, testing a field named "fieldB":
TestFieldB: IIf(Len([fieldB])>=0,Asc([fieldB]),999)

This shows 999 if null, or the character's ascii value if not null
(Asc() returns an integer)

You could also use:
TestFieldB: IIf(Len([fieldB])>=0,Asc([fieldB]))
which will leave a null display if Length is not >= 0.

Bruce Pick

Jim's wife wrote:

I imported a tab delimted text file and am now trying to select all fields that have a blank field in fieldB.

isnull(fieldB)
nothing is returned
i tried fieldB = ""
= " "
I trimmed it and still nothing

what else can I try?
The fields that are blank have no spaces, they are just blank. What is the character in there?

Thanks

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.563 / Virus Database: 355 - Release Date: 1/17/2004

[Paul]

Try

if len(FieldB & vbnullstring) = 0 then
'do whatever
end if

"Jim's wife" <ta*****@mindspring.com> wrote in message
news:Kp********************@news4.srv.hcvlny.cv.ne t...

I imported a tab delimted text file and am now trying to select all fields
that have a blank field in fieldB.

isnull(fieldB)
nothing is returned
i tried fieldB = ""
= " "
I trimmed it and still nothing

what else can I try?
The fields that are blank have no spaces, they are just blank. What is the character in there?

Thanks

[Paul]

Try

if len(FieldB & vbnullstring) = 0 then
'do whatever
end if

"Jim's wife" <ta*****@mindspring.com> wrote in message
news:Kp********************@news4.srv.hcvlny.cv.ne t...

I imported a tab delimted text file and am now trying to select all fields
that have a blank field in fieldB.

isnull(fieldB)
nothing is returned
i tried fieldB = ""
= " "
I trimmed it and still nothing

what else can I try?
The fields that are blank have no spaces, they are just blank. What is the character in there?

Thanks

[Paul]

Try

if len(FieldB & vbnullstring) = 0 then
'do whatever
end if

"Jim's wife" <ta*****@mindspring.com> wrote in message
news:Kp********************@news4.srv.hcvlny.cv.ne t...

I imported a tab delimted text file and am now trying to select all fields
that have a blank field in fieldB.

isnull(fieldB)
nothing is returned
i tried fieldB = ""
= " "
I trimmed it and still nothing

what else can I try?
The fields that are blank have no spaces, they are just blank. What is the character in there?

Thanks

[Peter Flick]

Hi,

I have found similar cases using ODBC

The trick is to add text to the field
then look for the text at the start of the field.
[YourDataField]& "#@#"

There may be a better technical answer, but this works.

Peter

Jim's wife wrote:

I imported a tab delimted text file and am now trying to select all fields
that have a blank field in fieldB.

isnull(fieldB)
nothing is returned
i tried fieldB = ""
= " "
I trimmed it and still nothing

what else can I try?
The fields that are blank have no spaces, they are just blank. What is the
character in there?

Thanks