Can I use IF-Then-Else in Access Queries

M

Minnow

I have a table called Readings, there are four fields: CustomerNumber,
StartReading, EndReading, ReadingUnit. There are two options for ReadingUnit,
it could be either Hour or Litre.

Now I want to make a query called LitreProcessed, if ReadingUnit is Litre
then LitreProcessed=EndReading-StartReading, else
LitreProcessed=(EndReading-StartReading)*60*0.12

Is it doable in Access, how?

Any reply will be hightly appreciated.
 
E

Eric D via AccessMonster.com

Look up IIF in Help

IIF([ReadingUnit]="Litre", [LitreProcessed]=([EndReading]-[StartReading]),
[LitreProcessed]=((([EndReading]-[StartReading])*60)*.12))

Not sure I got the syntax exactly correct, but this would be how you would do
it.
 
F

fredg

I have a table called Readings, there are four fields: CustomerNumber,
StartReading, EndReading, ReadingUnit. There are two options for ReadingUnit,
it could be either Hour or Litre.

Now I want to make a query called LitreProcessed, if ReadingUnit is Litre
then LitreProcessed=EndReading-StartReading, else
LitreProcessed=(EndReading-StartReading)*60*0.12

Is it doable in Access, how?

Any reply will be hightly appreciated.
Click to expand...

You can us IIF() directly in a query.

LitrePrecessed:IIf([ReadingUnit] = "Litre" , [EndReading] -
[StartReading], ([EndReading]-[StartReading]) * 60 * 0.12)
 
P

PC Datasheet

How can ReadingUnit be either Hour or Litre? Hour is a time measurement.
Isn't Litre a liquid volume measurement?
 
M

Minnow

Thank you all.

I wrote the following function. It tells me runtime error "5" and the
debugger highlighted the line of "If u="L" then", what is wrong with it?

Function LitersProcessed(ByVal u As String, ByVal r1 As Double, ByVal r2 As
Double) As Double
Dim lp As Double

If u = "L" Then
lp = r2 - r1
Else
lp = HourToLiter(r2 - r1)

End If


LitersProcessed = lp
End Function
 
Top