Dynamic RecordSet Field Name

D

Dave

Hopefully I'll get a response to this early. I'm using someone elses
database and I personally wouldn't do this, but this is how my boss
wants to see the data, and this is about the best way that I can think
of doing it. I have a function that uses 2 recordsets, and the
results of both will run a series of update queries. Everything is
fine until I get to the Update string where I have to concatenate 2
recordset values to get the value to incorporate in to the Update.

ie..."TotalsRST![" & PhasesRST![Phase] & "]"

would return

TotalsRST![III (90%)]

which would return a numerical value. How can I get the "TotalsRST!["
& PhasesRST![Phase] & "]" to return the value instead of TotalsRST!
[III (90%)]?




****Function****
Public Function CurPV()
1 On Error GoTo Err_df
5 Dim TotalsSQL As String
10 Dim PhasesSQL As String
15 Dim I As Integer
20 Dim A As String
25 Dim TotalsRST As ADODB.Recordset
30 Dim PhasesRST As ADODB.Recordset
35 Dim con As ADODB.Connection
40 Set TotalsRST = New ADODB.Recordset
45 Set PhasesRST = New ADODB.Recordset
50 Set con = CurrentProject.Connection
55 TotalsSQL = "SELECT p.ProjectID, '0' AS [LRE/PD&E], " & _
"IIf(s2.[AMOUNT (a)]<>0 And s1.[AMOUNT (a)]<>0,(s2.
[AMOUNT (a)]/s1.[AMOUNT (a)])-1,'') AS [I (30%)], " & _
"IIf(s3.[AMOUNT (a)]<>0,IIf(s2.[AMOUNT (a)]<>0,(s3.
[AMOUNT (a)]/s2.[AMOUNT (a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s3.[AMOUNT
(a)]/s1.[AMOUNT (a)])-1,'')),'') AS [II (60%)], " & _
"IIf(s4.[AMOUNT (a)]<>0,IIf(s3.[AMOUNT (a)]<>0,(s4.
[AMOUNT (a)]/s3.[AMOUNT (a)])-1,IIf(s2.[AMOUNT (a)]<>0,(s4.[AMOUNT
(a)]/s2.[AMOUNT (a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s4.[AMOUNT (a)]/s1.
[AMOUNT (a)])-1,''))),'') AS [III (90%)], " & _
"IIf(s5.[AMOUNT (a)]<>0,IIf(s4.[AMOUNT (a)]<>0,(s5.
[AMOUNT (a)]/s4.[AMOUNT (a)])-1,IIf(s3.[AMOUNT (a)]<>0,(s5.[AMOUNT
(a)]/s3.[AMOUNT (a)])-1,IIf(s2.[AMOUNT (a)]<>0,(s5.[AMOUNT (a)]/s2.
[AMOUNT (a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s5.[AMOUNT (a)]/s1.[AMOUNT
(a)])-1,'')))),'') AS [IV (PS&E)], " & _
"IIf(s6.[AMOUNT (a)]<>0,IIf(s5.[AMOUNT (a)]<>0,(s6.
[AMOUNT (a)]/s5.[AMOUNT (a)])-1,IIf(s4.[AMOUNT (a)]<>0,(s6.[AMOUNT
(a)]/s4.[AMOUNT (a)])-1,IIf(s3.[AMOUNT (a)]<>0,(s6.[AMOUNT (a)]/s3.
[AMOUNT (a)])-1,IIf(s2.[AMOUNT (a)]<>0,(s6.[AMOUNT (a)]/s2.[AMOUNT
(a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s6.[AMOUNT (a)]/s1.[AMOUNT
(a)])-1,''))))),'') AS [Final] " & _
"FROM (((((Projects AS p LEFT JOIN Phase AS s1 ON
p.ProjectID = s1.ProjectID) LEFT JOIN Phase AS s2 ON p.projectID =
s2.projectID) LEFT JOIN Phase AS s3 ON p.projectID = s3.projectID)
LEFT JOIN Phase AS s4 ON p.projectID = s4.projectID) LEFT JOIN Phase
AS s5 ON p.projectID = s5.projectID) INNER JOIN Phase AS s6 ON
p.projectID = s6.projectID " & _
"WHERE (((p.ProjectID)=" & Forms!Projects!projectID &
") AND ((s2.PHASES)='I (30%)') AND ((s1.Phases)='LRE/PD&E') AND
((s3.PHASES)='II (60%)') AND ((s4.PHASES)='III (90%)') AND ((s5.PHASES)
='IV (PS&E)') AND ((s6.PHASES)='Final'));"
60 PhasesSQL = "SELECT IndexPhases.Phase FROM IndexPhases;"

