Solved Update query - Yes/No field not correctly selecting records (1 Viewer)

[xBirdman]

I need to update related records after a user updates the core record. Assume there are three related records. The user accesses a form and updates record X. When the user clicks "close" a set of VB scripts runs. A bunch of TempVars are created from information in record X. Two queries are run. The first says, "Take <fields> from X and update those fields in records Y and Z. All of these fields should match for related records. The second query then says, if records Y and Z are not locked (by fldLock) update a different set of fields, which may or may not match across related fields. The lock field is a Yes/No data type. The query uses a TempVar to filter to only records with the proper incidentNumber, and then it is supposed to use the fldLock to only update records where the Yes/No is NOT checked, i.e. is False.

When I actually run the query, the fldLock filter isn't working. No records are being returned and therefore the updates aren't occurring where they should. I assume I'm not querying the Yes/No field appropriately. There's a sample below, and I've also tried fldLock Is Null (because that seems to work in a select query).

UPDATE tblCaseInfo
SET    tblCaseInfo.Location = [TempVars]![tVincLoc],
       tblCaseInfo.ServiceElement = [TempVars]![tVservEl],
       tblCaseInfo.IncDate = [TempVars]![tVincDt],
       tblCaseInfo.DatIncReported = [TempVars]![tVrepDt]
WHERE  ( ( ( tblCaseInfo.IncidentNumber ) = [TempVars]![tVincNum] )
         AND ( ( tblCaseInfo.fldLock ) =False ) );

I get the warning that the query is running, then that it is updating 0 records. Without the fldLock field included Everything works fine, but I can't protect the records that need to maintain separate information. Can someone suggest how to correctly write the above query (or last line thereof) so that any records matching in the IncidentNumber, that are not locked will be updated with the existing tempVars?

 

[June7]

Criteria looks fine to me. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[arnelgp]

when the user press the "Close" button..
this does not guarantee that the "record pointer" of the form is in the correct record.
worst if you add new record or edit a record on the last of the recordset, pressing "enter" to save the record, will put the record pointer to "new record".
it is better to use the Form's BeforeUpdate event to run your SQL Update.

 

[xBirdman]

Criteria looks fine to me. If you want to provide db for analysis, follow instructions at bottom of my post.

Thanks. Unfortunately this is a secure, legacy database with classified personal information in it. It is pretty complex and I'm not sure how I'd remove confidential info and maintain enough functionality. I appreciate the offer but I'd be breaking all kinds of laws by attempting to share even the structure.

 

[xBirdman]

when the user press the "Close" button..
this does not guarantee that the "record pointer" of the form is in the correct record.
worst if you add new record or edit a record on the last of the recordset, pressing "enter" to save the record, will put the record pointer to "new record".
it is better to use the Form's BeforeUpdate event to run your SQL Update.

I have the form set up so the record pointer 'should' only be able to see the current record. You can't move off the form to a new record and there is no way to add a new record from this form. It only pulls the single desired record into the form space. The other thing confusing me is that remember there are two queries that run in succession. The first always runs correctly and the only real difference in the two is the presence of the Yes/No field filter. If I open the query while the TempVars are all available, and convert the query to a select query, it pulls the records just fine. I'm wondering if there's a difference in the way the Yes/No is handled in an update versus select query.

I will explore the BeforeUpdate event, but don't understand why everything else would work and this one piece wouldn't. Thanks for the response.

 

[Gasman]

I tend to start with a Select query that proves to me that I am updating the correct records.
Then I change it to an update query.

 

[Isaac]

I need to update related records after a user updates the core record. Assume there are three related records. The user accesses a form and updates record X. When the user clicks "close" a set of VB scripts runs. A bunch of TempVars are created from information in record X. Two queries are run. The first says, "Take <fields> from X and update those fields in records Y and Z. All of these fields should match for related records. The second query then says, if records Y and Z are not locked (by fldLock) update a different set of fields, which may or may not match across related fields. The lock field is a Yes/No data type. The query uses a TempVar to filter to only records with the proper incidentNumber, and then it is supposed to use the fldLock to only update records where the Yes/No is NOT checked, i.e. is False.

When I actually run the query, the fldLock filter isn't working. No records are being returned and therefore the updates aren't occurring where they should. I assume I'm not querying the Yes/No field appropriately. There's a sample below, and I've also tried fldLock Is Null (because that seems to work in a select query).

