Nested If statement syntax error

[GOVMATE]

Hello,

I'm new to working with Access Iif statements. I'm attempting to change data values in an existing table for column named "Status." Some of the data in this column has the correct value (Active) that needed represented, while the rest is populated with either a "J" which is Active or "T" which is inactive. I needed an update query that will ignore any values that are already "Active," and convert any values that are "J" to Active and values that are "T" to "Non Active." Any help would be greatly appreciated. Below is the function I have written that's full of syntax errors:

IIf(([STATUS] T,"T") = "T", then "Non Active"), IIf([STATUS] J,"J") = "J" then "Active"), else Active = "Active"))

 

[boblarson]

If building it in a query:

MyNewFieldNameHere:IIf([STATUS]="T", "Non Active", IIf([STATUS]="J", "Active", "Active"))

If using it as a controlsource of a text box:

=IIf([STATUS]="T", "Non Active", IIf([STATUS]="J", "Active", "Active"))

 

[CraigDolphin]

In your update query, use a criterion of Not "Active" in the status field to eliminate all records that have Active. Then, in another column, use the expression:

Switch([Status]="J","Active",[Stutus]="T","Non Active",Nz([Status],"Missing") not in ("J","T"),Null)

Have the results in this column update your Status field. Don't forget, always backup your original table before running the update so you have start over if something goes wrong.

 

[GOVMATE]

If building it in a query:

MyNewFieldNameHere:IIf([STATUS]="T", "Non Active", IIf([STATUS]="J", "Active", "Active"))

If using it as a controlsource of a text box:

=IIf([STATUS]="T", "Non Active", IIf([STATUS]="J", "Active", "Active"))

Before I use a update query that will affect my table, I am first using a select query just to view the results. I am using the data from the field status and I want to display the results in my expression column named "xStatus" In the QBE grid I'm displaying columns "Status" and "xStatus". In the criteria for "Status" I entered Not "Active" In the criteria section for "xStatus" I used the syntax you suggested: =IIf([STATUS]="T","Non Active",IIf([STATUS]="J","Active","Active")) with this I do not get any records return for the select query display. Any suggestions? Thanks!!!!

 

[GOVMATE]

In your update query, use a criterion of Not "Active" in the status field to eliminate all records that have Active. Then, in another column, use the expression:

Switch([Status]="J","Active",[Stutus]="T","Non Active",Nz([Status],"Missing") not in ("J","T"),Null)

Have the results in this column update your Status field. Don't forget, always backup your original table before running the update so you have start over if something goes wrong.

Before I use a update query that will affect my table, I am first using a select query just to view the results. I am using the data from the field status and I want to display the results in my expression column named "xStatus" In the QBE grid I'm displaying columns "Status" and "xStatus". In the criteria for "Status" I entered Not "Active" In the criteria section for "xStatus" I used the syntax you suggested: Switch([Status]="J","Active",[Stutus]="T","Non Active",Nz([Status],"Missing") not in ("J","T"),Null) with this I do not get any records return for the select query display. Any suggestions? Thanks!!!!

 

[CraigDolphin]

Switch([Status]="J","Active",[Status]="T","Non Active",Nz([Status],"Missing") not in ("J","T"),Null)

Sorry...there was a typo in there: replace stutus with status.

As for problem solving, first try querying with just the Status field and see if you get records. Then put in your criteria and check for records. If so, add the xStatus field and check again. If you get records all the way through except when you add the xStatus field then the problem is with the XStatus expression. If you lose your records earlier, then the problem is elsewhere.

Perhaps posting your SQL will help also.

 

[GOVMATE]

Switch([Status]="J","Active",[Status]="T","Non Active",Nz([Status],"Missing") not in ("J","T"),Null)

Sorry...there was a typo in there: replace stutus with status.

As for problem solving, first try querying with just the Status field and see if you get records. Then put in your criteria and check for records. If so, add the xStatus field and check again. If you get records all the way through except when you add the xStatus field then the problem is with the XStatus expression. If you lose your records earlier, then the problem is elsewhere.

Perhaps posting your SQL will help also.

Hello Craig,

I apologize for reposting your typo, I actually corrected it before I attempted to use it. I tried your suggestion to see if i could display records in the status field that were Not "Active" and it worked. When I added the additional field to the select query "xstatus" with the criteria listed as Switch([Status]="J","Active",[Status]="T","Non Active",Nz([Status],"Missing") Not In ("J","T"),Null) No records are displayed? The following is my SQL. Any suggestions????? Thanks!!!!!!!


SELECT Working_table_Sourcedata_Current.STATUS, Working_table_Sourcedata_Current.xSTATUS
FROM Working_table_Sourcedata_Current
WHERE ((Not (Working_table_Sourcedata_Current.STATUS)="Active") AND ((Working_table_Sourcedata_Current.xSTATUS)=Switch([Status]="J","Active",[Status]="T","Non Active",Nz([Status],"Missing") Not In ("J","T"),Null)));

 

[CraigDolphin]

Ok...I think I see the problem. You do not use the expression Switch.... in the criteria row of xStatus. In the field row of the design view of the xStatus column replace xStatus with
xStatus: Switch([Status]="J","Active",[Status]="T","Non Active",Nz([Status],"Missing") not in ("J","T"),Null)

and do not put anything in the criteria row of that column. See if that fixes the problem

 

[GOVMATE]

Ok...I think I see the problem. You do not use the expression Switch.... in the criteria row of xStatus. In the field row of the design view of the xStatus column replace xStatus with
xStatus: Switch([Status]="J","Active",[Status]="T","Non Active",Nz([Status],"Missing") not in ("J","T"),Null)

and do not put anything in the criteria row of that column. See if that fixes the problem

Ok Craig...you're awesome...it worked. One more question. In order to permanetly update my table "Status" field, should I keep the <> "Active" remark for in criteria field for "status" and use the following syntax for the update Switch([Status]="J","Active",[Status]="T","Non Active",Nz([Status],"Missing") not in ("J","T"),Null) ???? Thanks a million!!!!

 

[CraigDolphin]

Yes, you do want to keep the criteria in the Status field of the update query otherwise it will replace currently 'Active' records with a null.

Or you could add to the switch statement so that when [Status] = "Active" the value would be "Active" then remove the Status field entirely. But either way should work. Just remember to backup your table before doing the update so you can retrieve it if all turns to custard.

 

[GOVMATE]

Yes, you do want to keep the criteria in the Status field of the update query otherwise it will replace currently 'Active' records with a null.

Or you could add to the switch statement so that when [Status] = "Active" the value would be "Active" then remove the Status field entirely. But either way should work. Just remember to backup your table before doing the update so you can retrieve it if all turns to custard.

Thanks Craig,

The query worked perfectly!!!!!!

 

[CraigDolphin]

Glad to help