Building a query

[CD Tom]

I want to build a query using VBA but I need to change some fields depending on number here's the original code

            i = rsparams("Lroundprinted") - 1
            ScoreCards1.SQL = "SELECT Scores.CFDNumber, Master.Alias, Master.state, Master.Extra_yes4, Scores.ShooterNumber, Scores.RandLaneNumber1, Scores.PageNumber1 as PageN, Scores.LaneNumber1 as Lane, Scores.rndNumber1 as Rnd, Params.Club_Name, " & _
            "Params.Match_Name, Scores.XOut, Scores.Range1 as Range, Scores.GroupLadies, Scores.GroupYouth, Scores.RndScored FROM Params, Master INNER JOIN Scores ON Master.CFDNumber = Scores.CFDNumber WHERE (((Scores.XOut) Is Null or Scores.Xout = "" "") and ((Scores.GroupYouth) = true)) " & _
            "ORDER BY scores.randlanenumber1, Scores.PageNumber1, Scores.Range1, Scores.LaneNumber1"

you will notices the column scores.randlane number1 I need to be able to change the 1 to the value of i. I've done this before but not in a query build. What's the secret to changing this.

Thanks for all your help this last year, and have a great Christmas and a safe New Year.

[Stewart Ross]

It's straightforward to change the fixed 1 at the end of the name to the value of the parameter variable. For example, in the ordering section of the SQL:

"ORDER BY scores.randlanenumber" & i & ", Scores.PageNumber1, Scores.Range1, Scores.LaneNumber1"

As you have to do this each time you refer to the field a more general solution is to introduce another variable which you set once only:

Dim strLaneField as String

i = rsparams("Lroundprinted") - 1
strLaneField = "Scores.LaneNumber" & i

ScoreCards1.SQL =  
  "SELECT " & _
    "Scores.CFDNumber, Master.Alias, Master.state, " & 
    "Master.Extra_yes4, Scores.ShooterNumber, " & _  
     strLaneField & ", " & _
    "Scores.PageNumber1 as PageN, Scores.LaneNumber1 as Lane, " & _
    "Scores.rndNumber1 as Rnd, Params.Club_Name, " & _
    "Params.Match_Name, Scores.XOut, " & _
    "Scores.Range1 as Range, Scores.GroupLadies, " & _
    "Scores.GroupYouth, Scores.RndScored " & _
  "FROM " & _
    "Params, Master INNER JOIN Scores ON Master.CFDNumber = Scores.CFDNumber " & _
  "WHERE " & _
    "(((Scores.XOut) Is Null or Scores.Xout = "" "") and ((Scores.GroupYouth) = true)) " & _
  "ORDER BY " & _
    "scores.randlanenumber1, Scores.PageNumber1, Scores.Range1, " & _
     strLaneField

-Stewart