UPDATE tblCaseInfo
SET    tblCaseInfo.Location = [TempVars]![tVincLoc],
       tblCaseInfo.ServiceElement = [TempVars]![tVservEl],
       tblCaseInfo.IncDate = [TempVars]![tVincDt],
       tblCaseInfo.DatIncReported = [TempVars]![tVrepDt]
WHERE  ( ( ( tblCaseInfo.IncidentNumber ) = [TempVars]![tVincNum] )
         AND ( ( tblCaseInfo.fldLock ) =False ) );

I get the warning that the query is running, then that it is updating 0 records. Without the fldLock field included Everything works fine, but I can't protect the records that need to maintain separate information. Can someone suggest how to correctly write the above query (or last line thereof) so that any records matching in the IncidentNumber, that are not locked will be updated with the existing tempVars?

I assume you have already done the first step, which is to Debug and test what exactly is the value of each of those TempVars?
And I'm talking about breaking the code at the precise time when that Update query is about to execute - immediately prior to that line of code.

We can guess and speculate but by debugging abilities on your end, you hold the answer, essentially.
Set a breakpoint on the line of code that runs the Update query. When the code breaks, start using the Immediate window to determine what the precise value of your TempVars, and/or any dynamic SQL string variable, if that's how you're doing the update.

With the code still in break mode (and having broke it immediately prior to the Update query running), after figuring out what each TempVars value is, then construct the identical sql statement as a select statement and create a new query and try to run it with those values. With the code still break'ed.

 

[xBirdman]

I assume you have already done the first step, which is to Debug and test what exactly is the value of each of those TempVars?
And I'm talking about breaking the code at the precise time when that Update query is about to execute - immediately prior to that line of code.

We can guess and speculate but by debugging abilities on your end, you hold the answer, essentially.
Set a breakpoint on the line of code that runs the Update query. When the code breaks, start using the Immediate window to determine what the precise value of your TempVars, and/or any dynamic SQL string variable, if that's how you're doing the update.

With the code still in break mode (and having broke it immediately prior to the Update query running), after figuring out what each TempVars value is, then construct the identical sql statement as a select statement and create a new query and try to run it with those values. With the code still break'ed.

Thanks, and yes, I had headed down this path. I believe what I have found now by running a dozen different scenarios, is that for some reason, although the yes/no field is showing 0, they are actually null, and thus not being filtered properly. I cannot for the life of me figure out why, but I should be able to set the default value for that field to 0 or False.

So now my issue is that I need to search for and update all those nulls to False. In my test database I ran a select where fldLock Is Null, and get 49 records. I tried several iterations of Update queries to set those values = 0 or False and it simply tells me that 0 records will be updated. Do you know how I can execute such an update for that field successfully? In my live database there are potentially hundreds of records. I know that if they haven't been specifically protected, they should be set to false, so I'm not worried about running the update, just can't figure out a query structure that works.

 

[Isaac]

