Nested IIF more than 10

[PA]

Dear All,

I have query1 as follows:

Date Client Qty Time
01-Mar A 10 745
02-Mar A 20 945
05-Mar B 25 1215
08-Mar C 30 1345
01-Mar B 35 1535
02-Mar C 40 1935
05-Mar B 15 2125


I want to add 1 new field called Time_up_to (next to column Time) to this
query1 by using iif as has result as follow:

Date Client Qty Time Time_up_to
01-Mar A 10 745 800
02-Mar A 20 945 1000
05-Mar B 25 1215 1230
08-Mar C 30 1345 1400
01-Mar B 35 1535 1600
02-Mar C 40 1935 2000
05-Mar B 15 2125 2200
...............
................
.........
1000 more records..

To get result for field "Time_up_to", I may need to use IIF nested function
more than 10(if possible in access 2003?) , is there anyway I can make it
simple ?

Appreciate your advise.

Thanks alot,

PA

 

[raskew via AccessMonster.com]

A nested Iif() statement takes on the format Iif(Expression, True Statement,
False Statement).. Where does the true/false issue come into play with your
example?

 

[Dale Fye]

Two suggestions.

1. Don't use nested IIF( ) statements. They are extremely difficult to
read when debugging. One method would be to use the Switch function. This
function accepts a series of parameter pairs with the first value in the pair
being an expression that evaluates to true or false, the second value in the
pair is the value to be used if the expression evaluates to True. It looks
something like:

?Switch(Variable = 1, 1, Variable < 4, 2, Variable < 10, 3, True, 0)

2. Personally, if you have business logic that is so complicated that you
could potentially need 10 nested IIF( ) statements, I would write a function.
This gives you the abilty to document each of the steps. It is much easier
to read and maintain. You will thank yourself later for doing it this way.

 

[Marshall Barton]

I have query1 as follows:

Date Client Qty Time
01-Mar A 10 745
02-Mar A 20 945
05-Mar B 25 1215
08-Mar C 30 1345
01-Mar B 35 1535
02-Mar C 40 1935
05-Mar B 15 2125


I want to add 1 new field called Time_up_to (next to column Time) to this
query1 by using iif as has result as follow:

Date Client Qty Time Time_up_to
01-Mar A 10 745 800
02-Mar A 20 945 1000
05-Mar B 25 1215 1230
08-Mar C 30 1345 1400
01-Mar B 35 1535 1600
02-Mar C 40 1935 2000
05-Mar B 15 2125 2200
..............
...............
........
1000 more records..

To get result for field "Time_up_to", I may need to use IIF nested function
more than 10(if possible in access 2003?) , is there anyway I can make it
simple ?

Believe us when we say that you would NOT like using more
than two nested IIf to do any calculation.

How about you explain what the TimeUpTo value is and we'll
see if there's a cleaner way to do it.

 

[dennisfed]

what i used is a dymanic variable that i manipulate manually
buildvar = buildvar & "bla bla bla"
as you go thru your if statements you can build this and include it in your
where statement
CurrentPrint = False ' public boolian current record or all
' true is only for current record
stDocName = "rptGeneralAuditTrack" 'stDocName is created by access

DoCmd.OpenReport stDocName, acViewPreview, , BuildVar

 

[Jack Leach]

This is my favorite method of doing this as well.

I use a setup on my search forms with module level variables for different
portions of the where clause. Each time a control is changed it sets the
appropriate variable and re-contencates and resets the recordsource with the
resulting string. These forms contain nearly every user-interfacable field
value as criteria and don't bog down at all when used average performance
systems.

Building an SQL this way is an extremely powerful method for handling large
amounts of changes in a query.

I'm not sure how this would work for the poster's problem, but I figured I'd
throw it out there.



--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[JimBurke via AccessMonster.com]

It looks to me that you're setting Time_up_to to the nearest half hour that
is >= time. Is that the case? Is time defined as an actual date/time field?
The way you have it displayed makes it look as if it's a text field.

Assuming it's a date/time field, you can create a field in your query like
this:

Time_up_to: DateAdd("n", 30 - (Minute(Time) Mod 30), Time)

