Question Using IIF Statements in Access

[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]=False,DateAdd("d",90,[Date Checked]),"")));

 

[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]=False,DateAdd("d",90,[Date Checked]),"")));

Reformatting your code, I get the following:

[FONT=Times New Roman][SIZE=3]SELECT [Standards Check record].[Date Checked], [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]  [Standards Check record].[VIP PASS 1], [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  [Standards Check record].[VIP Fail date][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]FROM [Standards Check record][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]WHERE ((([B][COLOR=green][Standards Check record].[VIP Fail date][/COLOR][/B])=[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]  IIf([COLOR=royalblue][B][VIP PASS 1][/B][/COLOR]=False, DateAdd("d",90,[B][COLOR=green][Date Checked][/COLOR][/B]), [COLOR=red][B]""[/B][/COLOR])));[/FONT][/SIZE]

It would appear that your Table contains two Date Fields and one Boolean Field that are used in this Formula. Your Formula is trying to insert a Blank String into a Date Field whenever the value in the Boolean Field is TRUE. You need to see if this is part of the problem.

-- Rookie

 

[grogmi1]

It just keeps coming up with 'Data Type Mismatch' when ever I try and run it

 

[MSAccessRookie]

It just keeps coming up with 'Data Type Mismatch' when ever I try and run it

That is most likely related to what I was trying to point out. Without seeing your actual Database, my best guess is that [VIP PASS 1] is a Boolean Variable witha Value of True. In your Formula, the Result of the IIf() is a String (""). When you compare the String to a Date, you get the 'Data Type Mismatch'. To resolve this, you will need to change the "" to a value that is compatible with a Date. Two possible solutions options come to mind, and each of them has possible problems.

1. Create a Dummy Date that will never be valid as content.
   1. This will require Additional testing to allow the program to ignore the Dummy Date.
   2. Problems could arise with this if the Dummy Date ever becomes valid.
2. Use NULL instead of a "".
   1. This will require Additional testing to allow the program to handle the Null Values.

 

[grogmi1]

This database is in it's early stages so I'm thinking of just scrapping it and starting again.

If I were to start again what would be the best way to have 3 columns 1. Date Checked 2. VIP Pass or fail 3. VIP Fail date.

What I need is for the 'VIP Fail date' field to be populated with a date which is 90 days more than the 'Date Checked' field if the 'VIP pass or Fail' is equal to "fail"

ANy help with this would be much appreciated

 

[Brianwarnock]

Personally I wouldn't have the third column. This would be shown in the report or form that I used to view the data, tables are for storing data not viewing it or entering it for that matter that is also done via a form.

Brian

 

[grogmi1]

The data will be eventually be displayed on a report and in a form. How can I create this in a form / report ?

Thanks