Solved

update query wont work

Posted on 2014-03-13
42
417 Views
Last Modified: 2014-03-26
Hi all,

I have a sql table linked to an access db, the table has a Bit field (yes/no in access), I am trying the following and its affecting zero records

update patients set patients.active = 0 where patients.active is null

also tried

update patients set patients.active = false where patients.active is null

however when i just select * from patients where active is null, I get those records...weird!
0
Comment
Question by:bfuchs
  • 12
  • 12
  • 3
  • +6
42 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Screwy.  Try this

update patients set active = 0 where IsNull(active) = True
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
nope, that does not return anything by select either..
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Just out of curiosity, where are you executing this, in the Access query designer, or in SQL Server?
Might be worth sending us a screen shot.
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
see attached.
Doc7.doc
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Jim, any hope here..?
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Couple of possibilities:

(1)  The screen shot in the designer grid will translate to the second column (my eyes are not THAT good) being true OR the third column IS NULL.  the OR is because the True and IS NULL parts are on different rows in the criteria section.  If you want an AND, then they have to be on the same row.

(2)  The third column does not contain NULL values.  It may contain blanks that look like NULLs, although that wouldn't be possible for a yes/no - bit column, but would be for a text column.

So, while were on the subject, verify real quick that Active is a yes/no - bit column.

Just for kicks and giggles, let's run the below query, which will return all possible values of the yes/no column, and a count of records:
SELECT active, COUNT(active) as the_count
FROM patients
GROUP BY active
ORDER BY active

Open in new window

0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Jim,
(I just returned from vacation)

regarding your first comment, I purposely did that or, since I wanted to include both scenarios in the query results.

About the second comment, I am attaching screenshots from where you can get a closer look of what's going on here..., first i tried your sql suggested and got one results, then tried another count and got a different results, which is my concern that indeed there are null (or perhaps something else..) stored there as well.., and finally just to reassure that we are talking about an YesNo field, I included a screenshot of the table design too-:)
Doc1.doc
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
Ok. First off  -- if you open the linked table in the datagrid view can you add a record?

If not then anything else said is useless. The linked table has to be in an updtable state.

If you can add a record, you have to understand that Access does false as 0. It's true is -1.

But the bit datatype in SQL is 0 for false and 1 (positive 1) for true. I have found the best solution is in code of using the literal number and and not the default code.
0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
Do you have a primary key in the SQL table?

Access usually cannot update a linked SQL table if there is not primary key. It can find the rows where a given field is Null, but cannot return to the table to update it without the primary key.
0
 
LVL 39

Expert Comment

by:thenelson
Comment Utility
Similar to Jim P. comment.

Open the table and set one of the active fields to 0. If you can't do that, then the problem is in the table, not the query.
0
 
LVL 76

Expert Comment

by:arnold
Comment Utility
Agree with other commenters, how is the column defined? Do you have any column that is null, see if you check for empty string '' versus null.
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi experts,
1- Yes, I am able to add/edit/delete records in this table.
2- there is a primary defined there (attached screenshot)
3- I am able to update manually those null records to 0, however strange as it sounds, an update query does not work, returns 0 records updated...
4- when i do select * where active = "" I get type mismatch.

again, when I select * from patients where active is null, I get those records and when I manually re-type the 0 and then run again the query I see those records aren't there anymore, its just the update query that fails.
Doc1.doc
0
 
LVL 76

Expert Comment

by:arnold
Comment Utility
Use '' rather than "" two different things.

What is the definition of the active column?
Change it to default to 0 and not null.

Try the following, update patients set active=0 where active <> 1
0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
Are you sure that you have Null values in the field?

If the number of records in the table is 5217 (the sum of the count for 0 and for -1), then you do not have any Null value.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
Please try this version of the update query:
update patients 
set patients.active = 0 
where Trim(patients.active & "") = 0

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
You could go flipside.
Instead of checking for null--which ain't working--check for anything that ISN'T true or false!

Update patients
set patients.active = 0
where patients.active<> true and patients.active<> False


If it's a linked table (Oracle and their bloody emptystring instead of null!) and the bit datatype is grieving you:

Update patients
set patients.active = 0
where patients.active<> -1 and patients.active<> 0 and patients.active<>1


That ought to git 'er, no matter what data is hiding in the column now!

Does a
select * from patients
where patients.active<> -1 and patients.active<> 0 and patients.active<>1

select the right records?
0
 
LVL 39

Expert Comment

by:thenelson
Comment Utility
Microsoft recommends using Len(patients.active & "") = 0 to find zero length and null values as the fastest technique so you can try:
update patients 
set patients.active = 0 
where Len(patients.active & "") = 0

Open in new window

I can't remember if the Len function is supported in the query engine. If you put Len(patients.active & "") within the query itself and an error returns that Len cannot be found, the query would need to be built in VBA.
0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
The recommendation of checking for a length=0 is good for strings in VBA, because of the way strings are stored in memory. It does not do any good for Boolean values, and working at the database level is a lot faster than working in VBA.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
Note: please ignore my earlier comment, http:#a39950225
I typed the Trim() function instead of the Len(), which thenelson has correctly posted in his comment, http:#a39950832
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Over the years, there has been much discussion of the efficiency of VBA recordset code VS  SQL operations.

