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])));"

On Wed, 3 Nov 2010 06:09:47 -0700 (PDT), colin spalding
<colin.mardell (AT) btopenworld (DOT) com> wrote:

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

On Nov 3, 9:09*am, colin spalding <colin.mard... (AT) btopenworld (DOT) com>
wrote:
I am not even sure what your question is, but generally for this type
of question you need to specify what language and tool version the
code is in and post the actual error message. I am not even sure if
you have a compile type error or a run-time error. The database
version is also usually important information.

HTH -- Mark D Powell --