Pass Field Name Using Variable to SQL string

[Ross]

I want to pass field names to an SQL string to identify the current of many
fields that I have to update in the table tblData (ie Fields ----> Jan06,
Feb06, Mar03......Dec09).

I want to use a recordset with SQL to identify the current field and edit
and update the current record in the current field.

The field is identified in the SQL as followings:

tblData." & varFieldName & " where the variable might be set to "Jan".
<----------

This syntax works perfectly in the SQL to identify the current field in the
db.openreocrdset(strsql).

The update is NOT working :
..edit
!Jan = dblAmount <---------------This works
![" & varFieldname & "] = dblAmount <----------This does not work
..update

This error message "item not found in collection" implies that it is
looking for a field "Jan" in tblData but is unable to find "Jan" which does
exists. Highlighting the variable indicates that the variable is correctly
set to Jan.

How can I make this work using variables. My syntax must be wrong? I am
using a variant variable and had the same results with a string variable.

Thanks

Ross

 

[Tom van Stiphout]

On Fri, 21 Nov 2008 17:54:22 -0800, Ross

First of all, you have a HORRIBLE database design, which violates
important database design principles such as the "repeating group"
principle. So why not first fix that elephant in the room?

Chances are I did not convince you, so here goes:
..Fields(varFieldName).Value = dblAmount

-Tom.
Microsoft Access MVP

 

[Ross]

Tom
I understand this, that it is horrible.

Your right, the first thing I said was / Let me normalize and
transactionalize this and their head IT guy said, "well, in an ideal world
.......but he said there is not enough time. Do it this way for expedience."

1. I am doing a quick and dirty for Excel financial people who understand
only spreadsheet design.

2. I have been given four days to convert this spreadsheet to Access (side
project while I am waiting permissions for other systems) because there are
so many complex calculations in the spreadsheet that Excel locks up.

3. The time required to understand and converting the calculations is
limiting my remaining time to develop.

Your right, the first thing I said was / Let me normalize and
transactionalize this and their head IT guy said, "well, in an ideal world
.......but he said there is not enough time. Do it this way for expedience."

Hence, there is a reasons for everything and some reasons are better than
others!

On Fri, 21 Nov 2008 17:54:22 -0800, Ross

First of all, you have a HORRIBLE database design, which violates
important database design principles such as the "repeating group"
principle. So why not first fix that elephant in the room?

Chances are I did not convince you, so here goes:
..Fields(varFieldName).Value = dblAmount

-Tom.
Microsoft Access MVP

I want to pass field names to an SQL string to identify the current of many
fields that I have to update in the table tblData (ie Fields ----> Jan06,
Feb06, Mar03......Dec09).

I want to use a recordset with SQL to identify the current field and edit
and update the current record in the current field.

The field is identified in the SQL as followings:

tblData." & varFieldName & " where the variable might be set to "Jan".
<----------

This syntax works perfectly in the SQL to identify the current field in the
db.openreocrdset(strsql).

The update is NOT working :
.edit
!Jan = dblAmount <---------------This works
![" & varFieldname & "] = dblAmount <----------This does not work
.update

This error message "item not found in collection" implies that it is
looking for a field "Jan" in tblData but is unable to find "Jan" which does
exists. Highlighting the variable indicates that the variable is correctly
set to Jan.

How can I make this work using variables. My syntax must be wrong? I am
using a variant variable and had the same results with a string variable.

Thanks

Ross