65 TotalsRST.Open TotalsSQL, con, adOpenStatic

70 PhasesRST.Open PhasesSQL, con, adOpenStatic
75 For I = 0 To PhasesRST.RecordCount - 1
80 DoCmd.RunSQL ("UPDATE Phase Set Phase.[Current Phase
Amount vs Previous (%)] = " & "TotalsRST![" & PhasesRST![Phase] & "]"
& " WHERE (((Phase.projectID)=39) AND ((Phase.PHASES)='" & PhasesRST!
[Phase] & "'));")
85 PhasesRST.MoveNext
90 Next I
95 TotalsRST.Close
100 PhasesRST.Close
Exit_df:
105 Exit Function
Err_df:
110 MsgBox Err.Number & " " & Err.DESCRIPTION & Chr(13) & Chr(10)
& "CurPV Line No: " & Erl
115 Resume Exit_df
End Function
 
R

RoyVidar

Dave said:
Hopefully I'll get a response to this early. I'm using someone elses
database and I personally wouldn't do this, but this is how my boss
wants to see the data, and this is about the best way that I can
think of doing it. I have a function that uses 2 recordsets, and the
results of both will run a series of update queries. Everything is
fine until I get to the Update string where I have to concatenate 2
recordset values to get the value to incorporate in to the Update.

ie..."TotalsRST![" & PhasesRST![Phase] & "]"

would return

TotalsRST![III (90%)]

which would return a numerical value. How can I get the
"TotalsRST![" & PhasesRST![Phase] & "]" to return the value instead
of TotalsRST! [III (90%)]?




****Function****
Public Function CurPV()
1 On Error GoTo Err_df
5 Dim TotalsSQL As String
10 Dim PhasesSQL As String
15 Dim I As Integer
20 Dim A As String
25 Dim TotalsRST As ADODB.Recordset
30 Dim PhasesRST As ADODB.Recordset
35 Dim con As ADODB.Connection
40 Set TotalsRST = New ADODB.Recordset
45 Set PhasesRST = New ADODB.Recordset
50 Set con = CurrentProject.Connection
55 TotalsSQL = "SELECT p.ProjectID, '0' AS [LRE/PD&E], " & _
"IIf(s2.[AMOUNT (a)]<>0 And s1.[AMOUNT (a)]<>0,(s2.
[AMOUNT (a)]/s1.[AMOUNT (a)])-1,'') AS [I (30%)], " & _
"IIf(s3.[AMOUNT (a)]<>0,IIf(s2.[AMOUNT (a)]<>0,(s3.
[AMOUNT (a)]/s2.[AMOUNT (a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s3.[AMOUNT
(a)]/s1.[AMOUNT (a)])-1,'')),'') AS [II (60%)], " & _
"IIf(s4.[AMOUNT (a)]<>0,IIf(s3.[AMOUNT (a)]<>0,(s4.
[AMOUNT (a)]/s3.[AMOUNT (a)])-1,IIf(s2.[AMOUNT (a)]<>0,(s4.[AMOUNT
(a)]/s2.[AMOUNT (a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s4.[AMOUNT (a)]/s1.
[AMOUNT (a)])-1,''))),'') AS [III (90%)], " & _
"IIf(s5.[AMOUNT (a)]<>0,IIf(s4.[AMOUNT (a)]<>0,(s5.
[AMOUNT (a)]/s4.[AMOUNT (a)])-1,IIf(s3.[AMOUNT (a)]<>0,(s5.[AMOUNT
(a)]/s3.[AMOUNT (a)])-1,IIf(s2.[AMOUNT (a)]<>0,(s5.[AMOUNT (a)]/s2.
[AMOUNT (a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s5.[AMOUNT (a)]/s1.[AMOUNT
(a)])-1,'')))),'') AS [IV (PS&E)], " & _
"IIf(s6.[AMOUNT (a)]<>0,IIf(s5.[AMOUNT (a)]<>0,(s6.
[AMOUNT (a)]/s5.[AMOUNT (a)])-1,IIf(s4.[AMOUNT (a)]<>0,(s6.[AMOUNT
(a)]/s4.[AMOUNT (a)])-1,IIf(s3.[AMOUNT (a)]<>0,(s6.[AMOUNT (a)]/s3.
[AMOUNT (a)])-1,IIf(s2.[AMOUNT (a)]<>0,(s6.[AMOUNT (a)]/s2.[AMOUNT
(a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s6.[AMOUNT (a)]/s1.[AMOUNT
(a)])-1,''))))),'') AS [Final] " & _
"FROM (((((Projects AS p LEFT JOIN Phase AS s1 ON
p.ProjectID = s1.ProjectID) LEFT JOIN Phase AS s2 ON p.projectID =
s2.projectID) LEFT JOIN Phase AS s3 ON p.projectID = s3.projectID)
LEFT JOIN Phase AS s4 ON p.projectID = s4.projectID) LEFT JOIN Phase
AS s5 ON p.projectID = s5.projectID) INNER JOIN Phase AS s6 ON
p.projectID = s6.projectID " & _
"WHERE (((p.ProjectID)=" & Forms!Projects!projectID &
") AND ((s2.PHASES)='I (30%)') AND ((s1.Phases)='LRE/PD&E') AND
((s3.PHASES)='II (60%)') AND ((s4.PHASES)='III (90%)') AND
((s5.PHASES) ='IV (PS&E)') AND ((s6.PHASES)='Final'));"
60 PhasesSQL = "SELECT IndexPhases.Phase FROM IndexPhases;"

65 TotalsRST.Open TotalsSQL, con, adOpenStatic

70 PhasesRST.Open PhasesSQL, con, adOpenStatic
75 For I = 0 To PhasesRST.RecordCount - 1
80 DoCmd.RunSQL ("UPDATE Phase Set Phase.[Current Phase
Amount vs Previous (%)] = " & "TotalsRST![" & PhasesRST![Phase] &
"]" & " WHERE (((Phase.projectID)=39) AND ((Phase.PHASES)='" &
PhasesRST! [Phase] & "'));")
85 PhasesRST.MoveNext
90 Next I
95 TotalsRST.Close
100 PhasesRST.Close
Exit_df:
105 Exit Function
Err_df:
110 MsgBox Err.Number & " " & Err.DESCRIPTION & Chr(13) &
Chr(10) & "CurPV Line No: " & Erl
115 Resume Exit_df
End Function
Click to expand...

I'm guessing what you're after, is this

TotalsRST.Fields(PhasesRST![Phase]).Value
 
D

Dave

Thanks for the Very quick reply.

TotalsRST.Fields(PhasesRST![Phase]).Value produces the error "3265
Item cannot be found in the collection corresponding to the request
name or ordinal."





Dave said:
Hopefully I'll get a response to this early.  I'm using someone elses
database and I personally wouldn't do this, but this is how my boss
wants to see the data, and this is about the best way that I can
think of doing it.  I have a function that uses 2 recordsets, and the
results of both will run a series of update queries.  Everything is
fine until I get to the Update string where I have to concatenate 2
recordset values to get the value to incorporate in to the Update.
Click to expand...
ie..."TotalsRST![" & PhasesRST![Phase] & "]"
Click to expand...
would return
Click to expand...
     TotalsRST![III (90%)]
Click to expand...
which would return a numerical value.  How can I get the
"TotalsRST![" & PhasesRST![Phase] & "]"  to return the value instead
of TotalsRST! [III (90%)]?
Click to expand...
****Function****
Public Function CurPV()
1       On Error GoTo Err_df
5       Dim TotalsSQL As String
10      Dim PhasesSQL As String
15      Dim I As Integer
20      Dim A As String
25      Dim TotalsRST As ADODB.Recordset
30      Dim PhasesRST As ADODB.Recordset
35      Dim con As ADODB.Connection
40      Set TotalsRST = New ADODB.Recordset
45      Set PhasesRST = New ADODB.Recordset
50      Set con = CurrentProject.Connection
55      TotalsSQL = "SELECT p.ProjectID, '0' AS [LRE/PD&E], " &_
                "IIf(s2.[AMOUNT (a)]<>0 And s1.[AMOUNT (a)]<>0,(s2.
