?
Solved

update query wont work

Posted on 2014-03-13
42
Medium Priority
?
430 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 12
  • 3
  • +6
42 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39927614
Screwy.  Try this

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

Author Comment

by:bfuchs
ID: 39927653
nope, that does not return anything by select either..
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39927671
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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 4

Author Comment

by:bfuchs
ID: 39927735
see attached.
Doc7.doc
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39928069
Hi Jim, any hope here..?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39929175
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 4

Author Comment

by:bfuchs
ID: 39935582
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.
ID: 39949240
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
ID: 39949347
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
ID: 39949385
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 79

Expert Comment

by:arnold
ID: 39949396
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 4

Author Comment

by:bfuchs
ID: 39949453
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 79

Expert Comment

by:arnold
ID: 39949478
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
ID: 39949590
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 46

Expert Comment

by:aikimark
ID: 39950225
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
ID: 39950781
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
ID: 39950832
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
ID: 39950888
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 46

Expert Comment

by:aikimark
ID: 39950894
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
ID: 39950994
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
 
LVL 26

Expert Comment

by:Nick67
ID: 39951018
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 4

Author Comment

by:bfuchs
ID: 39951351
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 79

Expert Comment

by:arnold
ID: 39951400
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
ID: 39951446
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 4

Author Comment

by:bfuchs
ID: 39951601
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
ID: 39951676
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 4

Author Comment

by:bfuchs
ID: 39951784
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 2000 total points
ID: 39951903
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 4

Author Comment

by:bfuchs
ID: 39951942
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
ID: 39951953
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
ID: 39952291
Does patients have a primary key? If not, add one and try again

hth

Mike
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39953677
"...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 4

Author Comment

by:bfuchs
ID: 39954207
@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.
ID: 39954222
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
ID: 39954260
@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 4

Author Comment

by:bfuchs
ID: 39954365
@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
ID: 39954389
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
ID: 39954441
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 4

Author Closing Comment

by:bfuchs
ID: 39954505
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
ID: 39954511
OK :)

Nick67
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

762 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