G
Guest
I am trying to write a query through code that will rotate through the field
names. The code is as follows:
rs.MoveLast
rs.MoveFirst
While Not rs.EOF
strFld = rs("Store_Number")
strFld1 = "tblTimes.[" & strFld & "]"
strSQL = "INSERT INTO tblMaster ( BeginStoreID, EndStoreID,
MilesBetween, TimeBetween )"
strSQL = strSQL & "SELECT " & Chr(34) & strFld & Chr(34) & " AS
StoreNoBeg, "
strSQL = strSQL & "tblMiles.Store_Number AS StoreNoEnd, "
strSQL = strSQL & "IIf(Len(Trim(tblMiles.[" & strFld &
"]))=0,0,CDbl(tblMiles.[" & strFld & "])) AS Mile, "
strSQL = strSQL & ConvertTime(Nz(strFld1, 0)) & " AS TimeBetween "
strSQL = strSQL & "FROM tblMiles INNER JOIN tblTimes ON
tblMiles.Store_Number = tblTimes.Store_Number"
Debug.Print strSQL
db.Execute strSQL
rs.MoveNext
Wend
It's not returning the correct information at the following line:
strSQL = strSQL & ConvertTime(Nz(strFld1, 0)) & " AS TimeBetween "
strFld1 is the name of the field I want the query to return. It looks to me
like it is using the string itself instead of strFld1 as a field name. This
is as opposed to my iif statements which does utilize what is in the variable
strFld as a field name. Any help would be appreciated.
names. The code is as follows:
rs.MoveLast
rs.MoveFirst
While Not rs.EOF
strFld = rs("Store_Number")
strFld1 = "tblTimes.[" & strFld & "]"
strSQL = "INSERT INTO tblMaster ( BeginStoreID, EndStoreID,
MilesBetween, TimeBetween )"
strSQL = strSQL & "SELECT " & Chr(34) & strFld & Chr(34) & " AS
StoreNoBeg, "
strSQL = strSQL & "tblMiles.Store_Number AS StoreNoEnd, "
strSQL = strSQL & "IIf(Len(Trim(tblMiles.[" & strFld &
"]))=0,0,CDbl(tblMiles.[" & strFld & "])) AS Mile, "
strSQL = strSQL & ConvertTime(Nz(strFld1, 0)) & " AS TimeBetween "
strSQL = strSQL & "FROM tblMiles INNER JOIN tblTimes ON
tblMiles.Store_Number = tblTimes.Store_Number"
Debug.Print strSQL
db.Execute strSQL
rs.MoveNext
Wend
It's not returning the correct information at the following line:
strSQL = strSQL & ConvertTime(Nz(strFld1, 0)) & " AS TimeBetween "
strFld1 is the name of the field I want the query to return. It looks to me
like it is using the string itself instead of strFld1 as a field name. This
is as opposed to my iif statements which does utilize what is in the variable
strFld as a field name. Any help would be appreciated.