Prev: Need help for Append Only feature in Access 2007
Next: Exclude item from a Report
From: rpbsr on 22 Jun 2008 12:26
Someone here was kind enough to suggest a module for an application I'm
working on involving the calculation of a discount based on household income
and the number of people. I want to develop a form such that the discount
field is filled in when they enter the household income and number in the
household. How do I use the module with the form?

Thanks for your help.
From: Ken Snell (MVP) on 22 Jun 2008 12:41
If all you want is to fill in a value, you probably don't need a module.
Just use a textbox on the form to hold the "discount" value, and use an
expression as the Control Source of that textbox, where the expression
returns the discount amount.

You'll need to give us many more details before we can provide more specific
recommendations.

--

Ken Snell
<MS ACCESS MVP>


"rpbsr" <rpbsr(a)discussions.microsoft.com> wrote in message
news:543A238F-1C75-41F2-8307-855B7B9B3F6A(a)microsoft.com...
> Someone here was kind enough to suggest a module for an application I'm
> working on involving the calculation of a discount based on household
> income
> and the number of people. I want to develop a form such that the discount
> field is filled in when they enter the household income and number in the
> household. How do I use the module with the form?
>
> Thanks for your help.


From: rpbsr on 22 Jun 2008 13:27
Hi Ken,

I need to assign discounts based on the number of people in the household and
the income. For example:
80% if 8 people and <$78K
80% if 7 people and <$73K
....80% if 1 person and <$41K

50% if 8 people and <$49K
50% if 7 people and <$46K
....

30% if 8 people and <$29K
....

The VBA module suggested was:

For sake of discussion I assume your table
is tblHouseholds, with fields HouseholdID, NumberOfPeople, Income, and
all three fields required.

select CalcDiscount(NumberOfPeople, Income)
from tblHouseholds

Then in a standard module write:
Public Function CalcDiscount(ByVal NumberOfPeople As Integer, ByVal
Income As Currency) As Single
Dim sngPercent As Single
sngPercent = 0
Select Case NumberOfPeople
Case 1
Select Case Income
Case 0 To 10000
sngPercent = 0.1 '0.1 = 10%
Case 10000 To 25000
sngPercent = 0.2
'etc.
End Select
Case 2
Select Case Income
Case 0 To 11000
sngPercent = 0.11
Case 11000 To 26000
sngPercent = 0.22
'etc.
End Select
'etc.
End Select
CalcDiscount = sngPercent
End Function


"Ken Snell (MVP)" wrote:

> If all you want is to fill in a value, you probably don't need a module.
> Just use a textbox on the form to hold the "discount" value, and use an
> expression as the Control Source of that textbox, where the expression
> returns the discount amount.
>
> You'll need to give us many more details before we can provide more specific
> recommendations.
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
> "rpbsr" <rpbsr(a)discussions.microsoft.com> wrote in message
> news:543A238F-1C75-41F2-8307-855B7B9B3F6A(a)microsoft.com...
> > Someone here was kind enough to suggest a module for an application I'm
> > working on involving the calculation of a discount based on household
> > income
> > and the number of people. I want to develop a form such that the discount
> > field is filled in when they enter the household income and number in the
> > household. How do I use the module with the form?
> >
> > Thanks for your help.
>
>
>
From: Ken Snell (MVP) on 23 Jun 2008 21:57
May I recommend that you create a table (name it tblDiscounts) that you can
use to store the various scenarios? This would be its structure:

DiscountID (primary key -- autonumber)
DiscountAmt
PeopleNumber
MaxIncome


Populate this table with your data.

Then, create a query based on this table. Name it qryDiscounts:

SELECT DiscountAmt, PeopleNumber, MaxIncome
FROM tblDiscounts
ORDER BY PeopleNumber, MaxIncome;


Then, you can use a DLookup expression for the ControlSource of a textbox on
the form (no module needed):

=DLookup("DiscountAmt", "qryDiscounts", "PeopleNumber = " &
[NumberofPeopleControlOnForm] & " And " & [IncomeControlOnForm] &
"<[MaxIncome]")

--

Ken Snell
<MS ACCESS MVP>



