calling a value from Aggregate Recordset?!?!

[T Best]

i'm opening up a recordset using the following code. it's an Aggregate
query. how do i call
the resultant value? The SQL code runs ok, i don't get any errors.

Dim TotalPaidIndem
Dim dbs As Database
Dim rst As Recordset
Dim strClaims As String

Set dbs = CurrentDb

strClaims = "SELECT Sum(RCADET.[11#_PD-IND]) AS [SumOf11#_PD-IND] " & _
"FROM RCACMB INNER JOIN RCADET ON RCACMB.[1#_CLM#] =
RCADET.[1#_CLM#] " & _
"WHERE (((RCACMB.[1#_CLM#]) Like 'SN*' Or (RCACMB.[1#_CLM#])
Like 'NS*') AND ((RCADET.[10#_DT-OS-]) Between #" &
[Forms]![frmClaimsReportRunner]![txtDtTransStart] & "# And #" &
[Forms]![frmClaimsReportRunner]![txtDtTransEnd] & "#));"

Set rst = dbs.OpenRecordset(strClaims)

i was trying...
TotalPaidIndem = rst.[SumOf11#_PD-IND]

and
TotalPaidIndem = rst.value

but kept getting Method or data member not found.

what am i doing wrong?
TIA
Ted

 

[Gary Walter]

It looks to me like you just want the "value"
so no need to open a recordset:

Dim TotalPaidIndem
Dim strClaims As String

strClaims = "SELECT Sum(RCADET.[11#_PD-IND]) AS [SumOf11#_PD-IND] " & _
"FROM RCACMB INNER JOIN RCADET ON RCACMB.[1#_CLM#] =
RCADET.[1#_CLM#] " & _
"WHERE (((RCACMB.[1#_CLM#]) Like 'SN*' Or (RCACMB.[1#_CLM#])
Like 'NS*') AND ((RCADET.[10#_DT-OS-]) Between #" &
[Forms]![frmClaimsReportRunner]![txtDtTransStart] & "# And #" &
[Forms]![frmClaimsReportRunner]![txtDtTransEnd] & "#));"

TotalPaidIndem=
CurrentProject.Connection.Execute(strClaims).Fields(0).Value

 

[John Spencer]

Try the following

TotalPaidIndem = rst![SumOf11#_PD-IND]
Or
TotalPaidIndem = rst.Fields(0)
OR
TotalPaidIndem = rst.Fields("SumOf11#_PD-IND")

 

[T Best]

the bang did the trick! thanks for the help.
i knew it was something stupid

Try the following

TotalPaidIndem = rst![SumOf11#_PD-IND]
Or
TotalPaidIndem = rst.Fields(0)
OR
TotalPaidIndem = rst.Fields("SumOf11#_PD-IND")

i'm opening up a recordset using the following code. it's an Aggregate
query. how do i call
the resultant value? The SQL code runs ok, i don't get any errors.

Dim TotalPaidIndem
Dim dbs As Database
Dim rst As Recordset
Dim strClaims As String

Set dbs = CurrentDb

strClaims = "SELECT Sum(RCADET.[11#_PD-IND]) AS [SumOf11#_PD-IND] " & _
"FROM RCACMB INNER JOIN RCADET ON RCACMB.[1#_CLM#] =
RCADET.[1#_CLM#] " & _
"WHERE (((RCACMB.[1#_CLM#]) Like 'SN*' Or (RCACMB.[1#_CLM#])
Like 'NS*') AND ((RCADET.[10#_DT-OS-]) Between #" &
[Forms]![frmClaimsReportRunner]![txtDtTransStart] & "# And #" &
[Forms]![frmClaimsReportRunner]![txtDtTransEnd] & "#));"

Set rst = dbs.OpenRecordset(strClaims)

i was trying...
TotalPaidIndem = rst.[SumOf11#_PD-IND]

and
TotalPaidIndem = rst.value

but kept getting Method or data member not found.

what am i doing wrong?
TIA
Ted