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. 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`
3. 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])
4. 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
5. 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?