DATE BASED QUERY - IIf STATEMENT NOT WORKING

[Ssnake57]

I have a DataBase Table [Unit_Rental_Info]. My fields are [Unit #], [Total Cost], [Change Date], [New Cost]. I need my code set up so that in my Query it will show the [Total Cost] for all date ranges up to [Change Date], and the [New Cost] for all date ranges after Change Date?
I tried

IIf([OpData]![Month] <= [Unit_Rental_Info]![Change Date], [Unit_Rental_Info]![Total Cost], [Unit_Rental_Info]![New Cost]).

I have tried this as a Query Field and as a Criteria and I the results are not accurate. Any help you can provide would be appreciated.

Larry

 

[nsukari]

Perhaps I'm not understanding what you want. First Where does the [OpData]![Month] come in?

If what you want is to show, for all records in the &quot;Unit_Rental_Info&quot; table, the field [Total Cost] for anything with a change date before and including today and [New Cost] for everything tomorrow and beyond, I don't see why you would not use this query:
IIf([Unit_Rental_Info]![Change Date]<=Date(),[Unit_Rental_Info]![Total Cost],[Unit_Rental_Info]![New Cost])

Otherwise, please explain where the [OpData]![Month] comes in and perhaps I can help you further.

 

[Ssnake57]

Thank You for your response, nsukari

I apologize, The Table [OpData] is a table of operational data on rental equipment. The Field [OpData]![Month] is the month in which that data was recorded. The Table [Unit_Rental_Info] relates to the monthly rent that is paid on the equipment.
[Total Cost] is the name of the current Rental Cost Field and [New Cost]is the name of the field related to a new Rate Increase and [Change Date] is the field to record when the Rate changed.

My problem arises when I do a report based on a time frame (say a quarter) in which there has been a rate increase. I need the data to show the accurate costs for the months before and after a Rate increase. At this time I am getting duplicate records with both old and new rental rates on the units that have eseen a rate increase.

Larry

 

[Ssnake57]

One other bit of information, The fields [Change Date] and [New Cost] are Null if no Rate increase has been implemented on specific Units. [UnitID] is a filed in both tables.

 

[nsukari]

Do you have this in your query?

INNER JOIN OpData ON Unit_Rental_Info.UnitID = OpData.UnitID

When I set up two sample tables with the same fields and used your query, it worked for me without giving me duplicates. Without that statement, you get duplicates.