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.public.excel.worksheet.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,IF(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

[RuralGuy]

I would tend to create a Public Function in a standard module using a Select Case structure but you would have to finish the "AND SO ON" before code could be developed. It would be easier to understand and maintain later.

[joeldault]

Thanks For your responce. What you are telling me. I Cant seam to get the Public Funtion to cross tables

[RuralGuy]

If you are using this function in a form or query, I see no reason to cross tables. Are A and B in different tables? Do you have them joined in a query?
The Function I had in mind would take the two values and return one.

[joeldault]

This is what I am going.
I have a From That I am trying to calulate State and County transfer tax. The County Charges 3.75 Cents per 500 and The State Charges 1.10 per 500. The From I am Creating is addiing the Costs. If I record a Mortgage it is 14.00 for the First page and 3.00 for each additional Page. The Form I have been able to put different kinds of docs in and get it to some the total on the form, but when I put in that a house dols for lets say 10,000.00. It need to give me the total of $8.60. If there was a way to send you the form I would.
I had a public funtion working but it got to big and the program said i could not add more
This is what I had

 If "" & Me!Consideration < 499 Then
 Me!ConsiderationCal = 1
 Else
 If "" & Me!Consideration < 1000 Then
 Me!ConsiderationCal = 2
Else
 If "" & Me!Consideration < 1499 Then
 Me!ConsiderationCal = 3
Endif
Endif
Endif

after I got to 250000.00 The Computer said I could not enter more

[joeldault]

Sorry Thanks for at least looking. I have been working on this for three days. all day and night. I cant get it to work

[nico5038]

I would create a function like:

Function fncMultiplier(lngI As Long) As Long
 
fncMultiplier = 1
While lngI > 500
      fncMultiplier = fncMultiplier + 1
      lngI = lngI - 500
Wend
 
End Function
 

Place this code in a module.

Now in the form field use:

=[B1] * fncMultiplier ([A1])
 

Success,

Nic;o)

[joeldault]

I am sorry, The Funtion In the Public Code???, I have never done that before

[MMcCarthy]

Hi Joel

Try the following function and see if it gives you the results you want.

Dim totalSum as Long 'the represents the total value (10,000 in your example)
Dim i as Integer
Dim count as Integer
Dim rslt as Double
 
    For i=0 to totalSum Step 500
        count = count +1
    next i
 
    rslt = B1 * count ' should give you your result

[joeldault]

nico5038

That still seams to give off the wrong answer for 500, 1000, 1500 etc

[joeldault]

Thanks msquared
I will try that next

[joeldault]

nico5038
I am sorry, My Thinking is wrong. I am not even sure at thhis moment if that is right or not. Sorry I need to think

[joeldault]

nico5038
Thats it. Thanks, Three Days I have been wooking on this

[joeldault]

nico5038

how do i get it not to calulate o.oo

[joeldault]

Function fncMultiplier(lngI As Long) As Long

fncMultiplier = 1
While lngI > 500
fncMultiplier = fncMultiplier + 1
lngI = lngI - 500
Wend

End Function

This Works great expect for $0.00 gives a Errior. Is there a way to fix this

[nico5038]

Use this code:

Function fncMultiplier(ByVal lngI As Long) As Long
 
If lngI = 0 Then
   fncMultiplier = 0
   Exit Function
End If
 
fncMultiplier = 1
While lngI > 500
      fncMultiplier = fncMultiplier + 1
      lngI = lngI - 500
Wend
 
End Function
 

And in the formfield use:

=B1 * fncMultiplier (NZ(A1))
 

The added NZ() function will take care of Null (empty) fields.

Nic;o)

[joeldault]

This is what I have. It works, There is still a value for null and 00.00

=fncMultiplier(NZ([Consideration]))*(NZ([StateTransTax]+[CountyTransTax]))

[MMcCarthy]

Glad you guys got it sorted :)

[nico5038]

I would expect to see:

=fncMultiplier(NZ([Consideration])) * (NZ([StateTransTax])+NZ([CountyTransTax]))
 

Being the multiplication factor derived from the "total amount" and multiplied by the (summed) tax.

Nic;o)

[joeldault]

nico5038
Thanks your first one works fine, The problem I think is that the feilds for state tax and County tax have a default vaule in them and that is why there is a number showings for 0.00. I have to Find a way to multiply the state transfer tax of .55 per five hundred and 1.10 per five hundred in that statement that will work. Thanks for the responce.

[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