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 it 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 3 nov, 15:15, colin spalding <colin.mard... (AT) btopenworld (DOT) com> wrote:
You cannot use a query to assign a value to a field. A query returns 0
or more rows with 1 or more columns. You are selecting multiple
columns in the query, so what do you want to assign to the field
anyway? IPT_Percentage, IPT_RateChangeStartDate, IPT_RateChangeFinish?

Use the DLoopUp function to get one value.

Groeten,

Peter
http://access.xps350.com

Dim x as double
Dim iptCurrent = ...

x=DbCurrent.OpenRecordset(iptCurrent).Fields(0)

You probably need to change your query string to have it work in this context.
You will have to incorporate the values from the form instead of referencing
the controls within the string.

iptCurrent = "SELECT IPT_Rates.IPT_Percentage" _
& " FROM IPT_Rates " _
& " GROUP BY IPT_Rates.IPT_Percentage" _
& " WHERE IPT_Rates.IPT_RateChangeStartDate <=" _
& FORMAT([Forms]![frmPolicy]![txtPeriodStartDate],"\#yyyy-mm-dd\#") _
& " AND IPT_Rates.IPT_RateChangeFinish >=" & _
Format([Forms]![frmPolicy]![txtPeriodStartDate],"\#yyyy-mm-dd|#")

Although, that makes me wonder why you don't just use DLookup or Dmin or Dmax
to get the required value.

x = NZ(DMax("IPT_Percentage","IPT_Rates","IPT_RateChan geStartDate
<=[Forms]![frmPolicy]![txtPeriodStartDate] AND IPT_RateChangeFinish
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 11/3/2010 10:15 AM, colin spalding wrote: