Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2004
    Posts
    77

    Unanswered: calculation syntax error , pls can someone else have a look pls, Needhelpdesparate !

    Hi there

    I have this error popping up on a form I am working on. Attached is a copy of the db with the form

    Run-time error "3075'

    Syntax error(missing operator) in query expression '[Lowerrange]=<2000 AND [HiRange] >2000".

    I have added the missing operator but still it is not working. I need to be able to keep in any amount 2001 and get it to give me the right result.

    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.)


    Pls can someone help me figure out how to get this calculation to work or fix it for me. I have been trying to fix it for a long time. (wks now).

    really appreciate who can help to get it to work. It is

    Many Thanks in advance
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    The proper syntax should be:

    [Lowerrange]<=2000

    (<=, not =<)

  3. #3
    Join Date
    Apr 2004
    Posts
    77

    calculation syntax error, pls can someone else

    Hi Matthew,

    Many Thanks for ur reply . I would make this correct.

    Thank u.

  4. #4
    Join Date
    Apr 2004
    Posts
    77

    Type mismatch error syntax!

    Hi there

    I have made the correct as suggested but I now get a:

    typemismatch error message:

    When I try to debug it highlights line 3 below:

    dblcommrate=dlookup("[ComAmount]", "tblrange", "[lowrange] < " & curAmount And "[highrange] > ")

    I have tried to debug it looking at each object on the line but it still gives me the error so I thought I maybe if I type the table that line is looking up below someone will spot my mistake I can't find....

    lowrange highrang amount
    >10 <=50 8
    >50 <=100 10
    >101 <=200 14
    >201 <=350 17
    >351 <=500 20
    >501 <=650 25
    >651 <=800 30
    >801 <=1000 35
    >1001 <=1500 40
    >1501 <=1750 45
    >1751 <=1999 50
    <=2000 >2000 0.

    I think maybe it is still my table. I can't seem to know exactly what.

    Pls any help would be much appreciated.

    Many Thanks.

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    "[lowrange] < " & curAmount & " AND [highrange] > " & curAmount
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Apr 2004
    Posts
    77

    "data type mismatch in crietria expression". pls desparate help

    Hi r123456

    Thanks for ur reply to my problem. I made the correction to the expression but I still get the "dblCommRate = Dlookup........ "line giving the error below


    "data type mismatch in crietria expression".

    I am thinking can one not match number and text data type in an expression.
    cause lowrange and highrange are "text" and comAmount is "number"

    I have tried to figure it out.

    Any help pls would be appreciated?

    ManyThanks in advance

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You would be thinking correctly...

    why is lowrange and highrange a text datatype?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Apr 2004
    Posts
    77

    calculation syntax error , pls can someone else have a look pls, Needhelpdesparate !

    Thanks Teddy, for ur reply.

    The lowrange and high range are text bcos of the >= and <= signs used to difference the ranges in the table.

    I mean I do not know any other way to show the ranges of the money.?

    <10 >=50 etc


    Many Thanks.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Uh... that doesn't make any sense...

    You have two fields, lowrange, and highrange. Why does there need to be text in either field?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Dec 2003
    Posts
    268

    Dlookup is not the best bet

    With what I have seen your best bet is to probably translate the information in the table via an upate query and a couple of small functions.

    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

    This should fix the problem

    This just goes to show how important it is to do the modeling of your DB the correct way and to not try to make the incorrect data types do something they aren't supposed to do. I went through this same type of thing trying to make a delta work in a DB.

  11. #11
    Join Date
    Apr 2004
    Posts
    77
    Hi mjweyland

    Thanks for ur reply. I am very happy and unsure (bcos of the process) bcos I am just learning and so I am a bit on the novice especially with vba.

    so I might be asking some strange questions

    1 so I need create a update query ? using the range table (so do I turn the table into an update query?

    2 and this bit... You are probably going to need code to catch those records where the value is greater or equal to 2000 ......

    for now my code is scatterd so I would pls appreciate any help that I can get to put this together. for the greater or equal to 2000

    a step by step guide pls if possible might help more(I really appreciate this reply from u) bcos I have been stuck on it for a month ..... I am kind of lost on this one and it is the main thing holding completion of the db.

    Many Thanks in advance.

  12. #12
    Join Date
    Apr 2004
    Posts
    77

    Dlook is not the best bet..

    Hi mjweyland

    Thanks for ur reply. I am very happy and unsure (bcos of the process) bcos I am just learning and so I am a bit on the novice especially with vba.

    so I might be asking some strange questions

    1 so I need create a update query ? using the range table (so do I turn the table into an update query?

    2 and this bit... You are probably going to need code to catch those records where the value is greater or equal to 2000 ......

    for now my code is scatterd so I would pls appreciate any help that I can get to put this together. for the greater or equal to 2000

    a step by step guide pls if possible might help more(I really appreciate this reply from u) bcos I have been stuck on it for a month ..... I am kind of lost on this one and it is the main thing holding completion of the db.

    Many Thanks in advance.

Posting Permissions

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