"rpbsr" <rpbsr(a)discussions.microsoft.com> wrote in message
news:BD15E0A5-073F-441F-94C7-AD1C6B130A28(a)microsoft.com...
> Hi Ken,
>
> I need to assign discounts based on the number of people in the household
> and
> the income. For example:
> 80% if 8 people and <$78K
> 80% if 7 people and <$73K
> ...80% if 1 person and <$41K
>
> 50% if 8 people and <$49K
> 50% if 7 people and <$46K
> ...
>
> 30% if 8 people and <$29K
> ...
>
> The VBA module suggested was:
>
> For sake of discussion I assume your table
> is tblHouseholds, with fields HouseholdID, NumberOfPeople, Income, and
> all three fields required.
>
> select CalcDiscount(NumberOfPeople, Income)
> from tblHouseholds
>
> Then in a standard module write:
> Public Function CalcDiscount(ByVal NumberOfPeople As Integer, ByVal
> Income As Currency) As Single
> Dim sngPercent As Single
> sngPercent = 0
> Select Case NumberOfPeople
> Case 1
> Select Case Income
> Case 0 To 10000
> sngPercent = 0.1 '0.1 = 10%
> Case 10000 To 25000
> sngPercent = 0.2
> 'etc.
> End Select
> Case 2
> Select Case Income
> Case 0 To 11000
> sngPercent = 0.11
> Case 11000 To 26000
> sngPercent = 0.22
> 'etc.
> End Select
> 'etc.
> End Select
> CalcDiscount = sngPercent
> End Function
>
>
> "Ken Snell (MVP)" wrote:
>
>> If all you want is to fill in a value, you probably don't need a module.
>> Just use a textbox on the form to hold the "discount" value, and use an
>> expression as the Control Source of that textbox, where the expression
>> returns the discount amount.
>>
>> You'll need to give us many more details before we can provide more
>> specific
>> recommendations.
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
>>
>> "rpbsr" <rpbsr(a)discussions.microsoft.com> wrote in message
>> news:543A238F-1C75-41F2-8307-855B7B9B3F6A(a)microsoft.com...
>> > Someone here was kind enough to suggest a module for an application I'm
>> > working on involving the calculation of a discount based on household
>> > income
>> > and the number of people. I want to develop a form such that the
>> > discount
>> > field is filled in when they enter the household income and number in
>> > the
>> > household. How do I use the module with the form?
>> >
>> > Thanks for your help.
>>
>>
>>


From: rpbsr on 24 Jun 2008 08:31
Ken,
Thanks, I'm glad a module won't be necessary for this. One question: for
[NumberOfPeopleControlOnForm] and [IncomeControlOnForm], I'd like these to
come to the form from a tblMembers. Will this work as you recommend? If not,
how would I modify the DLookup expression?

Thanks.

"Ken Snell (MVP)" wrote:

> May I recommend that you create a table (name it tblDiscounts) that you can
> use to store the various scenarios? This would be its structure:
>
> DiscountID (primary key -- autonumber)
> DiscountAmt
> PeopleNumber
> MaxIncome
>
>
> Populate this table with your data.
>
> Then, create a query based on this table. Name it qryDiscounts:
>
> SELECT DiscountAmt, PeopleNumber, MaxIncome
> FROM tblDiscounts
> ORDER BY PeopleNumber, MaxIncome;
>
>
> Then, you can use a DLookup expression for the ControlSource of a textbox on
> the form (no module needed):
>
> =DLookup("DiscountAmt", "qryDiscounts", "PeopleNumber = " &
> [NumberofPeopleControlOnForm] & " And " & [IncomeControlOnForm] &
> "<[MaxIncome]")
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
>
> "rpbsr" <rpbsr(a)discussions.microsoft.com> wrote in message
> news:BD15E0A5-073F-441F-94C7-AD1C6B130A28(a)microsoft.com...
> > Hi Ken,
> >
> > I need to assign discounts based on the number of people in the household
> > and
> > the income. For example:
> > 80% if 8 people and <$78K
> > 80% if 7 people and <$73K
> > ...80% if 1 person and <$41K
> >
> > 50% if 8 people and <$49K
> > 50% if 7 people and <$46K
> > ...
> >
> > 30% if 8 people and <$29K
> > ...
> >
> > The VBA module suggested was:
> >
> > For sake of discussion I assume your table
> > is tblHouseholds, with fields HouseholdID, NumberOfPeople, Income, and
> > all three fields required.
> >
> > select CalcDiscount(NumberOfPeople, Income)
> > from tblHouseholds
> >
> > Then in a standard module write:
> > Public Function CalcDiscount(ByVal NumberOfPeople As Integer, ByVal
> > Income As Currency) As Single
> > Dim sngPercent As Single
> > sngPercent = 0
> > Select Case NumberOfPeople
> > Case 1
> > Select Case Income
> > Case 0 To 10000
> > sngPercent = 0.1 '0.1 = 10%
> > Case 10000 To 25000
> > sngPercent = 0.2
> > 'etc.
> > End Select
> > Case 2
> > Select Case Income
> > Case 0 To 11000
> > sngPercent = 0.11
> > Case 11000 To 26000
> > sngPercent = 0.22
> > 'etc.
> > End Select
> > 'etc.
> > End Select
> > CalcDiscount = sngPercent
> > End Function
> >
> >
> > "Ken Snell (MVP)" wrote:
> >
> >> If all you want is to fill in a value, you probably don't need a module.
> >> Just use a textbox on the form to hold the "discount" value, and use an
> >> expression as the Control Source of that textbox, where the expression
> >> returns the discount amount.
> >>
> >> You'll need to give us many more details before we can provide more
> >> specific
> >> recommendations.
> >>
> >> --
> >>
> >> Ken Snell
> >> <MS ACCESS MVP>
> >>
> >>
> >> "rpbsr" <rpbsr(a)discussions.microsoft.com> wrote in message
> >> news:543A238F-1C75-41F2-8307-855B7B9B3F6A(a)microsoft.com...
> >> > Someone here was kind enough to suggest a module for an application I'm
> >> > working on involving the calculation of a discount based on household
> >> > income
> >> > and the number of people. I want to develop a form such that the
> >> > discount
> >> > field is filled in when they enter the household income and number in
> >> > the
> >> > household. How do I use the module with the form?
> >> >
> >> > Thanks for your help.
> >>
> >>
> >>
>
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Need help for Append Only feature in Access 2007
Next: Exclude item from a Report