5117869c   
   From: hugo@perFact.REMOVETHIS.info.INVALID   
      
   On Wed, 3 Nov 2010 06:09:47 -0700 (PDT), colin spalding   
    wrote:   
      
   >I have been away from this for a long time and seem to have forgotten   
   >much of which I learned.  This syntax will always only ever return one   
   >record and I need to use the value returned in the column   
   >"IPT_Rates.IPT_Percentage".  What am I missing?  Is the use of   
   >"Column() if so where?   
   >Thanks in advance from one frustrated brain dead amateur.   
   >   
   >Dim iptCurrent As String   
   >   
   >iptCurrent = "SELECT IPT_Rates.IPT_Percentage,   
   >IPT_Rates.IPT_RateChangeStartDate, IPT_Rates.IPT_RateChangeFinish " _   
   >& "FROM IPT_Rates " _ & "GROUP BY IPT_Rates.IPT_Percentage,   
   >IPT_Rates.IPT_RateChangeStartDate, IPT_Rates.IPT_RateChangeFinish " _   
   >& "HAVING (((IPT_Rates.IPT_RateChangeStartDate)<=([Forms]![frmPolicy]!   
   >[txtPeriodStartDate])) " _   
   >& "AND ((IPT_Rates.IPT_RateChangeFinish)>=([Forms]![frmPolicy]!   
   >[txtPeriodStartDate])));"   
      
   Hi Colin,   
      
   If you only need to return one column, you should only specify one   
   column. So the query should read:   
      
   SELECT IPT_Rates.IPT_Percentage   
   FROM (...rest of query unchanged)   
      
   Based on your total description, I think you can also simplify the   
   query. You should test, of course, but I expect that the query below   
   is equivalent:   
      
   SELECT DISTINCT IPT_Percentage   
   FROM   IPT_Rates   
   WHERE  [Forms]![frmPolicy]![txtPeriodStartDate]   
            BETWEEN IPT_RateChangeStartDate   
            AND    IPT_RateChangeFinish;   
      
   You probably don't need the DISTINCT either, but that's impossible for   
   me to tell with the info you gave. Remove it if you can, as the query   
   will probably be faster without this keyword.   
   --   
   Hugo Kornelis, SQL Server MVP   
   My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)