#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    5
    Rep Power
    0

    Between not working


    I am trying to take two columns and say if column A is between 0.01 and 0.05 cents more than column B then show me the results. This is what I have tried

    [allowed_amt]+(between 0.01 and 0.05)=[epd_amt]


    [allowed_amt]between +0.01 and +0.05=[epd_amt]

    The only thing that works is if I do it like this:

    [allowed_amt]+0.05=[epd_amt] this result only shows me allowed_amt that is plus 0.05 cents compared to epd_amt. It does not give those that are 0.01 0.02 0.03 or 0.04 which I need anything between the 2 amounts.
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,695
    Rep Power
    1958
    Looks like you are using MS Access?

    For my knowledge, the BETWEEN is more used to check the range of ONE column.

    I would use a simple calculation, something like this: (untested)
    Code:
    WHERE Abs(ColumnA-ColumnB) < 0.05
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    5
    Rep Power
    0

    this works


    so far this works but i need to do the same for the negative and that seems like so much typing. I then have to create a separate one for all those that <> that are +0.06 cents and then -0.06

    ([alloc_allw_amt]+0.01=[epd_calc_allw_amt]) Or ([alloc_allw_amt]+0.02=[epd_calc_allw_amt]) Or ([alloc_allw_amt]+0.03=[epd_calc_allw_amt]) Or ([alloc_allw_amt]+0.04=[epd_calc_allw_amt]) Or ([alloc_allw_amt]+0.05=[epd_calc_allw_amt])
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    5
    Rep Power
    0
    Originally Posted by MrFujin
    Looks like you are using MS Access?

    For my knowledge, the BETWEEN is more used to check the range of ONE column.

    I would use a simple calculation, something like this: (untested)
    Code:
    WHERE Abs(ColumnA-ColumnB) < 0.05
    Yes it is MS Access and I tried the ABS but it errors
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,244
    Rep Power
    4279
    Originally Posted by tmcrouse
    Yes it is MS Access and I tried the ABS but it errors
    o rly?

    as far as i remember, ABS() is a valid function in msaccess

    can we see the actual query, and the actual error message?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo