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

[Joel Marion]

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:

Low High 
007 015  
058 077
500 599
750 799

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:

IIF([Number] BETWEEN (select Low from Range) AND (select High from Range),"","analyze this record") AS NumberCheck,

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?

[NeoPa]

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).

[Joel Marion]

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).

SELECT Qcv.Title, Qcv.Course, Qcv.Sec, Qcv.Location, Qcv.Room, Qcv.Day, Qcv.Start, Qcv.End, Qcv.Start1, Qcv.End1, 

IIf([Start1] In (select StandardDate from Dates),"","Check Start Date") AS StartDateCheck, 

IIf([End1] In (select StandardDate from Dates),"","Check End Date") AS EndDateCheck, 

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, 

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, 

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

FROM Qcv;

and the Roomless lookup table is:

id	Low	High
1	 70	 99
3	 200	200
6	 245	249
13	250	259
5	 260	289
12	300	319
2	 470	475
9	 695	700
7	 710	715
8	 716	716
11	720	729
10	750	759
14	760	764
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!

[ADezii]

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:
   

   SecID	Sec
   1	    3456
   2	      35
   3	     501
   4	
   5	       8
   6	     772
   7	      96
   8	     547
   9	       1
   10	     63
   11	     99
   12	    112
   13	    750
   15	     63
   16	  55678
   17	     77
   18	     58
   19	    762
   20	    312
   21	    200
   22	    201

2. SQL Statement:
   

   SELECT Qcv.SecID, Qcv.Sec, fAnalyzeDataNotInRange([Sec]) AS NumberCheck
   FROM Qcv;

3. Function Definition:
   

   Public Function fAnalyzeDataNotInRange(varNumber As Variant) As Variant
   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim blnNotInRange As Boolean
   
   If IsNull(varNumber) Then
     fAnalyzeDataNotInRange = Null
       Exit Function
   End If
   
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset("Roomless", dbOpenForwardOnly)
   
   With rst
     Do While Not .EOF
       'Number in Range
       If varNumber >= ![Low] And varNumber <= ![High] Then
         blnNotInRange = False
           Exit Do
       Else        'Number NOT in Range
         blnNotInRange = True
       End If
         .MoveNext
     Loop
   End With
   
   fAnalyzeDataNotInRange = IIf(blnNotInRange, "Analyze Record", Null)
   
   rst.Close
   Set rst = Nothing
   End Function

4. OUTPUT (including ALL Records):
   

   SecID	Sec	NumberCheck
   1	   3456	Analyze Record
   2	     35	Analyze Record
   3	    501	Analyze Record
   4		
   5	      8	Analyze Record
   6	    772	Analyze Record
   7	     96	
   8	    547	Analyze Record
   9	      1	Analyze Record
   10	    63	Analyze Record
   11	    99	
   12	   112	Analyze Record
   13	   750	
   15	    63	Analyze Record
   16	 55678	Analyze Record
   17	    77	
   18	    58	Analyze Record
   19	   762	
   20	   312	
   21	   200	
   22	   201	Analyze Record

[NeoPa]

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 :

[U][B]Low High[/B][/U]
007 015  
058 077
500 599
750 799

This could reflect either of the two following tables :
Table = [Range]

[U][I]Field  Type     Format[/I][/U]
Low    Numeric  '000'
High   Numeric  '000'

Table = [Range]

[U][I]Field  Type     Length[/I][/U]
Low    String   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.

[Joel Marion]

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:

Title                   Course    Sec  Start   End  Location  Start1    End1      Status
Thermodynam&kinetics  CHEM-2102L  073  14:00  17:15   MAIN   9/8/2011  12/20/2011  A
Thermodynam&kinetics  CHEM-2102L  074  14:30  17:15   MAIN   9/7/2011  12/18/2011  A
Organic Chemistry I   CHEM-2202   001   9:00  10:20   MAIN   9/7/2011  12/20/2011  A
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):

Title                   Course    Sec  StartDateCheck  EndDateCheck  SlotStartCheck  SlotEndCheck
Thermodynam&kinetics  CHEM-2102L  073  Check Start Date    
Thermodynam&kinetics  CHEM-2102L  074                  Check End Date
Organic Chemistry I   CHEM-2202   001                                Check Slot Start Time
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).

[NeoPa]

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 :

SELECT   tQ.Title
       , tQ.Sec
       , tQ.[...]
       , IIf(Min(tQ.Sec Between tR.Low And tR.High), 'Found Result', 'Not Found Result')
       , ...
FROM     [QVC] AS [tQ]
       , [Roomless] AS [tR]
GROUP BY tQ.Title
       , tQ.Sec
       , ...