Getting Some VB Code to Change the fields a query runs on

[AlisonEmmett]

I have a feeling this will be difficult to explain...
I have a table with structure as follows:
Server Text
Date Date
Stat1 Value
Stat2 Value
Stat3 Value

I have a form with combo boxes for Server and Stat
Thus the user chooses a particular server and a particluar Statistic (Stat1 or Stat2 or Stat3).
I then want to run a query based on those two choices. I can't write the query easily becasuse I don't know which Stat field will be chosen - and I don't want to have three queries and three buttons if I can avoid it.

Possibly I need some VB that changes my query to the appropriate Stat field before the query is actually run. Is this possible ?. Thus my query might be "Select Server,Avg(Stat1) from table; - but if the combo box choice is Stat2, then the query needs to change to "Select Server, Avg(Stat2) from table;

Does anyone understand the dilemma ???

any help gratefully received - I can't work out the code [sig][/sig]

 

[MichaelRed]

AlisonEmmett,

You need to 'write' the SQL statement in CODE. There are various ways to use the statement once you have it writen, the choice will basically depend on what you are doing w/ the result.

A simple way is to just set a recordset assignment with the SQL string as the source.

Dim dbs as Database
Dim MyRs as recordset

SEt dbs = currentdb
set Myrs = dbs.openrecordset(YourSqlHere, dbopendynaset)

If you understand it this far, you should be able to finish it. If not, it is probably not he right approach for you at this time.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[AlisonEmmett]

Thanks Michael - how am I going to be able to change the SQL statement according to what is chosen in the form ? [sig][/sig]

 

[MichaelRed]

AllisonEmmett,

just use the combobox names as the variable name source(s)

e.g. Avg(cboMyStat)

I don't use Ms Access much these days, so you may need to adjust the syntax, but the concept is to just get the &quot;value&quot; being dispalyed in the combobox as the target/source of the AVG function.

When I first started using Ms Access I would let the query builder generate a &quot;static&quot; version of queries which I need to construct dynamically, after it 'works' as a static query, switch to the &quot;SQL&quot; view, Copy the SQL Text, switch to the module where I want to construct the dynamic version and paste Ms Access's SQL into the code as a comment (or group of them). With the static SQL as A 'guide', write the SQL statement - in code -and modify it as necessary to get the &quot;Dynamic&quot; behaviour.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]