Table does exist, but has error

Databases
1

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

Wend

2

Note the typoe in the name PostitionPosting

3

And that piece of code could benefit greatly by creating a SINGLE SQL statement the accessed both tables at once

SELECT a.ACCOUNT_NO,b.POSDATE
   FROM CLIENTS a, POSITIONPOSTING b
WHERE a.ACCOUNT_NO=b.ACCOUNT_NO
  ORDER BY b.POSDATE

and with a bit more thought, the 3rd SQL statement could be merge into this, and the result would be dozens of times faster and cleaner

4

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…

5

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.

6

The first query order by POSDATE and grabs the first one there to use that as the min

However if there are NO positionposting’s for a client that code will cause an exception as the record set will be empty

7

Hi Norman,

Yes, it will cause an exception… I wrote code to catch that after I got the last part working…