By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,484 Members | 1,803 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,484 IT Pros & Developers. It's quick & easy.

SQL Syntax: IIF(Between (select x from a) and (select y from a),"yes","no")

P: 22
I'm new to MS Access SQL and I'm trying to compare data to a table of multiple ranges in order to return yes/no results. I.e.: If this number is between X and Y, do this, else do this. My problem is that there are multiple ranges to be examined. These ranges are stored in a table (let's call it Range), and are all 3-digit numbers:
Expand|Select|Wrap|Line Numbers
  1. Low High 
  2. 007 015  
  3. 058 077
  4. 500 599
  5. 750 799
  6.  
I'm trying to check if my number exists between any of these ranges in order to exclude them from further analysis. Here's what I'm trying, that isn't working:
Expand|Select|Wrap|Line Numbers
  1. IIF([Number] BETWEEN (select Low from Range) AND (select High from Range),"","analyze this record") AS NumberCheck,
  2.  
Once I have the range check, I want to embed two other IIF statements in it, effectively excluding numbers that fall in range from further analysis.

I'm doing it this way because I'm doing a series of these types of checks in order to return a table showing results for five or six different checks.

Is this just a matter of proper syntax for doing between and select?
Nov 2 '11 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,709
  1. Is this being done within a Query? If so then please post your existing SQL.
  2. Are the values in the table actually strings of 3 digits each or numbers simply formatted as strings?
  3. What is [Number]? Where is it defined? How is it defined?

I'm sure we can provide a meaningful answer with the full question (It's asked well, but there a re just a few important details still required).
Nov 2 '11 #2

P: 22
NeoPa: Thank you for your helpful guidance.

1. Yes, this is being done with a Query - SQL is below.

2. The values for the number being assessed are all three digits, and are being brought into the query via Linked Table.

3. [Number] in my first example is actually called [Qcv.Sec], below. It is a section number used to distinguish a sub-set of [Qcv.Course]. Qcv.Sec is pre-defined within about 15 different non-consecutive ranges of varying size. There are 'blank' spots between some ranges (reserved for future use).

Lines 7 and 9 begin by checking whether or not each record's Section is within one of the ranges contained in the lookup table named "Roomless." My goal is to exclude all records with a Section contained in the Roomless lookup table from being assessed under SlotStartCheck and SlotEndCheck. (because if the course section is one without a room, I don't care if the start and end times are standard).

Expand|Select|Wrap|Line Numbers
  1. SELECT Qcv.Title, Qcv.Course, Qcv.Sec, Qcv.Location, Qcv.Room, Qcv.Day, Qcv.Start, Qcv.End, Qcv.Start1, Qcv.End1, 
  2.  
  3. IIf([Start1] In (select StandardDate from Dates),"","Check Start Date") AS StartDateCheck, 
  4.  
  5. IIf([End1] In (select StandardDate from Dates),"","Check End Date") AS EndDateCheck, 
  6.  
  7. IIF((Qcv.Sec) Between [Roomless].[Low] And [Roomless].[High],"",IIf(RIGHT([Course], 1)="L","",IIf([Location] In (select OffcLoc from Roomless),"",IIf([Start] In (select SlotStart from Slots),"","Check Slot Start Time")))) AS SlotStartCheck, 
  8.  
  9. IIF((Qcv.Sec) Between [Roomless].[Low] And [Roomless].[High],"",IIf(RIGHT([Course], 1)="L","",IIf([Location] In (select OffcLoc from Roomless),"",IIf([End] In (select SlotEnd from Slots),"","Check Slot End Time")))) AS SlotEndCheck, 
  10.  
  11. IIf([Location]="MAIN",IIf([Room] Is Null,"Loc. MAIN No Room",""),"") AS LocationCheck, IIf([StartDateCheck]="",IIf([EndDateCheck]="",IIf([SlotStartCheck]="",IIf([SlotEndCheck]="",IIf([LocationCheck]="","PASSED ALL CHECKS",""),""),""),""),"") AS CheckAll
  12.  
  13. FROM Qcv;
