Question Using IIF Statements in Access

[grogmi1]

Hi,

I am new to access and I'm having a little trouble using IIF statements.

I have a field in my Access table named 'Date Checked' a one named 'VIP Pass' and a one named 'VIP Fail date'.

What I am trying to do . . . If the answer in the 'VIP Pass' field is NO I need the 'VIP Fail Date' field to be 90 days from the 'Date Checked' field.

Something like if VIP Pass=No then VIP Fail date=Date Checked +90.

I am a total novice at this so any help would be much appreciated.

Regards

 

[pr2-eugin]

Hello grogmi1.. In Databases, it is not generally a good practice to store calculated values in tables, Allen Browne talks about it in this post HERE. Check it out.. Of course it can be displayed on a Form with an unbound control, or on a Query or Report.. Think wisely before proceeding this..

 

[grogmi1]

Thanks for the response Paul.
I have used a Query and attempted to create this myself using the 'build' function but I'm not getting very far. The field I mentioned are all from the same table I'm just struggling to create the correct IIF statement in my query.

 

[pr2-eugin]

As long as it is in a Query that's fine.. What do you want the VIP Failed date to be when it is Checked? I assume nothing?? If that is the case, then the Field VIP Failed Date should have the IIF statement.. Something like..

[VIP Failed Date] : IIF([VIP Pass]=0,DateAdd("d",90,[Date Checked]),"")

 

[grogmi1]

Thanks again Paul, sorry about the delay in replying.

I've tried the IIF statement you gave me but when I run the query the VIP Fail date is blank

Any ideas ???

 

[pr2-eugin]

If the VIP pass is true or Checked then the Fail date will be blank.. what is the data type of the field VIP pass in the table?

 

[grogmi1]

Hi Paul, its stays blank when the Vip pass is false aswell. Vip pass is linked to another table which gives it it's 'yes' or 'no' values

 

[pr2-eugin]

Could you please post your SQL query??

 

[grogmi1]

How do I do that ??

 

[pr2-eugin]

Go into the SQL view of the Query and copy and paste it here..

 

[grogmi1]

SELECT [Standards Check record].[Date Checked], [Standards Check record].[VIP PASS 1], [Standards Check record].[VIP Fail date]
FROM [Standards Check record]
WHERE ((([Standards Check record].[VIP Fail date])=IIf([VIP PASS 1]=No,DateAdd("d",90,[Date Checked]),"")));

 

[pr2-eugin]

Okay can you explain what you actually wan to do?
Do you want to select all VIP pass that has a NO value, and give them the Date checked + 90 as the fail date??

 

[grogmi1]

Thanks for getting back to me again Paul,

What I need to happen is that when we check one of the vehicles in our fleet we enter the date it was checked into the 'Date Checked field'. If we then decide it is not fit for VIP work we will select 'No' in the 'VIP Pass' field which will then automatically populate the VIP Fail date field with a date which is 90 days from the date it was checked.

Clear as mud !!!

Thanks again

 

[MSAccessRookie]

SELECT [Standards Check record].[Date Checked], [Standards Check record].[VIP PASS 1], [Standards Check record].[VIP Fail date]
FROM [Standards Check record]
WHERE ((([Standards Check record].[VIP Fail date])=IIf([VIP PASS 1]=No,DateAdd("d",90,[Date Checked]),"")));

Unless you have a Defined Constant or Variable named No, then there will be no value to test.

Is [VIP PASS 1] a String, Number, Boolean, Date, or something else? If it is Boolean, then use the Constants True and False instead.

-- Rookie.

 

[grogmi1]

Hi, I've tried it with 0 aswell but still no joy.

 

[jonathanchye]

Try :

SELECT [Standards Check record].[Date Checked], [Standards Check record].[VIP PASS 1], [Standards Check record].[VIP Fail date]
FROM [Standards Check record]
WHERE ((([Standards Check record].[VIP Fail date])=IIf([VIP PASS 1]=[B]False[/B],DateAdd("d",90,[Date Checked]),"")));

 

[grogmi1]

Thanks for the reply. It still doesn't populate the VIP fail date field

 

[jonathanchye]

Thanks for the reply. It still doesn't populate the VIP fail date field

Try this :

1) Create new form and set your table as teh recordsource
2) Create 3 textboxes with date checked, vip pass and vip fail date as their control source. You might want to create a combobox with Yes/No for "VIP Pass"...
3) Select the textbox for VIP Pass and create a code for the "After Update" event of this combo/textbox

The code would be something like :

If IsNull [Date Checked (or whatever the textbox name is)] Then
     If [VIP PASS] = "Yes" Then [VIP FAIL DATE] = DateAdd("d",90,[Date Checked]) 
Else
[VIP FAIL DATE] = Null
End If

edit : this is just a very rough example but should get you started

 

[grogmi1]

Hi,

Still no joy

 

[jonathanchye]

Hi,

Still no joy

Upload what you have and I'll see if I can fix it