Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

iif function

Status
Not open for further replies.

lasd

Programmer
Jan 14, 2005
48
IE
hi
am trying to use the IIF function in my database.

i have a column called interval(just wondering if my way of writing this is wrong. as this column contains a date and a time. If i write the time only will it pick it up in the query). what i want to do is say that if the value in this column is > 23:00:00 then use a night figure and if not use a day figure.Import is the table i am using.

eg
iif(Import.interval>23:00:00, Import.DayFigure * 3, Import.NightFigure*3)
Is my syntax completely wrong here.
thanks a million in advance for your help.

kindest regards
lasd
 
iif(timevalue(import.interval)>#23:00:00#, ......

Of course the only times that this test will be true are from 11pm to midnight.

 
so if i put iif(timevalue(Import.Interval)>#23:00:00# <#08:00:00, Import.DayFigure*3, Import.NightFigure*3)

will this work everytime i try to put this into the query is brings back that the syntax is incorrect
 
iif(timevalue(import.interval)>#23:00:00# or timevalue(import.interval) < #8:00:00#, ......, ......
 
Thanks a million that is working now.can I run something else by you. I will try to explain this as well as I can.
I have an import table. This table will contain values which will be calculated using other tables in my database.
lets say i want to calculate a unit charge.

TotalUnitCharge= day/night figure (calculated above using the iif function) * a single UnitCharge.

My problem is that this single UnitCharge is stored in another table called Type. I have used combo boxes in this table. Lets say type 1 has a value of 2 and type 2 has a value of three.
how do i distinguish between the two UnitCharges if they are in the same table on different rows, the type being selected using combo boxes.

sorry its so long winded. Thanks a mill

lasd
 
nope I did it on the table when i was in design view and used the lookup tab. i used the combo box selection here
 
It depends on what you are storing in the main table.
If you are storing the looked up value then you can use that main table field in your query, but if you are storing the lookup id then you will have either have to use another iif statement to work out the value or you will need to join the lookup table to the main table in your query and get the value from the other table.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top