parameter for field

[Guest]

Good day,
I wish to be able to run a report which first prompts a user for a field
name, then displays the report based on the data in that field. I have the
report and the query worked out, i just dont know how to accept a parameter
as a field in the query.

I would prefer to avoid VB if possible so that the suits can more easily
understand and modify it later.
thanks in advance

Mike P

 

[Allen Browne]

You cannot use a parameter for a field name.

You could use a form with a combo that has its RowSourceType set to Field
List so that it offers the fields, and then build a WhereCondition string
based on the field name and the value they entered, but you must also use
the correct delimiter for the field type.

If you don't know which field the value is in, it mean you have an
unnormalized table. You might consider whether the different fields should
be related records where the values are stored in a field, and another field
distinguishes the types.

 

[Guest]

Thanks for the quick reply.

Could you please explain how this WhereCondition string would work? My table
is normalized. I have 99 columns, called 01 to 99. The user must select one
to use. None of these are the key but there is a key for the table.

Is this the only way to have a variable field name?

thanks again

 

[Allen Browne]

Assuming:
- all 01 - 99 are text fields;
- the combo that contains the field names is called cboField
- the text box that contains the value to search for is called txtFind
- the report you want to open is called Report1

Private Sub cmdFind_Click()
Dim strWhere As String
If IsNull(Me.cboField) Or IsNull(Me.txtFind) Then
MsgBox "Need both field and value."
Else
strWhere = "[" & Me.cboField & "] = """ & Me.txtFind & """"
'Debug.Print strWhere
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End If
End Sub

The extra quotes are needed because the fields are Text type.
The square brackets are needed because the field names start with a number.

 

[onedaywhen]

You cannot use a parameter for a field name.

You can encode it e.g.

CREATE PROCEDURE TestProc (
arg_search_value INTEGER,
arg_01 INTEGER = 0,
arg_02 INTEGER = 0,
arg_03 INTEGER = 0
) AS
SELECT * FROM
Test WHERE SWITCH(
arg_01 <> 0, col_01,
arg_02 <> 0, col_02,
arg_03 <> 0, col_03
) = arg_search_value;

or you can pass the name of the field as text:

CREATE PROCEDURE TestProc (
arg_search_value INTEGER,
arg_column_name CHAR(6) = 'col_01'
) AS
SELECT * FROM
Test WHERE SWITCH(
arg_column_name = 'col_01', col_01,
arg_column_name = 'col_02', col_02,
arg_column_name = 'col_03', col_03,
TRUE, col_01
) = arg_search_value;

Jamie.

--