If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Pass Field Name Using Variable to SQL string
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 |
#2
|
|||
|
|||
Pass Field Name Using Variable to SQL string
On Fri, 21 Nov 2008 17:54:22 -0800, Ross
wrote: 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 |
#3
|
|||
|
|||
Pass Field Name Using Variable to SQL string
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! "Tom van Stiphout" wrote: On Fri, 21 Nov 2008 17:54:22 -0800, Ross wrote: 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 |
Thread Tools | |
Display Modes | |
|
|