and the Roomless lookup table is:
Expand|Select|Wrap|Line Numbers
  1. id    Low    High
  2. 1     70     99
  3. 3     200    200
  4. 6     245    249
  5. 13    250    259
  6. 5     260    289
  7. 12    300    319
  8. 2     470    475
  9. 9     695    700
  10. 7     710    715
  11. 8     716    716
  12. 11    720    729
  13. 10    750    759
  14. 14    760    764
  15. 4     765    766
My desired report is one that assesses each record for a number of criteria (StartDateCheck, EndDateCheck, SlotStartCheck, SlotEndCheck), spitting out error messages for each criteria not being met.

Thanks!
Nov 3 '11 #3

ADezii
Expert 5K+
P: 8,679
I'm a little confused as to what exactly you are trying to accomplish, but I'll attempt to get the Process started. I created some Test Data in Table Qcv as illustrated below. I then created a Query that will test every Value in the [Sec] Field of Qcv against all the Ranges listed in the Roomless Table. If a Value in the [Sec] Field did not exist in any of the Ranges in the Roomless Table, an Analyze Record is returned. All the relevant info is posted below, if I am way off track just let me know.
  1. Qcv Table:
    Expand|Select|Wrap|Line Numbers
    1. SecID    Sec
    2. 1        3456
    3. 2          35
    4. 3         501
    5. 4    
    6. 5           8
    7. 6         772
    8. 7          96
    9. 8         547
    10. 9           1
    11. 10         63
    12. 11         99
    13. 12        112
    14. 13        750
    15. 15         63
    16. 16      55678
    17. 17         77
    18. 18         58
    19. 19        762
    20. 20        312
    21. 21        200
    22. 22        201
    23.  
  2. SQL Statement:
    Expand|Select|Wrap|Line Numbers
    1. SELECT Qcv.SecID, Qcv.Sec, fAnalyzeDataNotInRange([Sec]) AS NumberCheck
    2. FROM Qcv;
  3. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fAnalyzeDataNotInRange(varNumber As Variant) As Variant
    2. Dim MyDB As DAO.Database
    3. Dim rst As DAO.Recordset
    4. Dim blnNotInRange As Boolean
    5.  
    6. If IsNull(varNumber) Then
    7.   fAnalyzeDataNotInRange = Null
    8.     Exit Function
    9. End If
    10.  
    11. Set MyDB = CurrentDb
    12. Set rst = MyDB.OpenRecordset("Roomless", dbOpenForwardOnly)
    13.  
    14. With rst
    15.   Do While Not .EOF
    16.     'Number in Range
    17.     If varNumber >= ![Low] And varNumber <= ![High] Then
    18.       blnNotInRange = False
    19.         Exit Do
    20.     Else        'Number NOT in Range
    21.       blnNotInRange = True
    22.     End If
    23.       .MoveNext
    24.   Loop
    25. End With
    26.  
    27. fAnalyzeDataNotInRange = IIf(blnNotInRange, "Analyze Record", Null)
    28.  
    29. rst.Close
    30. Set rst = Nothing
    31. End Function
  4. OUTPUT (including ALL Records):
    Expand|Select|Wrap|Line Numbers
    1. SecID    Sec    NumberCheck
    2. 1       3456    Analyze Record
    3. 2         35    Analyze Record
    4. 3        501    Analyze Record
    5. 4        
    6. 5          8    Analyze Record
    7. 6        772    Analyze Record
    8. 7         96    
    9. 8        547    Analyze Record
    10. 9          1    Analyze Record
    11. 10        63    Analyze Record
    12. 11        99    
    13. 12       112    Analyze Record
    14. 13       750    
    15. 15        63    Analyze Record
    16. 16     55678    Analyze Record
    17. 17        77    
    18. 18        58    Analyze Record
    19. 19       762    
    20. 20       312    
    21. 21       200    
    22. 22       201    Analyze Record
    23.  
Nov 3 '11 #4

NeoPa
Expert Mod 15k+
P: 31,709
Joel, that's a good attempt at an answer, but I fear you must not have understood question #2 very well. It's a question that gives two options to choose from as an answer. You have provided an answer which doesn't make clear which of the two possible options is correct, but essentially just repeated the statement from your question (which was such that the question was necessary you see).

