Results 1 to 5 of 5
  1. #1
    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
    The add the following code

    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.

    Many Thanks in advance.
    Attached Files Attached Files

  2. #2
    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
    currently using SS 2008R2

  3. #3
    Join Date
    Apr 2004
    Posts
    77
    Hi izyrider


    Thanks for ur reply to my message.

  4. #4
    Join Date
    Dec 2003
    Posts
    268

    comparison

    Did the functions help you clean up your data for the comparison?

    I thought this stuff looked familiar.

  5. #5
    Join Date
    Apr 2004
    Posts
    77
    Hi Myweyland

    Thanks for ur reply.

    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
  •