[AMOUNT (a)]/s1.[AMOUNT (a)])-1,'') AS [I (30%)], " & _
                "IIf(s3.[AMOUNT (a)]<>0,IIf(s2.[AMOUNT (a)]<>0,(s3.
[AMOUNT (a)]/s2.[AMOUNT (a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s3.[AMOUNT
(a)]/s1.[AMOUNT (a)])-1,'')),'') AS [II (60%)], " & _
                "IIf(s4.[AMOUNT (a)]<>0,IIf(s3.[AMOUNT (a)]<>0,(s4.
[AMOUNT (a)]/s3.[AMOUNT (a)])-1,IIf(s2.[AMOUNT (a)]<>0,(s4.[AMOUNT
(a)]/s2.[AMOUNT (a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s4.[AMOUNT (a)]/s1.
[AMOUNT (a)])-1,''))),'') AS [III (90%)], " & _
                "IIf(s5.[AMOUNT (a)]<>0,IIf(s4.[AMOUNT (a)]<>0,(s5.
[AMOUNT (a)]/s4.[AMOUNT (a)])-1,IIf(s3.[AMOUNT (a)]<>0,(s5.[AMOUNT
(a)]/s3.[AMOUNT (a)])-1,IIf(s2.[AMOUNT (a)]<>0,(s5.[AMOUNT (a)]/s2.
[AMOUNT (a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s5.[AMOUNT (a)]/s1.[AMOUNT
(a)])-1,'')))),'') AS [IV (PS&E)], " & _
                "IIf(s6.[AMOUNT (a)]<>0,IIf(s5.[AMOUNT (a)]<>0,(s6.
[AMOUNT (a)]/s5.[AMOUNT (a)])-1,IIf(s4.[AMOUNT (a)]<>0,(s6.[AMOUNT
(a)]/s4.[AMOUNT (a)])-1,IIf(s3.[AMOUNT (a)]<>0,(s6.[AMOUNT (a)]/s3.
[AMOUNT (a)])-1,IIf(s2.[AMOUNT (a)]<>0,(s6.[AMOUNT (a)]/s2.[AMOUNT
(a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s6.[AMOUNT (a)]/s1.[AMOUNT
(a)])-1,''))))),'') AS [Final] " & _
                "FROM (((((Projects AS p LEFT JOIN Phase AS s1 ON
p.ProjectID = s1.ProjectID) LEFT JOIN Phase AS s2 ON p.projectID =
s2.projectID) LEFT JOIN Phase AS s3 ON p.projectID = s3.projectID)
LEFT JOIN Phase AS s4 ON p.projectID = s4.projectID) LEFT JOIN Phase
AS s5 ON p.projectID = s5.projectID) INNER JOIN Phase AS s6 ON
p.projectID = s6.projectID " & _
                "WHERE (((p.ProjectID)=" & Forms!Projects!projectID &
") AND ((s2.PHASES)='I (30%)') AND ((s1.Phases)='LRE/PD&E') AND
((s3.PHASES)='II (60%)') AND ((s4.PHASES)='III (90%)') AND
((s5.PHASES) ='IV (PS&E)') AND ((s6.PHASES)='Final'));"
60      PhasesSQL = "SELECT IndexPhases.Phase FROM IndexPhases;"
Click to expand...
65      TotalsRST.Open TotalsSQL, con, adOpenStatic
Click to expand...
70      PhasesRST.Open PhasesSQL, con, adOpenStatic
75      For I = 0 To PhasesRST.RecordCount - 1
80          DoCmd.RunSQL ("UPDATE Phase Set Phase.[Current Phase
Amount vs Previous  (%)] = " & "TotalsRST![" & PhasesRST![Phase] &
"]" & " WHERE (((Phase.projectID)=39) AND ((Phase.PHASES)='" &
PhasesRST! [Phase] & "'));")
85          PhasesRST.MoveNext
90      Next I
95      TotalsRST.Close
100     PhasesRST.Close
Exit_df:
105     Exit Function
Err_df:
110     MsgBox Err.Number & "  " & Err.DESCRIPTION & Chr(13) &
Chr(10) & "CurPV Line No: " & Erl
115     Resume Exit_df
End Function
Click to expand...

I'm guessing what you're after, is this

TotalsRST.Fields(PhasesRST![Phase]).Value
Click to expand...
 
D

Dave

This worked....


Dim A as string
A = PhasesRST![Phase]
Docmd.RunSQL("UPDATE Phase Set Phase.[Current Phase Amount vs
Previous (%)] = " & TotalsRST.Fields(A).Value & " WHERE
(((Phase.projectID)=39) AND ((Phase.PHASES)='" & PhasesRST![Phase] &
"'));")





Dave said:
Hopefully I'll get a response to this early.  I'm using someone elses
database and I personally wouldn't do this, but this is how my boss
wants to see the data, and this is about the best way that I can
think of doing it.  I have a function that uses 2 recordsets, and the
results of both will run a series of update queries.  Everything is
fine until I get to the Update string where I have to concatenate 2
recordset values to get the value to incorporate in to the Update.
Click to expand...
ie..."TotalsRST![" & PhasesRST![Phase] & "]"
Click to expand...
would return
Click to expand...
     TotalsRST![III (90%)]
Click to expand...
which would return a numerical value.  How can I get the
"TotalsRST![" & PhasesRST![Phase] & "]"  to return the value instead
of TotalsRST! [III (90%)]?
Click to expand...
****Function****
Public Function CurPV()
1       On Error GoTo Err_df
5       Dim TotalsSQL As String
10      Dim PhasesSQL As String
15      Dim I As Integer
20      Dim A As String
25      Dim TotalsRST As ADODB.Recordset
30      Dim PhasesRST As ADODB.Recordset
35      Dim con As ADODB.Connection
40      Set TotalsRST = New ADODB.Recordset
45      Set PhasesRST = New ADODB.Recordset
50      Set con = CurrentProject.Connection
55      TotalsSQL = "SELECT p.ProjectID, '0' AS [LRE/PD&E], " &_
                "IIf(s2.[AMOUNT (a)]<>0 And s1.[AMOUNT (a)]<>0,(s2.
[AMOUNT (a)]/s1.[AMOUNT (a)])-1,'') AS [I (30%)], " & _
                "IIf(s3.[AMOUNT (a)]<>0,IIf(s2.[AMOUNT (a)]<>0,(s3.
[AMOUNT (a)]/s2.[AMOUNT (a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s3.[AMOUNT
(a)]/s1.[AMOUNT (a)])-1,'')),'') AS [II (60%)], " & _
                "IIf(s4.[AMOUNT (a)]<>0,IIf(s3.[AMOUNT (a)]<>0,(s4.
[AMOUNT (a)]/s3.[AMOUNT (a)])-1,IIf(s2.[AMOUNT (a)]<>0,(s4.[AMOUNT
(a)]/s2.[AMOUNT (a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s4.[AMOUNT (a)]/s1.
[AMOUNT (a)])-1,''))),'') AS [III (90%)], " & _
                "IIf(s5.[AMOUNT (a)]<>0,IIf(s4.[AMOUNT (a)]<>0,(s5.
[AMOUNT (a)]/s4.[AMOUNT (a)])-1,IIf(s3.[AMOUNT (a)]<>0,(s5.[AMOUNT
(a)]/s3.[AMOUNT (a)])-1,IIf(s2.[AMOUNT (a)]<>0,(s5.[AMOUNT (a)]/s2.
[AMOUNT (a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s5.[AMOUNT (a)]/s1.[AMOUNT
(a)])-1,'')))),'') AS [IV (PS&E)], " & _
                "IIf(s6.[AMOUNT (a)]<>0,IIf(s5.[AMOUNT (a)]<>0,(s6.
[AMOUNT (a)]/s5.[AMOUNT (a)])-1,IIf(s4.[AMOUNT (a)]<>0,(s6.[AMOUNT
(a)]/s4.[AMOUNT (a)])-1,IIf(s3.[AMOUNT (a)]<>0,(s6.[AMOUNT (a)]/s3.
[AMOUNT (a)])-1,IIf(s2.[AMOUNT (a)]<>0,(s6.[AMOUNT (a)]/s2.[AMOUNT
(a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s6.[AMOUNT (a)]/s1.[AMOUNT
(a)])-1,''))))),'') AS [Final] " & _
                "FROM (((((Projects AS p LEFT JOIN Phase AS s1 ON
p.ProjectID = s1.ProjectID) LEFT JOIN Phase AS s2 ON p.projectID =
s2.projectID) LEFT JOIN Phase AS s3 ON p.projectID = s3.projectID)
LEFT JOIN Phase AS s4 ON p.projectID = s4.projectID) LEFT JOIN Phase
AS s5 ON p.projectID = s5.projectID) INNER JOIN Phase AS s6 ON
p.projectID = s6.projectID " & _
                "WHERE (((p.ProjectID)=" & Forms!Projects!projectID &
") AND ((s2.PHASES)='I (30%)') AND ((s1.Phases)='LRE/PD&E') AND
((s3.PHASES)='II (60%)') AND ((s4.PHASES)='III (90%)') AND
((s5.PHASES) ='IV (PS&E)') AND ((s6.PHASES)='Final'));"
60      PhasesSQL = "SELECT IndexPhases.Phase FROM IndexPhases;"
Click to expand...
65      TotalsRST.Open TotalsSQL, con, adOpenStatic
Click to expand...
70      PhasesRST.Open PhasesSQL, con, adOpenStatic
75      For I = 0 To PhasesRST.RecordCount - 1
80          DoCmd.RunSQL ("UPDATE Phase Set Phase.[Current Phase
Amount vs Previous  (%)] = " & "TotalsRST![" & PhasesRST![Phase] &
"]" & " WHERE (((Phase.projectID)=39) AND ((Phase.PHASES)='" &
PhasesRST! [Phase] & "'));")
85          PhasesRST.MoveNext
90      Next I
95      TotalsRST.Close
100     PhasesRST.Close
Exit_df:
105     Exit Function
Err_df:
110     MsgBox Err.Number & "  " & Err.DESCRIPTION & Chr(13) &
Chr(10) & "CurPV Line No: " & Erl
115     Resume Exit_df
End Function
Click to expand...

I'm guessing what you're after, is this

TotalsRST.Fields(PhasesRST![Phase]).Value
Click to expand...
 
K

Ken Sheridan

Try this:

TotalsRST.Fields(PhasesRST!Phase)

Ken Sheridan
Stafford, England

Dave said:
Hopefully I'll get a response to this early. I'm using someone elses
database and I personally wouldn't do this, but this is how my boss
wants to see the data, and this is about the best way that I can think
of doing it. I have a function that uses 2 recordsets, and the
results of both will run a series of update queries. Everything is
fine until I get to the Update string where I have to concatenate 2
recordset values to get the value to incorporate in to the Update.

ie..."TotalsRST![" & PhasesRST![Phase] & "]"

would return

TotalsRST![III (90%)]

which would return a numerical value. How can I get the "TotalsRST!["
& PhasesRST![Phase] & "]" to return the value instead of TotalsRST!
[III (90%)]?




