Re: Pass Field Name Using Variable to SQL string

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Fri, 21 Nov 2008 17:54:22 -0800, Ross
<Ross@xxxxxxxxxxxxxxxxxxxxxxxxx> 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



.

Relevant Pages

  • Re: transition from programmer to developer
    ... copies of Chris Date's "Principles of Database Design" and "The SQL ... Standard" on my shelf. ... SQL standard. ... Newcomers to database design may not have heard of Chris Date. ...
    (comp.lang.java.programmer)
  • Re: how to sum a text field with commas
    ... "BY DEFINITION a column has scalar values, this is not a valid value in SQL" ... Holding "1,2,3" in a column has nothing to do with DDL, DML, DCL, and transaction control. ... IT has everything to do with database design 1nf, ... I find that most one-shot jobs have the data I want on a ...
    (microsoft.public.sqlserver.programming)
  • Re: Generating SQL
    ... Concatenated string are a PITA. ... most CRUD I encounter is not that simple. ... > SQL. ... There is no substitute for good database design. ...
    (comp.object)
  • Re: how to sum a text field with commas
    ... This violates a thing called First Normal Form which you ... I think you are confusing the query language (SQL, or in this product TSQL) with database design. ...
    (microsoft.public.sqlserver.programming)
  • Re: Returning too many lines
    ... Good database design does require some effort. ... Here are some general database, SQL, and and MS Access resources. ... Database Design for Mere Mortals by Michael J. Hernandez ... Books: General: Intermediate/Advanced ...
    (microsoft.public.access.queries)