Dynamic selection of fielsd in a query.

[Guest]

I use the following query to provide selected input into another query and it
works fine as long as I hardcode the 9919 and 'SS ID 13'. there are other
column headings such as 'SS ID 14', 'SS ID 5' and so forht that I need to use
in the query depending on which event a contestant has entered.

How do I code this to dynamicaly change the column used in the query?

FormA.fld2 contains the value I want to use to select the correct column.

SELECT 9910 AS [Entry ID], [Penalty SS XRef].[Penalty ID], 0 AS Penalty,
[Penalty SS XRef].[SS ID 13] AS [Sort Key]
FROM [Penalty SS XRef]
WHERE ((([Penalty SS XRef].[SS ID 13])>0));

I can switch columns using a string in a VBA form module but I need to do
this within a query.

 

[Guest]

You can use Forms![FormName]![TextBoxName]

Something like

SELECT Forms![FormA]![fld2] AS [Entry ID], [Penalty SS XRef].[Penalty ID], 0
AS Penalty,
[Penalty SS XRef].[SS ID 13] AS [Sort Key]
FROM [Penalty SS XRef]
WHERE ((([Penalty SS XRef].[SS ID 13])>0));

 

[Guest]

I am assuming that your 9919, 'SS ID 13', 'SS ID 14', and 'SS ID 5' are field
names.
I would suggest you change your table structure and have a field 'Event' and
then put 'SS ID 13', 'SS ID 14', and 'SS ID 5' in your records. That way you
could use a form or prompt to input the select criteria.

[Penalty SS XRef].[SS ID 13] AS [Sort Key]

Are you sorting on this field as it seems by the name of the alias? You do
not have an ORDER BY in the SQL.
--
KARL DEWEY
Build a little - Test a little

I use the following query to provide selected input into another query and it
works fine as long as I hardcode the 9919 and 'SS ID 13'. there are other
column headings such as 'SS ID 14', 'SS ID 5' and so forht that I need to use
in the query depending on which event a contestant has entered.

How do I code this to dynamicaly change the column used in the query?

FormA.fld2 contains the value I want to use to select the correct column.

SELECT 9910 AS [Entry ID], [Penalty SS XRef].[Penalty ID], 0 AS Penalty,
[Penalty SS XRef].[SS ID 13] AS [Sort Key]
FROM [Penalty SS XRef]
WHERE ((([Penalty SS XRef].[SS ID 13])>0));

I can switch columns using a string in a VBA form module but I need to do
this within a query.

 

[Guest]

I had tried that and I get the correct number of records but the 'entry ID'
field has one little square in each entry rather than the numeric value I
expected. I have tried using 'Format()' but no matter how I specify the
format I get a text field.

I can live with that but the real problem is how to code the column name
where 'SS ID' is a constant and '13' or whatever is a field value. any
suggestions.

I guess I could make the query a table and deal with delete all entries
after entering the scores of each competitor. I get hung up on these issues
where "I just know there is a better solution"
--
Steve S

You can use Forms![FormName]![TextBoxName]

Something like

SELECT Forms![FormA]![fld2] AS [Entry ID], [Penalty SS XRef].[Penalty ID], 0
AS Penalty,
[Penalty SS XRef].[SS ID 13] AS [Sort Key]
FROM [Penalty SS XRef]
WHERE ((([Penalty SS XRef].[SS ID 13])>0));

--
Good Luck
BS"D

I use the following query to provide selected input into another query and it
works fine as long as I hardcode the 9919 and 'SS ID 13'. there are other
column headings such as 'SS ID 14', 'SS ID 5' and so forht that I need to use
in the query depending on which event a contestant has entered.

How do I code this to dynamicaly change the column used in the query?

FormA.fld2 contains the value I want to use to select the correct column.

SELECT 9910 AS [Entry ID], [Penalty SS XRef].[Penalty ID], 0 AS Penalty,
[Penalty SS XRef].[SS ID 13] AS [Sort Key]
FROM [Penalty SS XRef]
WHERE ((([Penalty SS XRef].[SS ID 13])>0));

I can switch columns using a string in a VBA form module but I need to do
this within a query.

 

[Guest]

To add a constant before the form parameter

SELECT "SS ID" & Forms![FormA]![fld2] AS [Entry ID], [Penalty SS
XRef].[Penalty ID], 0 AS Penalty,
[Penalty SS XRef].[SS ID 13] AS [Sort Key]
FROM [Penalty SS XRef]
WHERE ((([Penalty SS XRef].[SS ID 13])>0));

Not sure about the "little square in each entry"
--
Good Luck
BS"D

I had tried that and I get the correct number of records but the 'entry ID'
field has one little square in each entry rather than the numeric value I
expected. I have tried using 'Format()' but no matter how I specify the
format I get a text field.

I can live with that but the real problem is how to code the column name
where 'SS ID' is a constant and '13' or whatever is a field value. any
suggestions.

I guess I could make the query a table and deal with delete all entries
after entering the scores of each competitor. I get hung up on these issues
where "I just know there is a better solution"
--
Steve S

You can use Forms![FormName]![TextBoxName]

Something like

SELECT Forms![FormA]![fld2] AS [Entry ID], [Penalty SS XRef].[Penalty ID], 0
AS Penalty,
[Penalty SS XRef].[SS ID 13] AS [Sort Key]
FROM [Penalty SS XRef]
WHERE ((([Penalty SS XRef].[SS ID 13])>0));

--
Good Luck
BS"D

I use the following query to provide selected input into another query and it
works fine as long as I hardcode the 9919 and 'SS ID 13'. there are other
column headings such as 'SS ID 14', 'SS ID 5' and so forht that I need to use
in the query depending on which event a contestant has entered.

How do I code this to dynamicaly change the column used in the query?

FormA.fld2 contains the value I want to use to select the correct column.

SELECT 9910 AS [Entry ID], [Penalty SS XRef].[Penalty ID], 0 AS Penalty,
[Penalty SS XRef].[SS ID 13] AS [Sort Key]
FROM [Penalty SS XRef]
WHERE ((([Penalty SS XRef].[SS ID 13])>0));

I can switch columns using a string in a VBA form module but I need to do
this within a query.