?
Solved

SQL with Between phrase with calculated values

Posted on 2003-03-27
9
Medium Priority
?
176 Views
Last Modified: 2012-05-04

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
Comment
Question by:regor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 1000 total points
ID: 8222319
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8222327
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8222373
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8222380
never tried it, and never hope to need to, but thanks for the information, anyway.

AW
0
 

Author Comment

by:regor
ID: 8222455
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8222476
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
 

Author Comment

by:regor
ID: 8222482
You just removed the brackets around [Index]?
0
 

Author Comment

by:regor
ID: 8222488
You just removed the brackets around [Index]?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8222510
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question