SQL - using varibles

[rtdc]

I need to make the field name created in the ‘as’ statement in this sql a variable I can call in RS.???

What I have so far: -

Srch = Me.Select_field & " as " & F1
When run this reads “[Car List]![Make] as Make” which is correct

Set RS = CurrentDb.OpenRecordset("SELECT " & Srch & " FROM [Car List] WHERE ((([Car List]![ID])= 79 ))")
Returns the value “Nissan” which is correct
RS.MoveFirst
Me.Text11 = RS.Make

What I need to do is something like RS.F1 instead of RS.Make so I can access the RS result using the variable F1 not use the same field name every time, in this example it is “Make” but next time it could be “Model”.

Is this possible?

Cheers
:banghead:

 

[Rx_]

Of course the Select would need to include all of the fields.
Then you have a choice on how to reference a field - maybe with a number variable or a string variable.
rs.Fields ("FieldName") ' literal field name
rs.Fields (VariableName) ' put field name in a string variable
rs.Fields(#) ' field number count - always come in handy
rs(#) ' since field is default - the shortcut

 

[rtdc]

Of course the Select would need to include all of the fields.
Then you have a choice on how to reference a field - maybe with a number variable or a string variable.
rs.Fields ("FieldName") ' literal field name
rs.Fields (VariableName) ' put field name in a string variable
rs.Fields(#) ' field number count - always come in handy
rs(#) ' since field is default - the shortcut

Thanks Rx this is exactly what I needed, your a star.