use parameter as field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to use a parameter as the field name, not just a
criteria?

Snippet of my SQL:

SELECT Eval("[Forms]![frm__Setup]![lbo_CurrentFields].[Column](0)") AS
TestField, [TestField] AS [Current Value] from qry_main

The parameter [TestField] will hold the name of the field. In the first
column, I want to show the name of the field for all records. In the
second column, I want to show the actual value of this field for the
current record:

STR_STUDENTID 999999
STR_STUDENTID 999982
STR_STUDENTID 999945

I hope this makes sense. I know that this is probably so simple I'm
looking right at it, but I can't seem to figure this out.

I've already tried this in a newsgroup, but the answer there was "no".
 
This type of question generally suggests an un-normalized table structure.
For instance, you have mistakenly set up each field as a test name.
If this is the case, normalize your table structure or use a union query
like:
SELECT StudentID, [Test1] as TestScore, "Test1" as Test
FROM tblTooWide
UNION ALL
SELECT StudentID, [Test2], "Test2"
FROM tblTooWide
UNION ALL
SELECT StudentID, [Test3], "Test3"
FROM tblTooWide
UNION ALL
SELECT StudentID, [QuizA], "QuizA"
FROM tblTooWide
UNION ALL
--- etc ----
FROM tblTooWide;

You can then easily query the records where Test = "Test3".

I could be wrong and if so, please come back with a description of your
table structure.
 
Duane,

Tables are normalized. What I am doing is validating text files using a
"table of sql' approach. I am attempting to have as much of the SQL in the
table as opposed to hard-coding the statements.

Currently I have changed the " [TestField] AS [Current Value] " to "
{replFieldName} AS [Current Value] " in the table, and then I use a Replace()
to switch the {replFieldName} with the current actual field name.

I was hoping to do something with the parameter in the SQL in order to avoid
the possibility of forgetting the Replace() in code as the project grows. So
far I come up empty.

I hope this makes sense.

-Daron

Duane Hookom said:
This type of question generally suggests an un-normalized table structure.
For instance, you have mistakenly set up each field as a test name.
If this is the case, normalize your table structure or use a union query
like:
SELECT StudentID, [Test1] as TestScore, "Test1" as Test
FROM tblTooWide
UNION ALL
SELECT StudentID, [Test2], "Test2"
FROM tblTooWide
UNION ALL
SELECT StudentID, [Test3], "Test3"
FROM tblTooWide
UNION ALL
SELECT StudentID, [QuizA], "QuizA"
FROM tblTooWide
UNION ALL
--- etc ----
FROM tblTooWide;

You can then easily query the records where Test = "Test3".

I could be wrong and if so, please come back with a description of your
table structure.
--
Duane Hookom
MS Access MVP


Daron said:
Is it possible to use a parameter as the field name, not just a
criteria?

Snippet of my SQL:

SELECT Eval("[Forms]![frm__Setup]![lbo_CurrentFields].[Column](0)") AS
TestField, [TestField] AS [Current Value] from qry_main

The parameter [TestField] will hold the name of the field. In the first
column, I want to show the name of the field for all records. In the
second column, I want to show the actual value of this field for the
current record:

STR_STUDENTID 999999
STR_STUDENTID 999982
STR_STUDENTID 999945

I hope this makes sense. I know that this is probably so simple I'm
looking right at it, but I can't seem to figure this out.

I've already tried this in a newsgroup, but the answer there was "no".
Click to expand...
Click to expand...
 
If you want to dynamically allow the choosing of a field based on a
selection on a form, you can either change the SQL property of the saved
query or use a list box type control with an expression in the query like:

ChoosenFieldValue: Choose([Forms]![FrmYourForm]!lboField, [FirstName],
[LastName], [City], [State])
--
Duane Hookom
MS Access MVP

Daron said:
Duane,

Tables are normalized. What I am doing is validating text files using a
"table of sql' approach. I am attempting to have as much of the SQL in the
table as opposed to hard-coding the statements.

Currently I have changed the " [TestField] AS [Current Value] " to "
{replFieldName} AS [Current Value] " in the table, and then I use a
Replace()
to switch the {replFieldName} with the current actual field name.

I was hoping to do something with the parameter in the SQL in order to
avoid
the possibility of forgetting the Replace() in code as the project grows.
So
far I come up empty.

I hope this makes sense.

-Daron

Duane Hookom said:
This type of question generally suggests an un-normalized table
structure.
For instance, you have mistakenly set up each field as a test name.
If this is the case, normalize your table structure or use a union query
like:
SELECT StudentID, [Test1] as TestScore, "Test1" as Test
FROM tblTooWide
UNION ALL
SELECT StudentID, [Test2], "Test2"
FROM tblTooWide
UNION ALL
SELECT StudentID, [Test3], "Test3"
FROM tblTooWide
UNION ALL
SELECT StudentID, [QuizA], "QuizA"
FROM tblTooWide
UNION ALL
--- etc ----
FROM tblTooWide;

You can then easily query the records where Test = "Test3".

I could be wrong and if so, please come back with a description of your
table structure.
--
Duane Hookom
MS Access MVP


Daron said:
Is it possible to use a parameter as the field name, not just a
criteria?

Snippet of my SQL:

SELECT Eval("[Forms]![frm__Setup]![lbo_CurrentFields].[Column](0)") AS
TestField, [TestField] AS [Current Value] from qry_main

The parameter [TestField] will hold the name of the field. In the first
column, I want to show the name of the field for all records. In the
second column, I want to show the actual value of this field for the
current record:

STR_STUDENTID 999999
STR_STUDENTID 999982
STR_STUDENTID 999945

I hope this makes sense. I know that this is probably so simple I'm
looking right at it, but I can't seem to figure this out.

I've already tried this in a newsgroup, but the answer there was "no".
Click to expand...
Click to expand...
Click to expand...
 
Back
Top