Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

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.
0
regor
Asked:
regor
  • 4
  • 3
  • 2
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now