Compound IF Statement?

[Guest]

good day...

I have a query that I have created that has a date difference feild on it
from invoice date to current/today's date.

I want to have another field in my query so that if days outstanding:

29<60 then "Balance Due Net 30"

60<90 then "Balance Due Net 60"

90 then "Balance Due Net 90"

Any suggestions on how I can accomplish this?

Thanks,

Brook

 

[Duane Hookom]

I would create a small user defined function that accepts the invoice date
and returns a string

Public Function GetTerms(pdatInvoice as Date) as String
Select Case pdatInvoice
Case 0 to 29
GetTerms = "Some String"
Case 20 to 59
GetTerms = "Balance Due Net 30"
Case 59 to 89
GetTerms = "Balance Due Net 60"
Case Else
GetTerms = "Balance Due Net 90"
End Select
End Function

Save this function in a standard module named "basBusinessCalcs". You can
then maintain this outside of a query. This type of calculation belongs in
only one place.

 

[Guest]

Thanks for the post, but i'm unsure how to set it up and how to impliment
your suggestion?

Brook

 

[Duane Hookom]

You open a new, blank module and then paste my code (modified code below)
into it from the "Public..." to "End Function". Then you exit and save the
module. You can now use the function in a query, controlsource, or any place
else you would use any function (except default for a field).

In a query, you might have a column:
Terms: GetTerms([InvoiceDate])

Public Function GetTerms(pdatInvoice as Date) as String
Select Case DateDiff("d",pdatInvoice, Date)
Case 0 to 29
GetTerms = "Some String"
Case 20 to 59
GetTerms = "Balance Due Net 30"
Case 59 to 89
GetTerms = "Balance Due Net 60"
Case Else
GetTerms = "Balance Due Net 90"
End Select
End Function

 

[Guest]

Thanks Duane,

I will work on this and post a response in a little while..

thanks again..

Brook

You open a new, blank module and then paste my code (modified code below)
into it from the "Public..." to "End Function". Then you exit and save the
module. You can now use the function in a query, controlsource, or any place
else you would use any function (except default for a field).

In a query, you might have a column:
Terms: GetTerms([InvoiceDate])

Public Function GetTerms(pdatInvoice as Date) as String
Select Case DateDiff("d",pdatInvoice, Date)
Case 0 to 29
GetTerms = "Some String"
Case 20 to 59
GetTerms = "Balance Due Net 30"
Case 59 to 89
GetTerms = "Balance Due Net 60"
Case Else
GetTerms = "Balance Due Net 90"
End Select
End Function


--
Duane Hookom
MS Access MVP
--

Thanks for the post, but i'm unsure how to set it up and how to impliment
your suggestion?

Brook

 

[John Vinson]

good day...

I have a query that I have created that has a date difference feild on it
from invoice date to current/today's date.

I want to have another field in my query so that if days outstanding:




Any suggestions on how I can accomplish this?

Thanks,

Brook

Duane's suggestion of a custom function is probably the most flexible
and readable approach, but if this is all you need to do then the
builtin Switch() function is an alternative. In a vacant query Field
cell you can type

BalLabel: Switch([field] < 30, Null, [Field] < 60, "Balance Due Net
30", [Field] < 90, "Balance Due Net 60", [Field] < 120, "Balance Due
Net 90", True, "Blankety-blank slow-paying cheapskate!")

well... you might want to edit or omit the last option... <g>

John W. Vinson[MVP]

 

[Duane Hookom]

Actually the solution I would create in any of my applications would involve
a small lookup table of day ranges and "balance title"...

--
Duane Hookom
MS Access MVP
--

good day...

I have a query that I have created that has a date difference feild on it
from invoice date to current/today's date.

I want to have another field in my query so that if days outstanding:




Any suggestions on how I can accomplish this?

Thanks,

Brook

Duane's suggestion of a custom function is probably the most flexible
and readable approach, but if this is all you need to do then the
builtin Switch() function is an alternative. In a vacant query Field
cell you can type

BalLabel: Switch([field] < 30, Null, [Field] < 60, "Balance Due Net
30", [Field] < 90, "Balance Due Net 60", [Field] < 120, "Balance Due
Net 90", True, "Blankety-blank slow-paying cheapskate!")

well... you might want to edit or omit the last option... <g>

John W. Vinson[MVP]

 

[Guest]

Thank you everyone for your suggestions, I actually used Duane's initial
method and it works great for my application.

again..

Thank you

Brook

Actually the solution I would create in any of my applications would involve
a small lookup table of day ranges and "balance title"...

--
Duane Hookom
MS Access MVP
--

good day...

I have a query that I have created that has a date difference feild on it
from invoice date to current/today's date.

I want to have another field in my query so that if days outstanding:

29<60 then "Balance Due Net 30"

60<90 then "Balance Due Net 60"

90 then "Balance Due Net 90"

Any suggestions on how I can accomplish this?

Thanks,

Brook

Duane's suggestion of a custom function is probably the most flexible
and readable approach, but if this is all you need to do then the
builtin Switch() function is an alternative. In a vacant query Field
cell you can type

BalLabel: Switch([field] < 30, Null, [Field] < 60, "Balance Due Net
30", [Field] < 90, "Balance Due Net 60", [Field] < 120, "Balance Due
Net 90", True, "Blankety-blank slow-paying cheapskate!")

well... you might want to edit or omit the last option... <g>

John W. Vinson[MVP]