HELP!!!!getting Null value when criteria "Is Not Null"

[DMUM via AccessMonster.com]

Hello, I Imported a file from Excel to Access. I am doing some comparisons
on some of the fields to ensure I have no duplicate information. One of the
fields I am working with contains empty fields , so my query is basicly
"Select prsa_num from tblImport where prsa_num is Not null". I am using the
Access query screen and putting the "Is Not Null" in the Criteria line. when
I run the query, it returns 1 empty field with the row of prsa_num. Seems
access is considering one of the Excel fields in the prsa_num column as
having data even though there is nothing I can see. I found this to be true
also when doing an import from Excel where the Excel file was created from
another import. My import to Access always pulls in additional rows that
have no data in them, as many as 40 empty rows. Does any one know what
causes this. More specifically, can anyone tell me how to address my query
issue, because it is inserted/appending a blank field to my table.

Thank you

By the way, I tried something I saw on one of the other threads - Len
[prsa_num]<>"", that didn't work either

 

[Guest]

They aren't null. There is also something called an empty string or there
just might be spaces.

SELECT *
FROM tblImport
WHERE (tblImport.prsa_num)=""
OR (tblImport.prsa_num) Is Null
OR (tblImport .prsa_num) Like " *" ;

 

[John Spencer]

You are right you can't see it. You probably have either a zero length
string or string of spaces.

Try changing the criteria to
WHERE prsa_num is Not Null And prsa_num <> ""

Or if you suspect that there is a string of spaces in the field, this should
filter out all records where the value is Null, a zero-length string, or
multiple spaces.

WHERE Trim(Nz(prsa_num,"")) <> ""

 

[DMUM via AccessMonster.com]

Hi John

Oddly this didn't work by putting it in the criteria string. I actually had
to use the SQL window and it works there. If I look at the Access query
window, it creates the WHERE as a seperate field.

Things that make you go hmmmm!

Thanks all

You are right you can't see it. You probably have either a zero length
string or string of spaces.

Try changing the criteria to
WHERE prsa_num is Not Null And prsa_num <> ""

Or if you suspect that there is a string of spaces in the field, this should
filter out all records where the value is Null, a zero-length string, or
multiple spaces.

Hello, I Imported a file from Excel to Access. I am doing some
comparisons

[quoted text clipped - 20 lines]

By the way, I tried something I saw on one of the other threads - Len
[prsa_num]<>"", that didn't work either

 

[DMUM via AccessMonster.com]

Hi Jerry

I tried all of your suggestions, except the LIKE before, but looking for NOT
null or empty values. But to no avail. Thanks for your input though.
Luckily John's suggestion worked.

Thank you

They aren't null. There is also something called an empty string or there
just might be spaces.

SELECT *
FROM tblImport
WHERE (tblImport.prsa_num)=""
OR (tblImport.prsa_num) Is Null
OR (tblImport .prsa_num) Like " *" ;

Hello, I Imported a file from Excel to Access. I am doing some comparisons
on some of the fields to ensure I have no duplicate information. One of the

[quoted text clipped - 14 lines]

By the way, I tried something I saw on one of the other threads - Len
[prsa_num]<>"", that didn't work either