In practical operation, until you get into multi-million row tables, the results of well-written code and well-written SQL  -- both in Access -- are comparable.  The folks behind the scenes have gotten REALLY good at ensuring both of them run well.  Which makes sense--when identical results are what's desired, optimization should result in similar methods being used behind the scenes.

Be that as it may, attached is sample that will generate a million row table with a Boolean field with random true, false and null values.  Go ahead and breakpoint the line that shows the nulls being hammered in, so you are sure that it is happening.  Then run Query1.  No nulls. In this sample, Access sets the nulls to false!

I take it that, being a linked table, there are nulls in the source environment--but none in Access?
NullBoolean.mdb
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Here's the same sample, but altered to write a text column along with.
It looks very much like Access ain't gonna show a null value in a Boolean column.

[REDACTED A REFERENCE TO A COMPETING URL THAT IS NOT PERMITTED]

So, the question becomes--do you have the ability and permission to add a computed column to the source table with a non-boolean datatype?  Because that's what you will need.
NullBoolean-v1.mdb
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi experts,

I am attaching in a notepad file all suggestions tried (I think its all what each of you suggested), unfortunately none of them worked, most returned zero records updated and one of them only contained the not null values, also attaching what I am still remaining with, a count of 58 null records.

 @Nick67,
didn't look at your latest yet, will reply afterwards.

Thanks,
Ben
Doc1.doc
sqlUpdate.txt
0
 
LVL 76

Expert Comment

by:arnold
Comment Utility
Not sure whether the  group by and order by had an adverse behavior with nulls.

having a column defined as true/false and default to false.

you keep posting the suggestions, but without the response.
Which SQL server are you linking to the Access MDB and with which credentials if any.
and which ms access MDB are you using?

Can you insert a new record? can you delete the record you just inserted? this is along side the question nick67 dealing with permissions.  
Presumably you can update a record that has 0 or 1.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Sigh
@Arnold
Murdering responses that consist solely of a URL, which may in the future break I get.
The idea is to present an Answer.  Nobody following the question is left to wonder what the URL will show.  Killing the evidence that MS Access will not allow a null in a yes/no data field is tiresome.

@Ben
Google 'How do you deal with NULL values in columns of type boolean in MS Access?'
The first link will lead to an article.  Within it is a link to an Allen Browne (highly respected Access Expert) comment
You could then Google 'As Jerry explained, the Yes/No data type in Access is capable of handling 2 states only. Null is not supported'

Your linked table (in Access) contains no nulls.  Access's Boolean datatype --in tables-- is only capable of containing true or false (-1 or 0)  Nothing else.  Access interprets NULL's from linked tables as FALSE.  The only time you will see Access showing NULL in a Boolean field is in a query, where a left or right join leads to non-existent data.

Access Boolean values in a table CANNOT be null.
Here's a link from Allen Browne's site
http://allenbrowne.com/xbase-05.html
Here is another
http://www.w3schools.com/sql/sql_datatypes.asp

Hence, your problem.
This behavior is by design.
See my previous comment

And way back up to, Jim Horn said it too
(2)  The third column does not contain NULL values.  It may contain blanks that look like NULLs, although that wouldn't be possible for a yes/no - bit column, but would be for a text column.
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Dear valued experts,

 So lets agree that it can't be a null value in a bit/YesNo field.

Not sure if you were able to see my screenshots, but what I am showing there is that when i run a count(ID) grouped by active, I get 3 count of records, one for the active, one for the not active and one for the... so lets called a different type of unknown value...

In my case when I select * where active is null I get to see those values...

My FE is Ms Access 2000/2003 and BE is Sql express 2005.

(as far I am aware of, I have no problem doing anything with that table, what show I post here to verify that???)
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
So, in SSEE 2005, in the actual table, what is the datatype?
Since you are hunting for nulls, I expect that it isn't bit, which is the Access equivalent to Boolean.  Is it tinyint or smallint or int?

In SSEE, the Management Express tool will show NULL values for bit fields.
If you have a need for three value logic, then to make Access play nice, you can experiment with changing the field in SSME to tinyint -- Access will then allow it to have a null value.  Do THAT only as a last resort--and test it on a throwaway table FIRST.  It's tricky!

