By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,501 Members | 831 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,501 IT Pros & Developers. It's quick & easy.

isnull not working

P: n/a


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
Nov 12 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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


Nov 12 '05 #3

P: n/a
"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)
Nov 12 '05 #4

P: n/a
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

Nov 12 '05 #5

P: n/a
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

Nov 12 '05 #6

P: n/a
"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?
Nov 12 '05 #7

P: n/a
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

Nov 12 '05 #8

P: n/a
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
Nov 12 '05 #9

P: n/a
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

Nov 12 '05 #10

P: n/a
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

Nov 12 '05 #11

P: n/a
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

Nov 12 '05 #12

P: n/a
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


Nov 12 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.