Place a value in one field based upon the value of another field

[creyn]

How can I write in the query if one field is "Yes" have another field = "" or if the field is "" then have another field = "No"?

I tried this:

=IIf([PQRS Data].Value = "Yes", [PQRS Data].Problems = "", "No")


This does not work. Can anyone assist me with clearing this up?

Thank you

 

[Joe4]

You cannot set the value of fields via a query unless you do an Update query.
Basically, calculations which you can run/call at any time should typically NOT be stored in a table field. They should just be calculated fields in a query.
General rule of thumb: Do not store anything in a field which can easily be calculated.

If you are still unclear as to what you should be doing, please explain to us in detail what you are trying to do, and we will try to come up with the suggested way of doing it.

 

[creyn]

This is what I am trying to do.

=IIf([Field1] = "Yes", [Filed2] = "", "No")

Can I do this without doing an update?

 

[Joe4]

If Field2 is a field in your table that you want to permanently update, you MUST use an UPDATE Query.
You cannot update field values using a SELECT query. SELECT queries just return data.

 

[creyn]

I will do a nested Update in my SELECT query and that should work

 

[Joe4]

I think you mean the other way around. You cannot nest an UPDATE Query inside a SELECT Query, but you can nest a SELECT Query inside an UPDATE query.
Though I am not sure why you need to do that. It should be pretty straightforward, since it is updating one field in a single table based on criteria in another field within that same table.

 

[creyn]

Below is my query. I cannot update the PQRS Data.Problems until the PQRS Data.Value has it value. This is part of the confusion I am having.

SELECT [PQRS Data].[Chart #] AS [Patient ID],
[PQRS Data].[Admit Dt] AS DOS,
[PQRS Data].[Visit ID],
=IIf([PQRS Data].Value = "Reporting Met And Performance Met" Or [PQRS Data].Value = "Advanced Care Planning - first 30 minutes" Or [PQRS Data].Value = "Advanced Care Planning - each additional 30 minutes", "Yes","") AS Met,
[PQRS Data].Problems,
"N/A" AS Excluded
FROM [PQRS Data]
GROUP BY [PQRS Data].[Chart #], [PQRS Data].[Admit Dt], [PQRS Data].[Visit ID], [PQRS Data].Problems, "N/A", [PQRS Data].Value;

 

[Joe4]

What you have posted is a SELECT Query, not an UPDATE Query.

Watch this tutorial here to see how to create UPDATE Queries: https://www.youtube.com/watch?v=DPMlpjfaS9Y

 

[creyn]

This is how I am writing the query now

UPDATE [PQRS Data] SET [PQRS Data].Problems = ""
WHERE [PQRS Data].Value = "Reporting Met And Performance Met" Or [PQRS Data].Value = "Advanced Care Planning - first 30 minutes" Or [PQRS Data].Value = "Advanced Care Planning - each additional 30 minutes"
FROM
(
SELECT [PQRS Data].[Chart #] AS [Patient ID],
[PQRS Data].[Admit Dt] AS DOS,
[PQRS Data].[Visit ID],
=IIf([PQRS Data].Value = "Reporting Met And Performance Met" Or [PQRS Data].Value = "Advanced Care Planning - first 30 minutes" Or [PQRS Data].Value = "Advanced Care Planning - each additional 30 minutes", "Yes","") AS Met,
[PQRS Data].Problems,
"N/A" AS Excluded
FROM [PQRS Data]
GROUP BY [PQRS Data].[Chart #], [PQRS Data].[Admit Dt], [PQRS Data].[Visit ID], [PQRS Data].Problems, "N/A", [PQRS Data].Value
) t
WHERE [PQRS Data].Problems = t.Problems;

 

[Joe4]

And is it working?

If not, please explain (in plan English), exactly what records you are trying to update to what, and what the conditions are.
I am always very leery of trying to decipher what someone is trying to do by reverse engineering their code, especially when I don't know if the code is faulty or not, and really does what they want it to.