What also gets tricky is that Access knows that your back-end is SQL and what is in there (if it is type bit is 0 and 1.  In any and all VBA code, if you test for TRUE by

table1!somevalue = -1

that breaks.

It has to be
table1!somevalue = TRUE

If you do not need three value logic, just run the update for patients.active <> 0
Then go through your code, and find all the places that you FAIL to give .Active a value--and ensure that you do.

The reason the  Count() is giving three grouping is that SSEE does have three for bit fields -- true, false and null-- and SSEE is doing that query, not Access.  But Access CANNOT show you that directly, because it cannot show NULL as a result for a Boolean field
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Nick67,

1- the field is defined as bit type in sql
2- I do not need 3 value logic here.
3- going forward I guess the default option would be the best.
4- just need a way to update the current null values to 0.
(what you're mentioning where active <>0 would include -1).
5- In my entire app, I never paid attention to null values in yes/no bit fields, (thinking like all you above that they cant be..) until recently I got complaints that some records aren't showing up...This is why I am so eager to get this update done ASAP.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
Comment Utility
You can run a select, and then the update in the SQL Server Management Express (SSME) tool, just to make sure that you are finding and changing the right stuff.

So in SSME, click New Query
Type in

Use [Whatever your database name is]
Select * from (probably will be) dbo.Patients where dbo.Patients is null

If that returns the right records then
Use [Whatever your database name is]
update dbo.patients set dbo.Patients.active = 0 where dbo.patients is null ]

Then design the table.
Set a default for Active to equal zero (or one!  why create it if they aren't going to be active?).

That should do it.

Wouldn't hurt to go into Access and see where the records for Patients get created, and ensure that the value for Active is getting explicitly set and saved to the database.

You can't tell Access to set a Boolean field to equal NULL -- but as you have seen, to your regret, SQL Server WILL set it to NULL if you don't specify it!  I ran into that when I upsized my main app.

Next question: what other bit field have that minefield hiding in them :)
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
OK in ssms it worked..!!
however still reserving some points for the expert who figures out how to do it in access-:)
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
No Biggie.
Divvy it up however you feel it best.
Glad you've got it figured out

Nick67
0
 
LVL 16

Expert Comment

by:DcpKing
Comment Utility
Does patients have a primary key? If not, add one and try again

hth

Mike
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
"...who figures out how to do it in access"

In my app, I have some SSEE 2005 bit fields with NULL in them
tblInsDetails.Complete to be specific

This works
UPDATE tblInsDetails SET tblInsDetails.Complete = False
WHERE (((tblInsDetails.Complete) Is Null));


Give
UPDATE Patients SET Patients.Active = False
WHERE (((Patients.Active) Is Null));

a try

Post up the messages that come up as you try it.
At the very beginning, you said you've tried this--but given that it works in my A2003-SSEE 2005 environment, I am interested in what error messages it may generate in your environment.
(Screen-shotting posts are much better if you take the shot (ALT-PRTSCR) paste it into MSPaint, trim it and save it as a jpg, rather than pasting it into Word and saving it as a .doc)
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
@DcpKing
yes already posted a screenshot displaying the primary key included.
(besides that if that would be the case, I would get the message "this recordset is not updatable.." instead of just "0 records updated")

@Nick67
1- there is no error message, simply when i run select * from patients where active is null I see records (currently 1 after yesterdays update..), and when i run update patients set active = false where active is null I get 0 records updated.
2- is there a way to include multiple screenshots in one file by doing it in paint?
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
update patients set active = false where active is null I get 0 records updated.

Don't use the false use 0 instead.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
@Ben,
1. I get the warning that an action query is about to run -- you SHOULD get that warning unless you have disabled it -- and then it runs and does its thing.

2. Paste in more screenshots, move them so they don't overlap and give 'er.

See the attached shot of the query and result.

Next question:  Can you successfully run ANY action query?
ben.jpg
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
@Jim P
nope, same thing.

@Nick67
I meant that warning message, see attached.
The most strange thing is that when I switch to datasheet view I see them.
Untitled.jpg
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
So,

Give me a screenshot of the Datasheet View of

Select Patients.Active from Patients
Where Patients.Active is null;


If that gives records -- and the right ones -- the next thing to try will be an IN clause subquery
For my tblInsDetails.Complete field, the QBE gives

UPDATE tblInsDetails SET tblInsDetails.Complete = False
WHERE (((tblInsDetails.JobID) In (SELECT tblInsDetails.JobID
FROM tblInsDetails
WHERE (((tblInsDetails.Complete) Is Null)))));


But as you could see from my screenshot in the previous post, Access found records and was willing to do the update--so something quite strange is bedeviling you.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Fun things!
In my environment, a count() query run on the field with nulls shows three groupings, Nothing ("", <>, '', emptiness), zero and -1.  Mind you, the count of the nothings is also zero

But
SELECT tblInsDetails.Complete, Count(IsNull([Complete])) AS MyNull, Count(tblInsDetails.Complete) AS CountOfComplete
FROM tblInsDetails
GROUP BY tblInsDetails.Complete;

does show the correct count in MyNull, so
Try
UPDATE Patients SET Patients.Active = False
WHERE (((IsNull([Patients].[Active]))=True));
0
 
LVL 3

Author Closing Comment

by:bfuchs
Comment Utility
Hi Nick, At this point I guess you deserve the points regardless..

As of the access option, My manager should just find out that I am spending the time on this when I have an alternative solution I'm afraid...So let's leave at for another opportunity.

Thanks a lot!
Ben
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
OK :)

Nick67
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now