IIF statement on a criteria (1 Viewer)

Mezta1988

Registered User.
Local time
Today, 09:14
Joined
Jan 22, 2019
Messages
41
Hi,

I have a query with multiple fields, Three of those fields are named "FirstName","Condition" and "Status". The condition field can be one the following, either "Working","Under Maintenance" or "Damage" and for the status, it's either "Availbale, Unavailable or Assigned".

If the condition is set to under maintenance then the status should switch to Unavailable, the same goes if the condition is set to damaged. however if the firstname is populated then the condition must switch to "Working" and and the status should also be set to "Assigned"

The code below is only my guess and I know it will not work.

Code:
Status: IIf(IsNull([FirstName],"Available","Unavailable","Assigned" and [Condition]=Damaged),"Unavailable","Available")
 

isladogs

MVP / VIP
Local time
Today, 06:14
Joined
Jan 14, 2017
Messages
18,186
Use an update query or rather two of them to set the values for all records.
 

Mezta1988

Registered User.
Local time
Today, 09:14
Joined
Jan 22, 2019
Messages
41
Code:
Use an update query or rather two of them to set the values for all records.
can you please elaborate it properly, as you know I'm not really good in access so I'm having a hard time to understand you reply.

Thanks,
 

isladogs

MVP / VIP
Local time
Today, 06:14
Joined
Jan 14, 2017
Messages
18,186
Sorry about the delay in replying.

Attached is a simple database with 1 (empty) table and 2 update queries
It also has a very simple procedure to run each query in turn ...but I could have just used SQL instead

Modify the queries for your own table name.
Check the queries do what you actually want on dummy data or a copy of your db first.
 

Attachments

  • Mezta1988.zip
    21.6 KB · Views: 73

George21

Registered User.
Local time
Today, 08:14
Joined
Jan 10, 2011
Messages
27
Will this be done through a form? If so, for the 2nd part of your query assign default values.
For the 1st a short VBA Code.

Check the attached db.
 

Attachments

  • IIF.zip
    45.5 KB · Views: 61

Users who are viewing this thread

Top Bottom