Well, this recurring theme whereby you state that you have a Select version of a query, and then an Update version--the Update version exactly the same as the Select's where clause--and they are appearing to have different 'selecting' results, that's something I'm not sure I've run into. (With one caveat--I know that in some RDBMS's, even if the Update query does find matching records [from the Where clause], yet, if the "update-to" values are no different than what the records' values already ARE, then it might update zero rows. Or maybe there is something else about the values you are updating to that violates a setting or expression which is causing no rows to be updated. Can you post the test database where the Update results appear different than the Select results?

Last question, is this a local Access table with the yes/no column, or is it a linked table to a remote database?

 

[Gasman]

You could try the NZ() function on your fldLock field?

 

[xBirdman]

Well, this recurring theme whereby you state that you have a Select version of a query, and then an Update version--the Update version exactly the same as the Select's where clause--and they are appearing to have different 'selecting' results, that's something I'm not sure I've run into. (With one caveat--I know that in some RDBMS's, even if the Update query does find matching records [from the Where clause], yet, if the "update-to" values are no different than what the records' values already ARE, then it might update zero rows. Or maybe there is something else about the values you are updating to that violates a setting or expression which is causing no rows to be updated. Can you post the test database where the Update results appear different than the Select results?

Last question, is this a local Access table with the yes/no column, or is it a linked table to a remote database?

I am the proverbial problem child in these scenarios... LOL. The 'test' database is just an older version of the live one moved onto a test server by our data security office so has all the same confidentiality concerns. And it is a linked database - back end is on an SQL server.

It does seem strange to me that the select version pulls the 49 'null' records (as distinct from the thousands of false/0 records) but when converting to an update query it isn't able to either recognize or update the field. Maybe it can't update the yes/no value in a field being used to generate the set? I'll have to play around with a different way to pull the data and see if I can get a workaround. Thanks for the back and forth. As you know, sometimes just walking through this exercise is helpful for finally shaking something loose.

 

[Isaac]

It does seem strange to me that the select version pulls the 49 'null' records (as distinct from the thousands of false/0 records) but when converting to an update query it isn't able to either recognize or update the field.

Are you able to post the SQL for the update query that updates 0 rows, but whose Select version returns 49?

 

[Isaac]

I just noticed you said this is a linked table in SQL Server. I think herein lies the problem.
The way Access treats it's yes/no columns is different from the way SQL Server treats its bit columns. You cannot take a linked SQL Bit column and simply start treating it under the rules as if it were an Access yes/no column.
Given the chance to make the design decision, I don't use either one, so I haven't had much experience in this area, but I know from reading others' experiences, that when you link to a SQL Server bit column from Access, that generates its own unique scenario that you have to study up on to understand.

The way Access "displays" them (probably more relevant to the Select) vs. the Update value, could be confusing things a bit for you. SQL Server might have them as a true NULL...whereas, per that article, Access displays them as FALSE. Who knows if your Select query is basing itself on the way Access "displays" things, whereas your Update query needs to specify the SQL-acceptable value to update to (probably 0 for False or 1 for True).

Honestly, if I were in your shoes, I think that I'd create a pass-through query, and write the update in T-SQL. That way, at least you know that you only have to approach your logic from a single platform's rules. Keep it simple. If you want to select Null records, your T-SQL can actually specify NULL. Your update statement will update them to either 1 or 0.

 

[xBirdman]

I just noticed you said this is a linked table in SQL Server. I think herein lies the problem.
The way Access treats it's yes/no columns is different from the way SQL Server treats its bit columns. You cannot take a linked SQL Bit column and simply start treating it under the rules as if it were an Access yes/no column.
Given the chance to make the design decision, I don't use either one, so I haven't had much experience in this area, but I know from reading others' experiences, that when you link to a SQL Server bit column from Access, that generates its own unique scenario that you have to study up on to understand.

The way Access "displays" them (probably more relevant to the Select) vs. the Update value, could be confusing things a bit for you. SQL Server might have them as a true NULL...whereas, per that article, Access displays them as FALSE. Who knows if your Select query is basing itself on the way Access "displays" things, whereas your Update query needs to specify the SQL-acceptable value to update to (probably 0 for False or 1 for True).

Honestly, if I were in your shoes, I think that I'd create a pass-through query, and write the update in T-SQL. That way, at least you know that you only have to approach your logic from a single platform's rules. Keep it simple. If you want to select Null records, your T-SQL can actually specify NULL. Your update statement will update them to either 1 or 0.

Okay, this makes a great deal of sense, and as you explain it I almost hear echoes of something I've read along those lines in the past. I will go directly to the server side and run the select in T-SQL and see if I get the same records, and if so I should then be able to do the update there directly. I've done less work on SQL servers and each new issue is just a learning opportunity. Thanks.

 

[Isaac]

Good luck!

 

[isladogs]

Unlike Access, a bit field in SQL Server can have 3 states: true/false & null
If you try to update a linked SQL table with bit fields containing null values, you will normally get a write conflict error as Access cannot handle the null values.

The solution to that issues is to set a default value (true or false as preferred) to ALL SQL bit fields and set any existing null values to that default.
Update queries based on those tables should then work without problems.

 

[xBirdman]

Thanks to everyone for input and suggestions. I was able to go directly to the server and use T-SQL to update all current nulls to 0. Next week I should be able to get the gate-keepers to run an update script which will set the default value to 0 for all future entries. Once the lock value is set = 0, the update query seems to work exactly as scripted. Cheers!

 

[isladogs]

Excellent. Good luck with your project

 

[Isaac]

Awesome, glad you got it worked out.