• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 720
  • Last Modified:

nText field pulling blank records when I use Is Not Null

Is there something else I can use to check this?  I have a linked SQL table in access.  The SQL field is a ntext field.  When I run a query and say Is Not Null it is still in some cases pulling a blank record.  Thanks all
0
sbornstein2
Asked:
sbornstein2
  • 9
  • 6
1 Solution
 
NightmanCTOCommented:
Is the record blank or null?
Check whether or not it is an empty string    column=''.
Or perhaps it has been padded RTRIM(column)=''.
0
 
sbornstein2Author Commented:
ya well I tried in access "Is Not Null and <> """.  But I get an error when I say the <> "" in access against that field.
0
 
sbornstein2Author Commented:
it is blank I beleive
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
NightmanCTOCommented:
Blank is not null, but empty string. Do you have access to Query Analyser?

If so, run this:

SELECT * FROM yourTable WHERE RTRIM(YourColumn)=''
0
 
sbornstein2Author Commented:
I cant access Query Analyzer right now unfortunatly.  I can only link to the table.  It is showing in design a memo field converting the SQL field Text to say that.  But I return approx 10 or so records that are showing my text field as blank in the query when using the Is Not Null only.
0
 
NightmanCTOCommented:
Then those records are not null, but probably empty string.

Post your query here and I'll see if I can modify it for you.
0
 
imran_fastCommented:
hi,
Is it showng <logn text> in enterprise manager? Can you verify that?
0
 
sbornstein2Author Commented:
it is just showing blank in Enterprise Manager and I dont want to change the datatype to a varchar(4000) etc. because it is setup using replication right now. I dont want to mess with that.
0
 
NightmanCTOCommented:
So run this query in Query Analyser:
SELECT COUNT(*) FROM yourTable WHERE RTRIM(YourColumn)=''

What does that return?
0
 
sbornstein2Author Commented:
anytime I use the '' it never works on a Text field.  I am in SQL now and it is not ntext it is a text field.  Your query above returns:

Server: Msg 8116, Level 16, State 2, Line 1
Argument data type text is invalid for argument 1 of rtrim function.

I open the table and it is showing blank or an empty string.
0
 
NightmanCTOCommented:
Do this then
SELECT COUNT(*) FROM yourTable WHERE RTRIM(CAST(YourColumn as nvarchar(4000)))=''
0
 
sbornstein2Author Commented:
4850 rows out of about 9K.  So looks like there is quite alot.
0
 
sbornstein2Author Commented:
should I try an update to Null on these?
0
 
sbornstein2Author Commented:
ya that worked I did an update to null where those came up and it now works.  Thanks for your help though.  I wonder what I can do for the future for this other than changing the datatype
0
 
NightmanCTOCommented:
Identify how it became '' in the first place. i.e. prevent unexpected data from coming in to your database from the UI by validating it.

Good to see it's working now.
0
 
sbornstein2Author Commented:
yup thanks Nightman.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now