Varable in a query

  • Thread starter Thread starter Ralph
  • Start date Start date
R

Ralph

Is there any way to assign a variable to an update query
that I am executing from a module using the querydef. I
have 12 months of columns in a table named -
"month1" "month2" etc. --but- I want the update to use the
1 column that I have assigned a variable name to earlier
in the module. I.E. I want the update to use the column
named "month6" which I have asssigned the variable
name 'varmo'. Can I build the query itself, where it
asks for what I want to update to, to refer to the
variable named 'varmo'
Hope this make sense ---
Thanks
 
Is there any way to assign a variable to an update query
that I am executing from a module using the querydef. I
have 12 months of columns in a table named -
"month1" "month2" etc.
Click to expand...

Are you aware that this is a BADLY denormalized database structure? A
much better design would be to have one value per record with the
month stored as data, not as a fieldname! This design would avoid the
problem you're having; and if you want to present the data in
"spreadsheet" view you could run a Crosstab query.
--but- I want the update to use the
1 column that I have assigned a variable name to earlier
in the module. I.E. I want the update to use the column
named "month6" which I have asssigned the variable
name 'varmo'. Can I build the query itself, where it
asks for what I want to update to, to refer to the
variable named 'varmo'
Hope this make sense ---
Thanks
Click to expand...

You'll need to actually construct the SQL string in VBA code,
concatenating the variable into the string wherever you need to refer
to the field (in the SELECT, WHERE, and/or ORDER BY clauses).

Seriously - consider normalizing. You're "committing spreadsheet upon
a database", and doing so will make your life unnecessarily complex.
 
You can either insert the variable into the query SQL string like so:

mySQLstring = "Update MyTable Set Myfield = " & varmo & " ... "

or you can create a simple function which returns the value of varmo

public function Myfunction() as string
Myfunction = varmo
end function

and then use this function in your query.

Hope this helps,

Peter De Baets
Peter's Software - MS Access Tools for Developers
http://www.peterssoftware.com
 
Back
Top