# Thread: calculation nightmare.Pls someone help me out!!

1. Registered User
Join Date
Apr 2004
Posts
77

## Unanswered: calculation nightmare.Pls someone help me out!!

Hi

Can someone pls help me out with this calculation I can’t get it to work.

I thought this would be the easiest part of the creating the Db…(bcos I though a select statement would do it) ..but it has been the hardest and yet I can’t get it to work.
It sees it is a VB thing which I have very little knowledge of for now..

I now see this is one of those things that require some experience and would be glad if someone out there is kind enough to help out.

I have attached a Db of what I have done so far.

The suggestion from mjweyland Is what I have further tried but the :
UPDATE tblRange SET LowRange = getLowRange([lowRange]), HighRange = getHighRange([HighRange]);

Is jumping out. Not sure if that is where to but the update bit…..
Create a new module

Public Function getLowRange(LowRange As String) As Integer
'This is based on the following logic
'if the length of lowrange <6 then use the right (length of lowrange -1) number of characters
'If the length of lowrange >=6 then use the right 4 characters of lowrange

If Len(LowRange) < 6 Then
getLowRange = CInt(Right(LowRange, Len(LowRange) - 1))
Else
getLowRange = CInt(Right(LowRange, 4))
End If
End Function

Public Function getHighRange(HighRange As String) As Integer
'This is based on the following logic
'If the second character of HighRange is numeric then take the right (length of HighRange -2) number of characters
'If the second character of HighRange is NOT numeric then take the right(length of HighRange -1) number of charcters

Dim i As Integer
i = 2
If IsNumeric(Mid(HighRange, 2, 1)) Then i = 1
getHighRange = CInt(Right(HighRange, Len(HighRange) - i))
End Function

Then you can apply this to your Update query:
UPDATE tblRange SET LowRange = getLowRange([lowRange]), HighRange = getHighRange([HighRange]);

You are probably going to need code to catch those records where the value is greater or equal to 2000

Then you can do an if statement to return the CommAmt using your DLookUp command

if CurAmount >2000 then
dblCommRate = 0
else
dblCommRate = dlookup("[CommAmount]", "tblRange","[lowrange] < " & curAmount & " AND [highrange] > " & curAmount
end if

a recap of what I am trying to do pls:
This is how the cal should work on the form....
I have the ff fields to work as :

I have for (A) amtinpounds , (B) exchangerate, (C) amtinNai . [A*B=C] :-1 then

a comm is charge on the (A) amtinST (using the CommRate and ComAmt)

and then a totalamountl (E) is calculated as (A+the commAmt).

however I key in the CommAmt into a text field called commission and if amount is greater than 2000 (>2000 *0.3.)

I would be grateful if someone can pls help me/ work with me to get this cal to work.

I would be very very very grateful for this help ....I believe somone out there has the experience... this is my first access database.. I am just trying to learn pls.

2. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
looks like your query is trying to stick the literal string
"getLowRange([lowRange])"
into a numeric hole

you need to concatenate in the value returned by your function.

strSQL = "UPDATE tblRange SET LowRange = " & getLowRange([lowRange]) & ", HighRange = " & getHighRange([HighRange]) & ";"

izy

3. Registered User
Join Date
Apr 2004
Posts
77
Hi izyrider

Thanks for ur reply to my message.

4. Registered User
Join Date
Dec 2003
Posts
268

## comparison

I thought this stuff looked familiar.

5. Registered User
Join Date
Apr 2004
Posts
77
Hi Myweyland

I did not really understand how to apply the update and as a result I was getting errors .
Basically I do not understand how to tie all together not bcos of ur explanation but bcos my vba is really almost (scantly).... I am just learning that bit, so i need to go get a good vba book that also explains syntax very well... that is also my problem.

Basically the cal aint working

Any way thanks for ur help.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•