This would add a half hour to a time that is on the hour or half hour, so if
you want those times to stay as they are then it would be

Time_up_to: IIf(Minute(Time) In (0,30), Time, DateAdd("n", 30 - (Minute(Time)
Mod 30), Time)

If Time is a text field then you'd have to use some VBA code to do it.

 

[raskew via AccessMonster.com]

Perhaps since PA posted the problem, perhaps he/she could be troubled to
provide some explanation.

Bob

It looks to me that you're setting Time_up_to to the nearest half hour that
is >= time. Is that the case? Is time defined as an actual date/time field?
The way you have it displayed makes it look as if it's a text field.

Assuming it's a date/time field, you can create a field in your query like
this:

Time_up_to: DateAdd("n", 30 - (Minute(Time) Mod 30), Time)

This would add a half hour to a time that is on the hour or half hour, so if
you want those times to stay as they are then it would be

Time_up_to: IIf(Minute(Time) In (0,30), Time, DateAdd("n", 30 - (Minute(Time)
Mod 30), Time)

If Time is a text field then you'd have to use some VBA code to do it.

Dear All,

[quoted text clipped - 34 lines]

PA

 

[PA]

Dear All,

Sorry for late response, very appreciate to all your help/responses. That is
correct that I try to make 30 min block time but just in number format(not in
time format).

I can do this task in MS Excel 2003 with user defined function as follows:
( but since the database has rows/records >65,000 , I need to put these data
into ms access 2003 instead).

As you can see that if I use IIF if will have nested IIF >10(if possible in
access 2003), and a bit hard to write it.
That is why I have a thought there may have compatible/equivalent VB code
in ms access 2003 that can be used in my access database rather than IIF
function.


Again thanks a lot for your help

Best Regards

PA
-----------------------

VBA code in excel 2003 as follows:

Function TUT(Time)


If Time <= 700 Then
TUT = 700

ElseIf Time <= 730 Then
TUT = 730

ElseIf Time <= 900 Then
TUT = 900

ElseIf Time <= 1200 Then
TUT = 1200

ElseIf Time <= 1230 Then
TUT = 1230

ElseIf Time <= 1300 Then
TUT = 1300

ElseIf Time <= 1330 Then
TUT = 1330

ElseIf Time <= 1400 Then
TUT = 1400

ElseIf Time <= 1430 Then
TUT = 1430

ElseIf Time <= 1500 Then
TUT = 1500

ElseIf Time <= 1530 Then
TUT = 1530

ElseIf Time <= 1600 Then
TUT = 1600

ElseIf Time <= 1630 Then
TUT = 1630

ElseIf Time <= 1700 Then
TUT = 1700

ElseIf Time <= 1730 Then
TUT = 1730

ElseIf Time <= 1800 Then
TUT = 1800

ElseIf Time <= 1830 Then
TUT = 1830

ElseIf Time <= 1900 Then
TUT = 1900

ElseIf Time <= 1930 Then
TUT = 1930

ElseIf Time <= 2000 Then
TUT = 2000

ElseIf Time <= 2030 Then
TUT = 2030

ElseIf Time <= 2100 Then
TUT = 2100

ElseIf Time <= 2130 Then
TUT = 2130

ElseIf Time <= 2200 Then
TUT = 2200

ElseIf Time <= 2230 Then
TUT = 2230

ElseIf Time <= 2300 Then
TUT = 2300

ElseIf Time <= 2330 Then
TUT = 2330


Else
Tgroup = 2400

End If

Perhaps since PA posted the problem, perhaps he/she could be troubled to
provide some explanation.

Bob

It looks to me that you're setting Time_up_to to the nearest half hour that
is >= time. Is that the case? Is time defined as an actual date/time field?
The way you have it displayed makes it look as if it's a text field.

Assuming it's a date/time field, you can create a field in your query like
this:

Time_up_to: DateAdd("n", 30 - (Minute(Time) Mod 30), Time)

This would add a half hour to a time that is on the hour or half hour, so if
you want those times to stay as they are then it would be

Time_up_to: IIf(Minute(Time) In (0,30), Time, DateAdd("n", 30 - (Minute(Time)
Mod 30), Time)

If Time is a text field then you'd have to use some VBA code to do it.

Dear All,

[quoted text clipped - 34 lines]

PA

 

[John W. Vinson]

Sorry for late response, very appreciate to all your help/responses. That is
correct that I try to make 30 min block time but just in number format(not in
time format).

Well... that's not really a number, because a Number field could be 784, but
that's not meaningful in a sexagesimal time value.

If you have an Integer and you want it to round up in the sense x00 - x30 =>
x30, x31 to x59 =>(x+1)00, and x60-x99 generates an error:

Private Sub Tut(tTime As Integer) As Variant
Select Case tTime Mod 100
Case 0
Tut = tTime
Case 1-30
Tut = 100 * (tTime \ 100) + 30
Case 31-59
Tut = 100 * (tTime \ 100) + 100
Case 60-99
Tut = Null
MsgBox "Invalid time value " & tTime
End Select
End Sub

If (as I'd strongly recommend!) you use an actual Date/Time value you can use
similar logic, but simpler since you can use the Minute() function of the time
value:

Private Sub Tut(tTime As Variant) As Variant
If IsDate(tTime) Then ' is it a valid date/time value?
Select Case Minute(tTime)
Case 0
Tut = tTime
Case 1-30
Tut = Hour(tTime) + 3600 ' 3600 seconds = 30 minutes
Case 31-59
Tut = Hour(tTime) + 1
End Select
Else
MsgBox "Invalid time value " & Nz(tTime, "<NULL>")
End If
End Sub

 

[PA]

Thanks alot, John

PA

Well... that's not really a number, because a Number field could be 784, but
that's not meaningful in a sexagesimal time value.

If you have an Integer and you want it to round up in the sense x00 - x30 =>
x30, x31 to x59 =>(x+1)00, and x60-x99 generates an error:

Private Sub Tut(tTime As Integer) As Variant
Select Case tTime Mod 100
Case 0
Tut = tTime
Case 1-30
Tut = 100 * (tTime \ 100) + 30
Case 31-59
Tut = 100 * (tTime \ 100) + 100
Case 60-99
Tut = Null
MsgBox "Invalid time value " & tTime
End Select
End Sub

If (as I'd strongly recommend!) you use an actual Date/Time value you can use
similar logic, but simpler since you can use the Minute() function of the time
value:

Private Sub Tut(tTime As Variant) As Variant
If IsDate(tTime) Then ' is it a valid date/time value?
Select Case Minute(tTime)
Case 0
Tut = tTime
Case 1-30
Tut = Hour(tTime) + 3600 ' 3600 seconds = 30 minutes
Case 31-59
Tut = Hour(tTime) + 1
End Select
Else
MsgBox "Invalid time value " & Nz(tTime, "<NULL>")
End If
End Sub

 

[PA]

Thanks alot, John

PA

Well... that's not really a number, because a Number field could be 784, but
that's not meaningful in a sexagesimal time value.

If you have an Integer and you want it to round up in the sense x00 - x30 =>
x30, x31 to x59 =>(x+1)00, and x60-x99 generates an error:

Private Sub Tut(tTime As Integer) As Variant
Select Case tTime Mod 100
Case 0
Tut = tTime
Case 1-30
Tut = 100 * (tTime \ 100) + 30
Case 31-59
Tut = 100 * (tTime \ 100) + 100
Case 60-99
Tut = Null
MsgBox "Invalid time value " & tTime
End Select
End Sub

If (as I'd strongly recommend!) you use an actual Date/Time value you can use
similar logic, but simpler since you can use the Minute() function of the time
value:

Private Sub Tut(tTime As Variant) As Variant
If IsDate(tTime) Then ' is it a valid date/time value?
Select Case Minute(tTime)
Case 0
Tut = tTime
Case 1-30
Tut = Hour(tTime) + 3600 ' 3600 seconds = 30 minutes
Case 31-59
Tut = Hour(tTime) + 1
End Select
Else
MsgBox "Invalid time value " & Nz(tTime, "<NULL>")
End If
End Sub