IIF in query using "<" sign

[reginaldmerritt]

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

IIf(Format([Forms].[FRMExamVenueHireDetails].[PickupDate],"mm/dd/yyyy")=[ReturnDate],([TBExamVenueHire].[ReturnTimeProportion])<[Forms].[FRMExamVenueHireDetails].[PickUpTimeProportion])

So I want the criteria "[TBExamVenueHire].[ReturnTimePropo rtion])<[Forms].[FRMExamVenueHir eDetails].[PickUpTimePropo rtion]" 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

SELECT TBExamVenueHire.ExamVenueHireID, TBExamVenueHire.ExamVenueID, TBExamVenueHire.PickUpDate, TBExamVenueHire.ReturnDate, TBExamVenueHire.PickUpTimeProportion, TBExamVenueHire.ActualReturnDateTime, TBExamVenueHire.ExamVenueHireStatusID
FROM TBExamVenueHire
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])));

[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).

[twinnyfo]

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:

CriteriaTest: IIf([Forms].[FRMExamVenueHireDetails].[PickupDate]=[ReturnDate], IIf([TBExamVenueHire].[ReturnTimeProportion])<[Forms].[FRMExamVenueHireDetails].[PickUpTimeProportion], True, False), True)

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 ReturnTimePropo rtion < PickUpTimePropo rtion. 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.

[Rabbit]

There's no need to use IIf(). You can just OR your conditions.

[reginaldmerritt]

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 ReturnTimePropo rtion.

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

[Rabbit]

Actually, OR will work with what you're trying to do.

SELECT 
	TBExamVenueHire.ExamVenueHireID, 
	TBExamVenueHire.ExamVenueID, 
	TBExamVenueHire.PickUpDate, 
	TBExamVenueHire.ReturnDate, 
	TBExamVenueHire.PickUpTimeProportion, 
	TBExamVenueHire.ActualReturnDateTime, 
	TBExamVenueHire.ExamVenueHireStatusID 
FROM 
	TBExamVenueHire 
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])<[Forms].[FRMExamVenueHireDetails].[PickUpTimeProportion] AND
		[Forms].[FRMExamVenueHireDetails].[PickupDate]=[ReturnDate] OR
		[Forms].[FRMExamVenueHireDetails].[PickupDate]>[ReturnDate]
	)

[NeoPa]

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.

[Rabbit]

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.

[reginaldmerritt]

Thank you very much Rabbit. I'll give both options a go and see which one works best.

Thanks for your input NeoPa

[reginaldmerritt]

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.

[NeoPa]

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.