Let me try to make it clear why such a question is necessary. You post that your [Range] table contains data such as :
Expand|Select|Wrap|Line Numbers
  1. Low High
  2. 007 015  
  3. 058 077
  4. 500 599
  5. 750 799
This could reflect either of the two following tables :
Table = [Range]
Expand|Select|Wrap|Line Numbers
  1. Field  Type     Format
  2. Low    Numeric  '000'
  3. High   Numeric  '000'
Table = [Range]
Expand|Select|Wrap|Line Numbers
  1. Field  Type     Length
  2. Low    String   3
  3. High   String   3
It's Europa League night so I've not much time now, but if you can provide a valid answer for Q2 then I can look at it later for you.
Nov 3 '11 #5

P: 22
Ah, I see. Sorry for the confusion. Yes, it is option A) Numeric field, format '000' - and all of the numbers being evaluated are three digits, as are all of the Low/High values in the Roomless table.

ADezii: Here's a little more info - The (relevant section of the) table looks like:

Expand|Select|Wrap|Line Numbers
  1. Title                   Course    Sec  Start   End  Location  Start1    End1      Status
  2. Thermodynam&kinetics  CHEM-2102L  073  14:00  17:15   MAIN   9/8/2011  12/20/2011  A
  3. Thermodynam&kinetics  CHEM-2102L  074  14:30  17:15   MAIN   9/7/2011  12/18/2011  A
  4. Organic Chemistry I   CHEM-2202   001   9:00  10:20   MAIN   9/7/2011  12/20/2011  A
  5. Organic Chemistry I   CHEM-2202   002  10:30  11:30   MAIN   9/7/2011  12/20/2011  A
And I want my report to look like (columns Start through Status omitted for clarity here, but I want them in the final report):

Expand|Select|Wrap|Line Numbers
  1. Title                   Course    Sec  StartDateCheck  EndDateCheck  SlotStartCheck  SlotEndCheck
  2. Thermodynam&kinetics  CHEM-2102L  073  Check Start Date    
  3. Thermodynam&kinetics  CHEM-2102L  074                  Check End Date
  4. Organic Chemistry I   CHEM-2202   001                                Check Slot Start Time
  5. Organic Chemistry I   CHEM-2202   002                                                Check Slot End Time
In short: sections 070 though 099 are on the "Roomless" list, and don't need to be checked for slot start and end times (and I've already got the code working on checking start and end dates).
Nov 3 '11 #6

NeoPa
Expert Mod 15k+
P: 31,709
It appears that [Range] is actually [Roomless]. I'm not sure why the ambiguity was introduced, but I suggest we forget it ever was, along with [Range], and use [Roomless] going forward instead.

If you need to check each record of [QCV] against all these [Roomless] records, then your best bet (I would say) would be to use an unjoined pairing of the two tables (in the FROM clause - creates Cartesian Product - See SQL JOINs) then GROUP the results on the data of your [QVC] record.

If not all [Roomless] records are required then the WHERE clause can specify which to include.

[Roomless] field data would then need to be aggregated wherever it occurs (EG. In SELECT clause) where a result is required. When finding if a record of [QVC]matches any record of [Roomless] then the Min() aggregate function can be used of the boolean value.

I will try to provide some example SQL, but I find when I try to look at something where I need precise details your example SQL contradicts the understanding I get from your question explanation. It appears that [Roomless] has another field you require beyond the three included in your explanation. That rather throws the whole concept of the solution back to the drawing-board I'm afraid, but it's too late into the process for me to start again from scratch at this point. Hopefully what I've said already will trigger some ideas. It should certainly have convinced you how disasterous it can be to post your question without accurate and reliable detail in future.

Anyway, see what you can see from the following example :

Expand|Select|Wrap|Line Numbers
  1. SELECT   tQ.Title
  2.        , tQ.Sec
  3.        , tQ.[...]
  4.        , IIf(Min(tQ.Sec Between tR.Low And tR.High), 'Found Result', 'Not Found Result')
  5.        , ...
  6. FROM     [QVC] AS [tQ]
  7.        , [Roomless] AS [tR]
  8. GROUP BY tQ.Title
  9.        , tQ.Sec
  10.        , ...
Nov 7 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.