By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,433 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

IIF in query using "<" sign

reginaldmerritt
100+
P: 201
I'm trying to select certain records in a query using an embedded If statement. I want to us a "<" in the statement but can't see any information on how to do this.

The IIF statement is

Expand|Select|Wrap|Line Numbers
  1. IIf(Format([Forms].[FRMExamVenueHireDetails].[PickupDate],"mm/dd/yyyy")=[ReturnDate],([TBExamVenueHire].[ReturnTimeProportion])<[Forms].[FRMExamVenueHireDetails].[PickUpTimeProportion])
  2.  
So I want the criteria "[TBExamVenueHire].[ReturnTimeProportion])<[Forms].[FRMExamVenueHireDetails].[PickUpTimeProportion]" only if "[PickupDate] =[ReturnDate]", otherwise I don't want any criteria to be used.

I hope that makes sense, any help appreciated.

This is the full SQL
Expand|Select|Wrap|Line Numbers
  1. SELECT TBExamVenueHire.ExamVenueHireID, TBExamVenueHire.ExamVenueID, TBExamVenueHire.PickUpDate, TBExamVenueHire.ReturnDate, TBExamVenueHire.PickUpTimeProportion, TBExamVenueHire.ActualReturnDateTime, TBExamVenueHire.ExamVenueHireStatusID
  2. FROM TBExamVenueHire
  3. WHERE (((TBExamVenueHire.ExamVenueID)=[Forms].[FRMExamVenueHireDetails].[ExamVenueID]) AND ((TBExamVenueHire.ReturnDate)<=Format([Forms].[FRMExamVenueHireDetails].[PickUpDate],"mm/dd/yyyy")) AND ((TBExamVenueHire.ActualReturnDateTime) Is Null) AND ((TBExamVenueHire.ExamVenueHireStatusID)=1) AND ((TBExamVenueHire.ReturnTimeProportion)=IIf(Format([Forms].[FRMExamVenueHireDetails].[PickupDate],"mm/dd/yyyy")=[ReturnDate],([TBExamVenueHire].[ReturnTimeProportion])<[Forms].[FRMExamVenueHireDetails].[PickUpTimeProportion])));
  4.  
Aug 17 '12 #1

✓ answered by twinnyfo

Reginald,

When using an IIF statement, you must have the conditional statement in the first argument. If that is true, the IIF statement will return the second argument. If the first conditional statement if false, the IIF Statement will return the third argument.

In your example, you are essentially asking your statement to evaluate the second argument, which will return either a true or false result.

The IIF statement does not "do" anything, it just returns a value.

There are ways to have conditionals in your queries. One way I have done it is to evaluate your criteria first, where the IIF returns a true or false, then select only those records which are true (or false).

Share this Question
Share on Google+
10 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,123
Reginald,

When using an IIF statement, you must have the conditional statement in the first argument. If that is true, the IIF statement will return the second argument. If the first conditional statement if false, the IIF Statement will return the third argument.

In your example, you are essentially asking your statement to evaluate the second argument, which will return either a true or false result.

The IIF statement does not "do" anything, it just returns a value.

There are ways to have conditionals in your queries. One way I have done it is to evaluate your criteria first, where the IIF returns a true or false, then select only those records which are true (or false).
Aug 17 '12 #2

twinnyfo
Expert Mod 2.5K+
P: 3,123
Here is an exam ple I threw together (I hope I got all the parentheses correct...)

In your query, this should be one of your fields:


Expand|Select|Wrap|Line Numbers
  1. CriteriaTest: IIf([Forms].[FRMExamVenueHireDetails].[PickupDate]=[ReturnDate], IIf([TBExamVenueHire].[ReturnTimeProportion])<[Forms].[FRMExamVenueHireDetails].[PickUpTimeProportion], True, False), True)
  2.  
The Criteria for this field should be set to True. You don't need to include this value in your query, but just use it as an evaluator.

