iif Statement Not Updating in Query

[tboles]

Hello All!
I've never experienced this issue and it is driving me nuts. I'm not super experienced in Access, but thought this was a pretty easy iif statement. I have a query based on a table that has a column called Calculated Card Expiration:

IIf([Card Type Issued]="PIV",[Issue Date]+1095,IIf([Card Type Issued]="Non-PIV",[Issue Date]+1095,IIf([Card Type Issued]="Flash",[Issue Date]+365,Null)))

It worked fine when I ran the query the first time, but now when a Card Type Issued and Issue Date is added or changed it doesn't work.
Thank you so much for anyone that can assist with what I'm doing wrong!

 

[jdraw]

How and what exactly does your UPDATE occur?
Can you show us more?

 

[tboles]

At the risk of me sounding silly, I'm not sure what more I can show...I'll try

There is field call card type issue that is a drop down of PIV, Non-PIV, and Flash and a field call Issue Date that is the date the card was issued. I am trying to calculate the expiration date based on the type of card. For Example, the PIV card expires 3 years from the issue date.

When I ran the query initially it provided the date I needed, but now as new entries are made, that field is not updating with the expiration date

Thank you very much for your response!

 

[vbaInet]

You can condense that expression further:

IIf([Card Type Issued] LIKE "*PIV", DateAdd("yyyy", 3, [Issue Date]), IIf([Card Type Issued]="Flash", DateAdd("yyyy", 3, [Issue Date]), Null))

... look into the DateAdd() function used.

Are you actually trying to save the value to a certain field?

 

[tboles]

Thank you for your response! The statement you provided does work as well, it provides the calculated expiration date for all of the entries that were previously there. Unfortunately, when I change the issue date the calculated expiration date doesn't change with it. And if I add a new card with an issue date it will not provide the calculated expiration date

I would like it to save it in the query so I can run reports on expiring cards. although the query is based of the table. I also have a form on the query (that does update the table as well)

I've never experienced this issue before...could it be an issue with my computer maybe?

Thank you again!

 

[vbaInet]

Unfortunately, when I change the issue date the calculated expiration date doesn't change with it. And if I add a new card with an issue date it will not provide the calculated expiration date

When you change the Issued Date you need to tab out of that control for it to re-calculate. Also, you don't save calculated values.