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

 

[creyn]

The field, [PQRS Data].Problems needs to be Updated to blank("") if the field, [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.

 

[Joe4]

Then it should be just as simple as:

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");

 

[creyn]

Thanks. Will this work also?

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]

Why do you want to add all that other stuff to the query?
What is the purpose of doing so?
Are you trying to add more criteria by doing that?

 

[creyn]

I need the other stuff because I have other fields that I need to display in the query.

 

[Joe4]

I need the other stuff because I have other fields that I need to display in the query.

OK, you are confusing a bunch of different things here. It is important to get them straight.

Update Queries are used to update values in a table. They are NOT used for display purposes (for showing people data or for providing Forms, Reports, or Exports with data). This is true of all ACTION Queries (Update, Append, Delete). They are used for ACTIONS only, not for displaying data.

Select Queries are used to display data or supply Forms, Reports, or Exports with data. You CANNOT permanently change the value of a table field in a Select query through a formula (though if you have an updateable query, you can allow users to manually update field values in them).

Select Queries can show Calculated Fields. These are simply calculations and are NOT mapped to or stored in any underlying table field. They are dynamic, on-the-fly calculations.
A general rule of thumb with Access is that anything which can easily be calculated on-the-fly should NOT be stored in an underlying table field. Never store that which can easily be calculated! If they can easily be calculated, there is no reason to store them. Select Queries can be used as the data source of other Queries, Forms, Reports, and Exports, so there is no reason to store everything at the Table level.

Does that clarify things for you?

 

[creyn]

Yes, that does clarify things for me. Thank you for your help. I really appreciate it

 

[Joe4]

Great!
You are welcome.