Conditional Message Calculation

[Patti]

Hello,
I'm working with a range of numbers and I want to produce a field that says:

30 days

30-60 days
61-90 days
91-120 days
over 120 days

How can I set up this query? I've been playing around with update query but
really getting nowhere. IN excel its rather simple, so it should be easy to
do in Access, I just can't figure it out. :
=IF(AG2<30,"< 30 days",IF(AND(AG2>29,AG2<61),"30-60
days",IF(AND(AG2>59,AG2<91),"61-90 days",IF(AND(AG2>90, AG2< 121),"91-120
days", IF(AG2>120,">120 days")))))

 

[Jerry Whittle]

While you could create something similar in Access with an IIf statement, a
Case statement in a function works better and easier to maintain. Put
something like below in a module:

Function fAG2(strAG2 As Variant) As String
Dim The AG2 As String
Select Case strAG2
Case Is < 30
TheAG2 = "30 DAYS"
Case 30 To 60
TheAG2 = "30-60 days"
Case 61 To 90
TheAG2 = "61-90 days"
Case 91 To 120
TheAG2 = "91-120 days"
Case Is > 120
TheAG2 = ">120 days"
Case Else ' Other values.
TheAG2 = "Unknown"
End Select
fAG2 = TheAG2
End Function


Then in the query put something like below in the field:

The AG2: fAG2([AG2])

 

[Dale Fye]

Patti

Take a look at the switch command:

= switch([AG2] < 30, "< 30 days", [AG2] < 61, "30 - 60 days", [AG2] < 91,
"61-90 days", ...)


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Patti]

Thanks Dale, where would I put this switch command? Is it a field in a query?

Patti

Take a look at the switch command:

= switch([AG2] < 30, "< 30 days", [AG2] < 61, "30 - 60 days", [AG2] < 91,
"61-90 days", ...)


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hello,
I'm working with a range of numbers and I want to produce a field that says:
30-60 days
61-90 days
91-120 days
over 120 days

How can I set up this query? I've been playing around with update query but
really getting nowhere. IN excel its rather simple, so it should be easy to
do in Access, I just can't figure it out. :
=IF(AG2<30,"< 30 days",IF(AND(AG2>29,AG2<61),"30-60
days",IF(AND(AG2>59,AG2<91),"61-90 days",IF(AND(AG2>90, AG2< 121),"91-120
days", IF(AG2>120,">120 days")))))

 

[Dale Fye]

In the same place you were going to put the IF command. You could put it in
a query, or as the control source of a control on a form.

The nice thing about switch, is that you are entering a series of arguments
that will evaluate to True or False, followed by what you want the Switch
function to return. It will go through the arguments until if finds the
first one that is satisfied. So, in your case, you don't have to test for

29 and < 61, you just have to test for <61).

I generally include a final argument two argument of True and "Unknown" to
make sure that the function will actually return a value if none of the
previous arguments evaluate to TRUE.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Thanks Dale, where would I put this switch command? Is it a field in a query?

Patti

Take a look at the switch command:

= switch([AG2] < 30, "< 30 days", [AG2] < 61, "30 - 60 days", [AG2] < 91,
"61-90 days", ...)


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hello,
I'm working with a range of numbers and I want to produce a field that says:
30 days
30-60 days
61-90 days
91-120 days
over 120 days

How can I set up this query? I've been playing around with update query but
really getting nowhere. IN excel its rather simple, so it should be easy to
do in Access, I just can't figure it out. :
=IF(AG2<30,"< 30 days",IF(AND(AG2>29,AG2<61),"30-60
days",IF(AND(AG2>59,AG2<91),"61-90 days",IF(AND(AG2>90, AG2< 121),"91-120
days", IF(AG2>120,">120 days")))))