Ok, I have a section of code that access a table called PositionPosting in two lines. In the first set of code it finds the table and gets the data, in the second line it gives an error that the table does not exist. I have double checked the code, and have run the line in a shell window and it does return data… what is going wrong here:
Dim tAccountNo, tPosDate As String
Dim AcctVal as Single
Dim rstSolClients as RecordSet = data.SQLSelect(“Select * from CLIENTS where SolicitNumber = '” + str(eID) + “’”)
While not rstSolClients.EOF
tAccountNo = rstSolClients.Field(“ACCOUNT_NO”).StringValue
Dim rstClientPos As RecordSet = data.SQLSelect(“Select * from PositionPosting where ACCOUNT_NO = '” + tAccountNo + “’ order by POSDATE”)
tPosDate = rstClientPos.Field(“POSDATE”).StringValue
strSql = “Select *, SUM(AMOUNT) As AcctVal from PostitionPosting where ACCOUNT_NO = '” +tAccountNo + “’ and POSDATE = '” + tPosDate + “’”
Dim rstAcctVal as Recordset = data.SQLSelect(strSql)
MsgBox data.ErrorMessage
If data.ErrorMessage = "" then
AcctVal = rstAcctVal.Field("AcctVal").Value
End If
MsgBox str(AcctVal)
rstSolClients.MoveNext
I must have read over that line for an hour… and I still missed a stupid typo… thanks Norman… It now works…
Dave… thank you for the code input… the reason I have it as two lines is that I need to determine the earliest date that the account number has data in positionpositing, then add up the values for that account number for that date only. I think your code would all the values for all the dates that had values for that account number…
I did forget to include your ID value, but I don’t see anything that determines a MIN date value, and even so that too can be accomodated in a single SQL query.