****Function****
Public Function CurPV()
1 On Error GoTo Err_df
5 Dim TotalsSQL As String
10 Dim PhasesSQL As String
15 Dim I As Integer
20 Dim A As String
25 Dim TotalsRST As ADODB.Recordset
30 Dim PhasesRST As ADODB.Recordset
35 Dim con As ADODB.Connection
40 Set TotalsRST = New ADODB.Recordset
45 Set PhasesRST = New ADODB.Recordset
50 Set con = CurrentProject.Connection
55 TotalsSQL = "SELECT p.ProjectID, '0' AS [LRE/PD&E], " & _
"IIf(s2.[AMOUNT (a)]<>0 And s1.[AMOUNT (a)]<>0,(s2.
[AMOUNT (a)]/s1.[AMOUNT (a)])-1,'') AS [I (30%)], " & _
"IIf(s3.[AMOUNT (a)]<>0,IIf(s2.[AMOUNT (a)]<>0,(s3.
[AMOUNT (a)]/s2.[AMOUNT (a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s3.[AMOUNT
(a)]/s1.[AMOUNT (a)])-1,'')),'') AS [II (60%)], " & _
"IIf(s4.[AMOUNT (a)]<>0,IIf(s3.[AMOUNT (a)]<>0,(s4.
[AMOUNT (a)]/s3.[AMOUNT (a)])-1,IIf(s2.[AMOUNT (a)]<>0,(s4.[AMOUNT
(a)]/s2.[AMOUNT (a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s4.[AMOUNT (a)]/s1.
[AMOUNT (a)])-1,''))),'') AS [III (90%)], " & _
"IIf(s5.[AMOUNT (a)]<>0,IIf(s4.[AMOUNT (a)]<>0,(s5.
[AMOUNT (a)]/s4.[AMOUNT (a)])-1,IIf(s3.[AMOUNT (a)]<>0,(s5.[AMOUNT
(a)]/s3.[AMOUNT (a)])-1,IIf(s2.[AMOUNT (a)]<>0,(s5.[AMOUNT (a)]/s2.
[AMOUNT (a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s5.[AMOUNT (a)]/s1.[AMOUNT
(a)])-1,'')))),'') AS [IV (PS&E)], " & _
"IIf(s6.[AMOUNT (a)]<>0,IIf(s5.[AMOUNT (a)]<>0,(s6.
[AMOUNT (a)]/s5.[AMOUNT (a)])-1,IIf(s4.[AMOUNT (a)]<>0,(s6.[AMOUNT
(a)]/s4.[AMOUNT (a)])-1,IIf(s3.[AMOUNT (a)]<>0,(s6.[AMOUNT (a)]/s3.
[AMOUNT (a)])-1,IIf(s2.[AMOUNT (a)]<>0,(s6.[AMOUNT (a)]/s2.[AMOUNT
(a)])-1,IIf(s1.[AMOUNT (a)]<>0,(s6.[AMOUNT (a)]/s1.[AMOUNT
(a)])-1,''))))),'') AS [Final] " & _
"FROM (((((Projects AS p LEFT JOIN Phase AS s1 ON
p.ProjectID = s1.ProjectID) LEFT JOIN Phase AS s2 ON p.projectID =
s2.projectID) LEFT JOIN Phase AS s3 ON p.projectID = s3.projectID)
LEFT JOIN Phase AS s4 ON p.projectID = s4.projectID) LEFT JOIN Phase
AS s5 ON p.projectID = s5.projectID) INNER JOIN Phase AS s6 ON
p.projectID = s6.projectID " & _
"WHERE (((p.ProjectID)=" & Forms!Projects!projectID &
") AND ((s2.PHASES)='I (30%)') AND ((s1.Phases)='LRE/PD&E') AND
((s3.PHASES)='II (60%)') AND ((s4.PHASES)='III (90%)') AND ((s5.PHASES)
='IV (PS&E)') AND ((s6.PHASES)='Final'));"
60 PhasesSQL = "SELECT IndexPhases.Phase FROM IndexPhases;"

65 TotalsRST.Open TotalsSQL, con, adOpenStatic

70 PhasesRST.Open PhasesSQL, con, adOpenStatic
75 For I = 0 To PhasesRST.RecordCount - 1
80 DoCmd.RunSQL ("UPDATE Phase Set Phase.[Current Phase
Amount vs Previous (%)] = " & "TotalsRST![" & PhasesRST![Phase] & "]"
& " WHERE (((Phase.projectID)=39) AND ((Phase.PHASES)='" & PhasesRST!
[Phase] & "'));")
85 PhasesRST.MoveNext
90 Next I
95 TotalsRST.Close
100 PhasesRST.Close
Exit_df:
105 Exit Function
Err_df:
110 MsgBox Err.Number & " " & Err.DESCRIPTION & Chr(13) & Chr(10)
& "CurPV Line No: " & Erl
115 Resume Exit_df
End Function
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to preserve the value of a variable on the same aspx page? 8
Group and SUM 4
Dropping Zeros from Avg 8
Help writing a query 4
Search multiple values & return single value - seperate worksheets 4
How doable Using Excel? 2
2 sheets and a select 2
Calculating formula ..circular references 1

Top