SQL with Between phrase with calculated values


I am trying to do the following SQL statement for a report form. Basically I am trying to choose a bunch of Index table records that _almost_ match records in my RptIndex table on a tolerance of +/-.1. Eventually I want to replace .1 with a variable from a form that will prompt at runtime. But Access doesnt seem to like the following. It tells me that the AND is missing.

SELECT Index.Index1 FROM (RptIndex INNER JOIN [Index] ON ( Index.Index1 Between (RptIndex.Index1-.1) AND (RptIndex.Index1+.1) ));

Both Index & RptIndex have fields called Index1.
regorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

shanesuebsahakarnCommented:
Try this:
SELECT Index.Index1 FROM RptIndex INNER JOIN Index ON Index.Index1>(RptIndex.Index1-.1) AND Index.Index1<(RptIndex.Index1+.1)

Alternatively, you just need to alter the bracketing slightly:
SELECT Index.Index1 FROM RptIndex INNER JOIN Index ON (Index.Index1 Between (RptIndex.Index1-.1) AND (RptIndex.Index1+.1))

Otherwise, Access thinks that the part after the AND is another JOIN criteria. Both of the above work ok in my tests.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Arthur_WoodCommented:
I doubt that it is possible to JOIN like that.  A JOIN requires the values on each side of the JOIN to MATCH EXACTLY.

But rather than using JOIN, use WHERE:

SELECT Index.Index1 FROM RptIndex, [Index] where Index.Index1 BETWEEN RptIndex.Index1-1 and rptIndex.Index1+1

That MIGHT work.

AW

0
shanesuebsahakarnCommented:
It's possible to perform that JOIN. Joins do not have to match exactly - you can use the Like operator to perform a join, for example.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Arthur_WoodCommented:
never tried it, and never hope to need to, but thanks for the information, anyway.

AW
0
regorAuthor Commented:
The first option worked. I guess my mind is not working subtley enough at this point but I dont see the difference between what I did and what you did in the second example you gave. Thanks much.
0
shanesuebsahakarnCommented:
regor, I didn't think there was a difference either but when I used the second option on my Access XP test database it seemed to work (without the brackets it gave me the same "AND is missing" error message. Maybe it's a quirk of Access.
0
regorAuthor Commented:
You just removed the brackets around [Index]?
0
regorAuthor Commented:
You just removed the brackets around [Index]?
0
shanesuebsahakarnCommented:
No, I removed the brackets around the whole FROM section, but I've just tried your original statement and it worked (I didn't try it before because I assumed from your description that it wouldn't work) - not sure why it didn't in your db though. I tried it on Access XP though, I don't know if that was significant.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.