Update yes/no field with query

[Leesha]

Hi,

I have a table with multiple yes/no contrls that will need to be updated via queries. I've tried the following sql statement but its not updating the field. When I run the query as a select query to test the iff statment it runs fine. I don't get any errors when running the update query but nothing fills in in the table. I've tried it using true, yes and -1 but nothing works. Is this possible to do and if so what am I doing wrong?

Thanks,
Leesha

UPDATE tblCRRDataTable INNER JOIN [tblCRRDX-Chosen25] ON (tblCRRDataTable.off_code = [tblCRRDX-Chosen25].off_code) AND (tblCRRDataTable.Admit_no = [tblCRRDX-Chosen25].admit_no) AND (tblCRRDataTable.Client_no = [tblCRRDX-Chosen25].client_no) SET tblCRRDataTable.DiabetesDX = IIf([tblcrrdx-chosen25].[catagorty]="Diabetes",True);

[HansV]

Try

UPDATE tblCRRDataTable INNER JOIN [tblCRRDX-Chosen25] ON (tblCRRDataTable.off_code = [tblCRRDX-Chosen25].off_code) AND (tblCRRDataTable.Admit_no = [tblCRRDX-Chosen25].admit_no) AND (tblCRRDataTable.Client_no = [tblCRRDX-Chosen25].client_no) SET tblCRRDataTable.DiabetesDX = ([tblcrrdx-chosen25].[catagorty]="Diabetes");

Are you sure the field name catagorty is spelled that way? Shouldn't it be category ?

[Leesha]

Hi,
Nope that didn't do it either. [DiabetesDX] should be set to true only if the value in [catagroty] is "Diabetes". And yes, its spelled wrong but at this point there are other queries that have the same wrong spelling and I don't want to blow stuff up. It's someone else's database that I'm working on and I don't know what else will be affected at this point.

Leesha

[Leesha]

OMG! I did it! This is what I tried:

UPDATE tblCRRDataTable INNER JOIN [tblCRRDX-Chosen25] ON (tblCRRDataTable.Client_no = [tblCRRDX-Chosen25].client_no) AND (tblCRRDataTable.Admit_no = [tblCRRDX-Chosen25].admit_no) AND (tblCRRDataTable.off_code = [tblCRRDX-Chosen25].off_code) SET tblCRRDataTable.DiabetesDX = True
WHERE ((([tblCRRDX-Chosen25].Catagorty)="diabetes"));

Thanks!
Leesha

[HansV]

If you only want to set DiabetesDX to True if the "catagorty" is diabetes, but not set it to false otherwise, that should do it.

[Leesha]

Yes that is what I need. However your question makes me wonder................isn't it assumed that if there is no check in the box to indicate true then the answer is false? When I run a query for false, only the boxes without checks come up? Am I missing something?

Also, is there a way to give the option of True/False/NA without having to set up drop down boxes with choices to pick from? I have a lot of yes/no controls and it would take forever to do dropdown boxes. The ability to have NA would be awesome.

Leesha

[HansV]

If DiabetesDX was empty before you ran the query, all values would be False by default, and your update query would set the value to True where needed.
But if the 'Catagorty" field is edited and the value "diabetes" is removed for one or more patients, re-running the update query would not set DiabetesDX to False, because the query can only set the value to True, not to False.

A Yes/No field can only be True or False, there is no N/A. You could use a number field if you need a third state.
You can then use a check box bound to the number field, with the Triple State property set to Yes. The check box will toggle the value of the field between Null (empty, undetermined), 0 (False) and -1 (True).

[Leesha]

Thanks for information! I'll give it some thought as to the best approach,

Leesha

[JohnH]

Hi,
And yes, its spelled wrong but at this point there are other queries that have the same wrong spelling and I don't want to blow stuff up.

If you download Rick Fisher's Find and Replace fixing issues like this is a very simple job.