Conditional Message Calculation


P

Patti

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")))))
 
Ad

Advertisements

J

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])
 
D

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.
 
P

Patti

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

Dale Fye said:
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 said:
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")))))
 
Ad

Advertisements

D

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.



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

Dale Fye said:
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 said:
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")))))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top