this should be quick

  • Thread starter Thread starter T Best
  • Start date Start date
T

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
 
Try using the bang (!) after rst instead of dot (.)

TotalPaidIndem = rst![SumOf11#_PD-IND]

HTH
 
Ted,

By the way, as an aside, it is not a good idea to use a # or a - as part
of the name of a field or control or database object. I would recommend
changing these if you can, and certainly avoid this practice in future.
 
i couldn't agree more. it was like that when i got here. there are so many
queries and reports based on these field names. it would be an absolute
nightmare to change them. maybe one of these days...

thanks so much for the help...that worked. i knew it was something stupid

Steve Schapel said:
Ted,

By the way, as an aside, it is not a good idea to use a # or a - as part
of the name of a field or control or database object. I would recommend
changing these if you can, and certainly avoid this practice in future.

--
Steve Schapel, Microsoft Access MVP
Try using the bang (!) after rst instead of dot (.)

TotalPaidIndem = rst![SumOf11#_PD-IND]

HTH
Click to expand...
Click to expand...
 
Back
Top