IIF Statement with comparison operators in the true and false parts (1 Viewer)

chugo

New member
Local time
Today, 07:20
Joined
May 2, 2013
Messages
9
Really struggling with something that I thought would be straightforward. It may just be the way I'm going about implementing. I have a report where my customer wants to be able to input a value and then be able to select whether the report shows values above or below that value. I have a combo box that has 2 values (1=Less than or equal to, 2=greater than or equal to). There is a text box where he inputs the rate to compare against (e.g. $75). When he hits submit, the application stores the values of the combo box and text box into global variable and then I have a public function that can be used to retrieve the values. GetHRate() gets the value to compare against and GetHRateCompare() gets the value to indicate <=(1) or >=(2).

In my query for criteria for rate I have this expression:
IIf(GetHRateCompare()=1,"'<=",">=") & GetHrate()

I've tried every combination of double quotes, single quotes, no quotes moving the GetHRate inside the IIf statement and nothing seems to work.

If I just hardcode <=GetHrate() into the criteria it works perfect but that does not achieve my goal of letting him select over/under at run time. Here's the full query:

SELECT qryCustomers_AverageTimeByScheduleID.ServiceName, qryCustomers_AverageTimeByScheduleID.ScheduleID, qryCustomers_AverageTimeByScheduleID.SumOfTotalTime, qryCustomers_AverageTimeByScheduleID.PropertyID, qryCustomers_AverageTimeByScheduleID.PropertyName, qryCustomers_AverageTimeByScheduleID.MonthCount, qryCustomers_AverageTimeByScheduleID.AverageTime, qryCustomers_AverageTimeByScheduleID.ServiceTime, qryCustomers_AverageTimeByScheduleID.ServiceCost, qryCustomers_AverageTimeByScheduleID.TotalStopsPerMonth, qryCustomers_AverageTimeByScheduleID.HourlyRate, qryCustomers_AverageTimeByScheduleID.ProjectedHourlyRate, [HourlyRate]-[ProjectedHourlyRate] AS RateDiff, qryCustomers_AverageTimeByScheduleID.CustomerName, qryCustomers_AverageTimeByScheduleID.CustomerID
FROM qryCustomers_AverageTimeByScheduleID
WHERE (((qryCustomers_AverageTimeByScheduleID.HourlyRate)=IIf(GetHRateCompare()=1,"'<=",">=") & GetHrate()));

Any thoughts? I'm about to pull my hair out...
 

chugo

New member
Local time
Today, 07:20
Joined
May 2, 2013
Messages
9
That sound you hear is me kicking myself in the a$$. It's amazing how you get one solution stuck in your head and you refuse to see the simple answer staring you in the face.

Thanks! That was a much easier way to do it.
 

Users who are viewing this thread

Top Bottom