Problem accessing recordset fields with variable names

[pachederm]

Help!

I have a query with enumerated field names (i.e. CBL_1_kW,CBL_2_kW,CBL_3_kW,......CBL_10_kW)

I'm trying to construct the field names with a counter and feed them into an array where I can sort them later. (i.e. rs!["CBL_"& Str(i) & "_kW")

The problem I am having is that the program fails to compile when the field name is constructed in the for loop with the counter (i). If I manually type in the variable it works find but this is tedious and poor programming I would think.

Any advice?


----------------------

type MeterReading
Date as variant
kW as variant
end type

dim MeterReadings(1 to 10) of MeterReading


rs.movefirst
do while not (rs.eof)
for (i=1 to 10)
with MeterReadings(i)
.Date = rs!["CBL_"&Str(i)&"_Date"] <--- compile error
.kW = rs!["CBL_"&Str(i)&"_kW"] <---- compile error
end with
next i
rs.movenext
loop

 

[MarkK]

.Date = rs!["CBL_" & i & "_Date"]  [COLOR="Green"]'should work fine[/COLOR]

Typically when you convert to a string use CStr().
Str() converts a long to a string but leaves a leading space for an implied plus (+) sign.
Welcome to the forum,
Mark

 

[pachederm]

Thanks Mark,

I'll try it tomorrow when I get into the office...
you know...come to think of it, the resultant string in the watch window did look like it had spaces in it...I though it was just screen formatting.

thanks again!

 

[pachederm]

Didn't work. When I look at the watch window, the code looks like this rsCBL!["CBL_1_Date"] with quotes, and I get an "item not found in collection" error.

If I hard-code .ReadDate = rsCBL![CBL_1_Date] for example, it works fine....

I looks like there are "quotes" in the calculation of the field name....

does anyone know how to convert the fieldname variable so it doesn't crash the compiler when I pass it to the rsCBL!. variable?

For i = 1 To 10 'put all ten radings into array if include = true

If (rsCBL!Include1 = "Y") Then <--- works
With MeterReadings(i)
.ReadDate = rsCBL!["CBL_" & Cstr(i) & "_Date"] <---dies
End With
End If
Next i

 

[spikepl]

rsCBL("CBL_" & Cstr(i) & "_Date")

 

[boblarson]

This should work

.Date = rs.Fields("CBL_" & Str(i) & "_Date").Value

 

[pachederm]

Yes the Bob, worked fine.......took 3 days to figure it out but hey, i'm all the better now

 

[vbaInet]

You don't need to convert the i to string. It is surrounded by strings already. The date line (for example) should be:

.Date = rs("[CBL_" & i & "_Date]")

Edit: Late reply

 

[boblarson]

Oops, I meant to remove that as well.