Need help with Variable substitution

[Al]

I have a table with 2 variables based upon years (dblYr04Val, cblYr04Val). I
would like to have the following subroutine set the value of dblComValue
based upon the value of global variable sYear.

I get error 13 – Type mismatch on line 10. How do I code this so the
variable substitution will work?


Public Sub ProcessData()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim dblYrValue as Double
Dim strYrValue as String

1. Set rst = dbs.OpenRecordset("tblData", dbOpenDynaset)

2. If sYear = "2004" Then
3. strYrValue = "![dblYr04Val]"
4. Else
5. strYrValue = "![dblYr05Val]"
6. End If

7. With rst
8. .MoveFirst
9. Do While Not .EOF
10. dblComValue = strYrValue
11. .MoveNext
12. Loop
13. End With

14. Set rst = Nothing
End Sub

 

[Dirk Goldgar]

I have a table with 2 variables based upon years (dblYr04Val,
cblYr04Val). I would like to have the following subroutine set the
value of dblComValue based upon the value of global variable sYear.

I get error 13 - Type mismatch on line 10. How do I code this so the
variable substitution will work?


Public Sub ProcessData()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim dblYrValue as Double
Dim strYrValue as String

1. Set rst = dbs.OpenRecordset("tblData", dbOpenDynaset)

2. If sYear = "2004" Then
3. strYrValue = "![dblYr04Val]"
4. Else
5. strYrValue = "![dblYr05Val]"
6. End If

7. With rst
8. .MoveFirst
9. Do While Not .EOF
10. dblComValue = strYrValue
11. .MoveNext
12. Loop
13. End With

14. Set rst = Nothing
End Sub

I must warn you that it's a problematic design, embedding data (the year
number) in the design of the table (names of fields "dblYr04Val",
"dblYr05Val"). The very problem you posted about makes that clear. I
don't know all the circumstances of your database design, so I won't
belabor the point, but for most purposes it would be better to have a
field in the table named "DataYear" or something like that, in which you
would store the specific year to which the data in this record applies.
Then it would be easy to query by year.

However, to answer your question with your current design, you can use
the field name as a string index into the recordset's Fields collection:

' ...
If sYear = "2004" Then
strYrValue = "dblYr04Val"
Else
strYrValue = "dblYr05Val"
End If

' ...
With rst
' ...
dblComValue = .Fields(strYrValue)
' ...
End With

' ...

 

[Al]

Thanks Dirk. .fields works great.

You're correct about the database design. The table with the YrVals has
turned into an intersect table. We probably need to reevaluate the design.

Al

I have a table with 2 variables based upon years (dblYr04Val,
cblYr04Val). I would like to have the following subroutine set the
value of dblComValue based upon the value of global variable sYear.

I get error 13 - Type mismatch on line 10. How do I code this so the
variable substitution will work?


Public Sub ProcessData()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim dblYrValue as Double
Dim strYrValue as String

1. Set rst = dbs.OpenRecordset("tblData", dbOpenDynaset)

2. If sYear = "2004" Then
3. strYrValue = "![dblYr04Val]"
4. Else
5. strYrValue = "![dblYr05Val]"
6. End If

7. With rst
8. .MoveFirst
9. Do While Not .EOF
10. dblComValue = strYrValue
11. .MoveNext
12. Loop
13. End With

14. Set rst = Nothing
End Sub

I must warn you that it's a problematic design, embedding data (the year
number) in the design of the table (names of fields "dblYr04Val",
"dblYr05Val"). The very problem you posted about makes that clear. I
don't know all the circumstances of your database design, so I won't
belabor the point, but for most purposes it would be better to have a
field in the table named "DataYear" or something like that, in which you
would store the specific year to which the data in this record applies.
Then it would be easy to query by year.

However, to answer your question with your current design, you can use
the field name as a string index into the recordset's Fields collection:

' ...
If sYear = "2004" Then
strYrValue = "dblYr04Val"
Else
strYrValue = "dblYr05Val"
End If

' ...
With rst
' ...
dblComValue = .Fields(strYrValue)
' ...
End With

' ...

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)