As you can see, the Field itself is just a placeholder. It evaluates PickupDate with ReturnDate (these should not require formatting, but they may if one has the HH.MM.SS included and the other not.....) and if they are equal, then it evaluates whether ReturnTimeProportion < PickUpTimeProportion. If that is tru, then the result is true (returning that record). If not, then it returns false. If PickupDate and ReturnDate are not equal, then the result is true (returning the record).

Hope this helps.
Aug 17 '12 #3

Rabbit
Expert Mod 10K+
P: 12,324
There's no need to use IIf(). You can just OR your conditions.
Aug 17 '12 #4

reginaldmerritt
100+
P: 201
Thanks for all your help.

Rabbit OR won't work with what I'm trying to do, but thanks

twinnyfo, thank you for your example, that's a smart work around, but i think there is another way.

The query looks for all [pickupdate] >= [returndate] but if [pickupdate] = [returdate] then i want to use the criteria that looks at ReturnTimeProportion.

What i'll do is create two queries, one with [pickupdate] > [returndate] and another where [pickupdate] = [returndate] but with the criteria that looks at ReturnTimeProportion. Then create a union query with those two queries. I think that will work.
Aug 17 '12 #5

Rabbit
Expert Mod 10K+
P: 12,324
Actually, OR will work with what you're trying to do.

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     TBExamVenueHire.ExamVenueHireID, 
  3.     TBExamVenueHire.ExamVenueID, 
  4.     TBExamVenueHire.PickUpDate, 
  5.     TBExamVenueHire.ReturnDate, 
  6.     TBExamVenueHire.PickUpTimeProportion, 
  7.     TBExamVenueHire.ActualReturnDateTime, 
  8.     TBExamVenueHire.ExamVenueHireStatusID 
  9. FROM 
  10.     TBExamVenueHire 
  11. WHERE 
  12.     TBExamVenueHire.ExamVenueID=[Forms].[FRMExamVenueHireDetails].[ExamVenueID] AND 
  13.     TBExamVenueHire.ReturnDate<=Format([Forms].[FRMExamVenueHireDetails].[PickUpDate],"mm/dd/yyyy") AND 
  14.     TBExamVenueHire.ActualReturnDateTime Is Null AND 
  15.     TBExamVenueHire.ExamVenueHireStatusID=1 AND
  16.     (
  17.         [TBExamVenueHire].[ReturnTimeProportion])<[Forms].[FRMExamVenueHireDetails].[PickUpTimeProportion] AND
  18.         [Forms].[FRMExamVenueHireDetails].[PickupDate]=[ReturnDate] OR
  19.         [Forms].[FRMExamVenueHireDetails].[PickupDate]>[ReturnDate]
  20.     )
Aug 17 '12 #6

NeoPa
Expert Mod 15k+
P: 31,261
OR is also a more efficient way of processing through the table, as it is native SQL, whereas IIf() is a function that would need to be called for every relevant record. The OR approach is definitely to be recommended Reginald.
Aug 17 '12 #7

Rabbit
Expert Mod 10K+
P: 12,324
NeoPa is correct. OR is more efficient than IIf(). However, you stumbled on a (possibly) more efficient approach with UNION. There are times when a UNION is quicker than OR and times when OR is quicker than UNION. The only way to know which one is to run tests using both.
Aug 18 '12 #8

reginaldmerritt
100+
P: 201
Thank you very much Rabbit. I'll give both options a go and see which one works best.

Thanks for your input NeoPa
Aug 18 '12 #9

reginaldmerritt
100+
P: 201
Just encase anyone is interested, in this instance using OR works a quicker than UNION, not by much but there are not many records at the moment and the time difference will probably be amplified as the system is used more.

Thanks for your help Rabbit it is very obvious that OR works, thank you for pointing it out to me, sorry for not seeing this before adding this post.
Aug 20 '12 #10

NeoPa
Expert Mod 15k+
P: 31,261
Reginald, even when you were under that misaprehension, you responded politely and thanked Rabbit for his input. No-one should think any less of you (including yourself) for not being fully aware of the situation at that point in time. We're all here to learn and teach. It's all part of the process.
Aug 20 '12 #11

Post your reply

Sign in to post your reply or Sign up for a free account.