Query with user defined columns

[Roger Withnell]

My query has a WHERE clause on a column (no surprise there!).

When the query is run, I would like the query to ask the user to enter field
name of this column .

How do I do this?

Thanking you in anticipation.

 

[Norman Yuan]

You simply need to get the user's input and then compose the query string.

A simple example:

Dim strColumn as String
Dim strSQL as String
strColumn=InputBox("Enter column name") ''Or get input from a form's
control.
''You may wna to validate user input before run the query
''Obviously, you can also have user to input the condition for "WHERE"
clause
strSQL="SELECT * FROM myTable WHERE " & strColumn & "='Your condition'"
DoCmd RunSQL strSQL

My query has a WHERE clause on a column (no surprise there!).

When the query is run, I would like the query to ask the user to enter field
name of this column .

How do I do this?

Thanking you in anticipation.

 

[Dirk Goldgar]

My query has a WHERE clause on a column (no surprise there!).

When the query is run, I would like the query to ask the user to
enter field name of this column .

How do I do this?

About the only way you can do this is to rewrite the SQL of the query on
the fly. You can't have the query itself prompt for the name of the
column. But you can use the InputBox function or your own dialog form
to prompt the user for the name, and then modify the SQL of the query.
The code to do that might look something like the (completely untested,
"air code") example below. Note that this example assumes that you
already know what the basic SQL of the query is, and what criterion will
be applied to the field entered by the user; all you don't know is
which field it will be applied to. Here's the example:

'----- start of example -----
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strColumnName As String

strColumnName = InputBox("Enter the field name:")
If Len(strColumnName) = 0 Then
MsgBox "No field name, no query!"
Exit Sub
End If

strSQL = "SELECT foo, bar, baz FROM MyTable WHERE " & _
strColumnName & _
" > 0"

Set db = CurrentDb
Set qdf = db.QueryDefs("MyQuery")

qdf.SQL = strSQL

Set qdf = Nothing
Set db = Nothing

DoCmd.OpenQuery "MyQuery"
'----- end of example -----

If there are more variables to the situation, it can get rather hairy.

Out of curiosity, why do you need to do this? It's pretty unusual that
you'd need to apply the same criterion to any one of several possible
fields, and not know in advance which field. What is the table
structure like that it needs this sort of operation?

 

[Dirk Goldgar]

You simply need to get the user's input and then compose the query
string.

A simple example:

Dim strColumn as String
Dim strSQL as String
strColumn=InputBox("Enter column name") ''Or get input from a form's
control.
''You may wna to validate user input before run the query
''Obviously, you can also have user to input the condition for "WHERE"
clause
strSQL="SELECT * FROM myTable WHERE " & strColumn & "='Your
condition'"
DoCmd RunSQL strSQL

Except RunSQL only works with action queries, not with SELECT queries.

 

[david epsom dot com dot au]

SELECT Field3
FROM myTable WHERE
((Switch(
[Enter Field Name]="fldname1",[field1],
[Enter Field Name]="fldname2",[field2]
) AS tmpvalue =[Enter Value]));


or

SELECT myAnswer ,[Enter 1 for field1, 2 for field2] as f
FROM myTable WHERE ((Switch([f]=1 ,[Field1],[f]=2,[Field2]) AS tmpvalue
=[Enter Value]));

 

[Dirk Goldgar]

SELECT Field3
FROM myTable WHERE
((Switch(
[Enter Field Name]="fldname1",[field1],
[Enter Field Name]="fldname2",[field2]
) AS tmpvalue =[Enter Value]));


or

SELECT myAnswer ,[Enter 1 for field1, 2 for field2] as f
FROM myTable WHERE ((Switch([f]=1 ,[Field1],[f]=2,[Field2]) AS
tmpvalue =[Enter Value]));

Clever!

 

[Roger Withnell]

Thanks for this, but I get an syntax error (missing operator) on the first
which, I think, is the one I want.

I've replaced Field1, 2 and 3 with valid field names and the name of the
table, of course.

I have to admit I'm not familiar with the Switch function. Where can I find
a tutorial on it?

SELECT Field3
FROM myTable WHERE
((Switch(
[Enter Field Name]="fldname1",[field1],
[Enter Field Name]="fldname2",[field2]
) AS tmpvalue =[Enter Value]));


or

SELECT myAnswer ,[Enter 1 for field1, 2 for field2] as f
FROM myTable WHERE ((Switch([f]=1 ,[Field1],[f]=2,[Field2]) AS tmpvalue
=[Enter Value]));

 

[david epsom dot com dot au]

Switch, Choose and IIF are in the Access help. (If you have
the Access 97 help, you can use the "see also" link to
move between them).

In the Access 2003 help, they are under Expression, then
Functions.

Thanks for this, but I get an syntax error (missing operator)

Look very carefully at your SQL: you probably have a missing
comma, or perhaps a misplaced bracket or quote.

(david)

Thanks for this, but I get an syntax error (missing operator) on the first
which, I think, is the one I want.

I've replaced Field1, 2 and 3 with valid field names and the name of the
table, of course.

I have to admit I'm not familiar with the Switch function. Where can I
find a tutorial on it?

SELECT Field3
FROM myTable WHERE
((Switch(
[Enter Field Name]="fldname1",[field1],
[Enter Field Name]="fldname2",[field2]
) AS tmpvalue =[Enter Value]));


or

SELECT myAnswer ,[Enter 1 for field1, 2 for field2] as f
FROM myTable WHERE ((Switch([f]=1 ,[Field1],[f]=2,[Field2]) AS tmpvalue
=[Enter Value]));

My query has a WHERE clause on a column (no surprise there!).

When the query is run, I would like the query to ask the user to enter
field name of this column .

How do I do this?

Thanking you in anticipation.