By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 1,942 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

Question For Microsoft Access Data Base

P: 20
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
Expand|Select|Wrap|Line Numbers
  1. =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.
Expand|Select|Wrap|Line Numbers
  1. Function TieredPricing(LookupTable As Range, LookupValue As Double, _
  2. Optional IncludeBoundaryValue As Boolean = False)
  3. Dim Rslt As Variant
  4. On Error Resume Next
  5. Rslt = Application.WorksheetFunction.Match( _
  6. LookupValue, LookupTable.Columns(1), 1)
  7. If Not IsEmpty(Rslt) Then
  8. If IncludeBoundaryValue _
  9. And LookupTable.Cells(Rslt, 1).Value = LookupValue Then
  10. If Rslt = 1 Then _
  11. TieredPricing = _
  12. "Look up value outside of table values" _
  13. Else _
  14. TieredPricing _
  15. = LookupTable.Cells(Rslt - 1, 2).Value
  16. Else
  17. TieredPricing = _
  18. LookupTable.Cells(Rslt, 2).Value
  19. End If
  20. Else
  21. TieredPricing = "Look up value outside of table values"
  22. End If
  23. End Function
  24.  
None of this is working Please HELP

Joel
Jan 3 '10 #1

✓ answered by nico5038

I would create a function like:

Expand|Select|Wrap|Line Numbers
  1. Function fncMultiplier(lngI As Long) As Long
  2.  
  3. fncMultiplier = 1
  4. While lngI > 500
  5.       fncMultiplier = fncMultiplier + 1
  6.       lngI = lngI - 500
  7. Wend
  8.  
  9. End Function
  10.  
Place this code in a module.

Now in the form field use:
Expand|Select|Wrap|Line Numbers
  1. =[B1] * fncMultiplier ([A1])
  2.  
Success,

Nic;o)

Share this Question
Share on Google+
24 Replies


RuralGuy
Expert 100+
P: 375
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.
Jan 3 '10 #2

P: 20
Thanks For your responce. What you are telling me. I Cant seam to get the Public Funtion to cross tables
Jan 3 '10 #3

RuralGuy
Expert 100+
P: 375
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.
Jan 3 '10 #4

P: 20
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
Expand|Select|Wrap|Line Numbers
  1.  If "" & Me!Consideration < 499 Then
  2.  Me!ConsiderationCal = 1
  3.  Else
  4.  If "" & Me!Consideration < 1000 Then
  5.  Me!ConsiderationCal = 2
  6. Else
  7.  If "" & Me!Consideration < 1499 Then
  8.  Me!ConsiderationCal = 3
  9. Endif
  10. Endif
  11. Endif
after I got to 250000.00 The Computer said I could not enter more
Jan 3 '10 #5

P: 20
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
Jan 3 '10 #6

nico5038
Expert 2.5K+
P: 3,072
I would create a function like:

Expand|Select|Wrap|Line Numbers
  1. Function fncMultiplier(lngI As Long) As Long
  2.  
  3. fncMultiplier = 1
  4. While lngI > 500
  5.       fncMultiplier = fncMultiplier + 1
  6.       lngI = lngI - 500
  7. Wend
  8.  
  9. End Function
  10.  
Place this code in a module.

Now in the form field use:
Expand|Select|Wrap|Line Numbers
  1. =[B1] * fncMultiplier ([A1])
  2.  
Success,

Nic;o)
Jan 3 '10 #7

P: 20
I am sorry, The Funtion In the Public Code???, I have never done that before
Jan 3 '10 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Joel

Try the following function and see if it gives you the results you want.
Expand|Select|Wrap|Line Numbers
  1. Dim totalSum as Long 'the represents the total value (10,000 in your example)
  2. Dim i as Integer
  3. Dim count as Integer
  4. Dim rslt as Double
  5.  
  6.     For i=0 to totalSum Step 500
  7.         count = count +1
  8.     next i
  9.  
  10.     rslt = B1 * count ' should give you your result
Jan 3 '10 #9

P: 20
nico5038

That still seams to give off the wrong answer for 500, 1000, 1500 etc
Jan 3 '10 #10

P: 20
Thanks msquared
I will try that next
Jan 3 '10 #11

P: 20
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
Jan 3 '10 #12

P: 20
nico5038
Thats it. Thanks, Three Days I have been wooking on this
Jan 3 '10 #13

P: 20
nico5038

how do i get it not to calulate o.oo
Jan 3 '10 #14

P: 20
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
Jan 4 '10 #15

nico5038
Expert 2.5K+
P: 3,072
Use this code:

Expand|Select|Wrap|Line Numbers
  1. Function fncMultiplier(ByVal lngI As Long) As Long
  2.  
  3. If lngI = 0 Then
  4.    fncMultiplier = 0
  5.    Exit Function
  6. End If
  7.  
  8. fncMultiplier = 1
  9. While lngI > 500
  10.       fncMultiplier = fncMultiplier + 1
  11.       lngI = lngI - 500
  12. Wend
  13.  
  14. End Function
  15.  
And in the formfield use:
Expand|Select|Wrap|Line Numbers
  1. =B1 * fncMultiplier (NZ(A1))
  2.  
The added NZ() function will take care of Null (empty) fields.

Nic;o)
Jan 4 '10 #16

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

=fncMultiplier(NZ([Consideration]))*(NZ([StateTransTax]+[CountyTransTax]))
Jan 4 '10 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
Glad you guys got it sorted :)
Jan 4 '10 #18

nico5038
Expert 2.5K+
P: 3,072
I would expect to see:
Expand|Select|Wrap|Line Numbers
  1. =fncMultiplier(NZ([Consideration])) * (NZ([StateTransTax])+NZ([CountyTransTax]))
  2.  
Being the multiplication factor derived from the "total amount" and multiplied by the (summed) tax.

Nic;o)
Jan 4 '10 #19

P: 20
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.
Jan 4 '10 #20

P: 20
Is there a way to stop getting this from stop returning the value of 11.00 when 0 is entered
Expand|Select|Wrap|Line Numbers
  1. If "" & Me!Number_of_Pages_in_Doc_1 < 0 > "" Then
  2. Me!Total_Costs_1 = Me!Number_of_Pages_in_Doc_1 * 3 + 11 
Jan 5 '10 #21

MMcCarthy
Expert Mod 10K+
P: 14,534
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 ...
Expand|Select|Wrap|Line Numbers
  1.     If Me!Number_of_Pages_in_Doc_1 <> 0 _
  2.         Or NZ(Me!Number_of_Pages_in_Doc_1,"") <> "" Then
  3.  
  4.         Me!Total_Costs_1 = Me!Number_of_Pages_in_Doc_1 * 3 + 11 
  5.     Else
  6.         Me!Total_Costs_1=0
  7.     End If
  8.  
Jan 5 '10 #22

P: 20
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.
Jan 5 '10 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jan 5 '10 #24

P: 20
thanks for everything guys
Jan 5 '10 #25

Post your reply

Sign in to post your reply or Sign up for a free account.