Question For Microsoft Access Data Base

[joeldault]

Question For Microsoft Access Data Base
--------------------------------------------------------------------------------

I am Trying to create a single formula that would do the following:

If A1 is less then or equal to 499, deliver B1*1, if A1 is equal to or less than 999 but more than and including 500, deliver B1*2, If A1 is equal to or less than 1499 but more than and including 1000, deliver B1*3, AND SO ON.....

I have read the Posts on this and they don't Work

=Int(A1/500)*B1 Dont work, It gives the wrong values for 500, 1000, 1500, etc

+Int(A1/500+1)-(MOD(A1,500)=0 Dont work, I cant get Access to except this formula. It wont save. It changes back to whatever was in the Control Source.

I am not an expert at this and the third response from what I can tell looks like an answer for Excel and not Access, Well I cant figure it out. Creating the Table which is the first part makes no since on how to do it.

That post was as follows:
[This followup was posted to microsoft.publi c.excel.workshe et.functions
with an email copy to =?Utf-8?B?QW1hbg==?=.
Please use the newsgroup for further discussion.]

Three possibilities, in addition to the option already discussed by
others, are below. None of the options requires the thresholds to be
equally spaced, nor that the return values be 1,2,3, etc.

1) Use the formula =B1*(IF(A1<=0,0 ,IF(A1<=500,1,I F(A1<=1000,2,3) )))
This might be the easiest to understand but it has two limitations.
First, IF statements can be nested only 7 deep. There are ways around
the limitation, but they just complicate the matter further. Second,
the thresholds and the returned values are hidden in the formula and
not immediately obvious from looking at the worksheet.

2) Create a table such as:
F G
2 0 1
3 500 2
4 1000 3

And, use the formula

=B1*INDEX(G2:G4,MATCH(A1,F2:F4,1)-(A1=INDEX(F2:F4,MATCH(A1,F2:F4,1))))

The 2nd part -- following the -( part -- is an adjustment for the fact
that you want the conditions to be 'less than or equal to'. The
advantage of this formula is that it is easy to adjust for more
conditions. The downside is that it might be somewhat intimidating to
a novice.

3) Use the VBA user defined function (UDF) given below as
=TieredPricing( F2:G4,A1,TRUE). The advantage is that it makes the
worksheet more 'self documented' and it further separates the code from
the data. The downside is the need for a VBA module and having to deal
with the accompanying 'This file contains macros' warning.

Function TieredPricing(LookupTable As Range, LookupValue As Double, _
Optional IncludeBoundaryValue As Boolean = False)
Dim Rslt As Variant
On Error Resume Next
Rslt = Application.WorksheetFunction.Match( _
LookupValue, LookupTable.Columns(1), 1)
If Not IsEmpty(Rslt) Then
If IncludeBoundaryValue _
And LookupTable.Cells(Rslt, 1).Value = LookupValue Then
If Rslt = 1 Then _
TieredPricing = _
"Look up value outside of table values" _
Else _
TieredPricing _
= LookupTable.Cells(Rslt - 1, 2).Value
Else
TieredPricing = _
LookupTable.Cells(Rslt, 2).Value
End If
Else
TieredPricing = "Look up value outside of table values"
End If
End Function
 

None of this is working Please HELP

Joel

[joeldault]

Is there a way to stop getting this from stop returning the value of 11.00 when 0 is entered

If "" & Me!Number_of_Pages_in_Doc_1 < 0 > "" Then
Me!Total_Costs_1 = Me!Number_of_Pages_in_Doc_1 * 3 + 11 

[MMcCarthy]

Hi Joe

Don't forget to put a forward slash (/) before the word code on the closing code tag.

In answer to your questions, try this out ...

    If Me!Number_of_Pages_in_Doc_1 <> 0 _
        Or NZ(Me!Number_of_Pages_in_Doc_1,"") <> "" Then
 
        Me!Total_Costs_1 = Me!Number_of_Pages_in_Doc_1 * 3 + 11 
    Else
        Me!Total_Costs_1=0
    End If
 

[joeldault]

Thanks to all of you, I have been having a heck of a time on these problems, You guys have made the problem seam easy, That worked.

[MMcCarthy]

Nico and I have a few years doing this between us. You pick up a few things along the way :D One day you'll be the guru helping others.

[joeldault]

thanks for everything guys