Passing a form value into a SQL Statement

[Guest]

Hello:

I have a form that has a drop-down box containing different quarters. I
want to pass the year into a SQL statement as a field name in the query.

So, for example, when I select the 2Q05 in the drop-down box,
I want the statement to
SELECT TABLE1."2Q05" FROM TABLE1

I hope that was clear enough.

Thank you in advance!

 

[Albert D.Kallal]

Where do you want to the sql to be used?

You can't really substitute a field for a general query. In fact, parameters
DO NOT work this way. In fact, this means your data is not normalized, and
will difficult to work with to create repots that you need.

You would be best to have a date type field, and then use the format()
command to produce a 1-4 value based on the date.

However, you do have what you have.

So, when you select the 2q05 in the drop down, where do you want the sql to
be changed?

You can simply use the after update event of the combo box as follows

dim strSql as string

strSql = "select " & me.MyCombo & " from table1"

' at this point...it is not clear where, or what you want to do with the
above sql, but you are free to stuff this sql into another combo box, or
listbox, or sub-form...or whatever....

eg:
my.MyOtherComboBox.RowSource = strSql

So, in effect, you just buld your sql in code..and DO NOT try and use some
type of parmaters in it....the above thus should work fine for you...

-
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Guest]

Thank you Albert.

I am now trying to run the SQL string I created in a click event button.
(Forgive my ignorance in the proper terms)

How do I run the string I created?

 

[Albert D.Kallal]

How do I run the string I created?

I will re-quote from my previous post:

<quote>
' at this point...it is not clear where, or what you want to do with the
above sql
</quote>

Ok, so, I ask the question again:
Where, or what do you want to do this sql? You can't simply "run" the sql
and expect that results to be of any use. Likely, you want to sql for a
report, or perhaps a lsitbox, or perhaps the results have to go to a form?
So, you don't run the string in a sense, but you DECIDE WHERE/WHAT you want
to do this sql? I suppose you might want to create query, and the launch
the query..but how can a user do anything with that? (perhaps you do need to
do this for reasons of a export...but again...I just don't know).

So, is the results to be displayed in report? Perhaps a listbox? perhaps a
form? We can play the guessing game for many questions!

Until we know what you want to do with this sql, I can't give an answer. I
can give some wild guess (like in my last past I gave an example of how to
use the sql in another combo box).

So, where (or what) do you want to use the sql for?

The answer is in your answer!!

 

[Guest]

Hi Albert,

sorry i wasn't more clear. I would like my results to be in a table.

 

[Albert D.Kallal]

Hi Albert,

sorry i wasn't more clear. I would like my results to be in a table.

Do you need the results to a new table, or sent into an existing table ?
(that existing table will have all the fields?).

Do you want to append the data to the existent table, or do we "empty out"
the data in this table each time you run the sql?

 

[Albert D.Kallal]

In the mean time, lets just assume you mean to send the data to a new table.

The code to do this would be:

Dim strSql As String

Dim strToTable As String

strToTable = "MyNewTable"

On Error Resume Next
CurrentDb.Execute "drop table " & strToTable
On Error GoTo 0

strSql = "select " & me.MyCombo & " into " & strToTable & _
" from table1 "

CurrentDb.Execute strSql


The above would create a new table called "MyNewTable"