Dynamic selection of fielsd in a query.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve S - 30 Oct 2007 23:49 GMT

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.

Reply to this Message

Ofer Cohen - 31 Oct 2007 00:05 GMT

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));

Signature

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
[quoted text clipped - 12 lines]
> I can switch columns using a string in a VBA form module but I need to do
> this within a query.

Reply to this Message

Steve S - 31 Oct 2007 00:40 GMT

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"

Signature

Steve S

> You can use Forms![FormName]![TextBoxName]
>
[quoted text clipped - 22 lines]
> > I can switch columns using a string in a VBA form module but I need to do
> > this within a query.

Reply to this Message

Ofer Cohen - 31 Oct 2007 19:45 GMT

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"

Signature

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
[quoted text clipped - 35 lines]
> > > I can switch columns using a string in a VBA form module but I need to do
> > > this within a query.

Reply to this Message

KARL DEWEY - 31 Oct 2007 00:36 GMT

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.

Signature

KARL DEWEY
Build a little - Test a little

Reply to this Message