CODEWITHSUNDEEP
ms-accessms-access-2010
GRY
GRY

Reputation: 724

Access variable Field Names

In MS Access 2010, I am building an update query (using the Query Designer). I would like to pass the name of the column to update as a variable, at run time.

I have tried writing the SQL and running the query in VBA. This seemed like the easiest way... however the SQL to complete the update becomes quite messy. I would prefer to do this in the query builder GUI . Is it possible?

I have so far tried entering field names into the query builder:

expr1:[field_name]

Although Access prompts me for "Field_name" This results in "Cannot update 'field_name'; field not updateable.

Also, I tried this method:

Expr1: "'" & [field_name] & "'"

which results in "'" & [field_name] & "'" is not a valid name; check for punctuation.. etc

Below is a screen capture the query I am trying to build.

a screen cap of the query I am proposing

Upvotes: 3

Views: 15654

Answers (3)

Schalton
Schalton

Reputation: 3104

I use this method frequently - I know it's a very old post, but hope this helps someone - building on what David said:

Sub CreateQuery
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("NameOfNewQuery")
strSQL = "Select " 'notice the space
strSQL = strSQL & "FROM " 'notice the sapce
strSQL = strSQL & "WHERE;"
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
Set dbs = Nothing
End Sub

Upvotes: 0

David Poduska
David Poduska

Reputation: 11

Perhaps the best method is to use SQL, build your prompts and then assign these values to variables in VBA, then just add the variable value into your SQL.

So something along these lines. Your using Update query but same logic

Dim SQL as string
dim **FieldName** as string

SQL = "Select [table]![" & Chr(34) & **FieldName** & Chr(34) & "] from ........"

Check Here for SQL building tips

Upvotes: 1

HansUp
HansUp

Reputation: 97101

Access' db engine will not allow you to use a parameter as the name of the target field for your UPDATE statement.

If you try a user-defined function instead of a parameter to provide the field name, the result will be the same ... no joy.

It seems the db engine will not resolve object names when it executes a SQL statement. That limitation applies not just to field names, but table names as well. IOW, the following query will fail with an error message that it "cannot find the input table or query 'give me a table name'".

SELECT *
FROM [give me a table name];

While that isn't exactly the same as your situation, I suspect the reason may be. The db engine is too limited about resolving object names when it plans/executes queries.

Upvotes: 2

Related Questions