L
lwert
We are converting an Access application that was originally written for
Access 97 to the 2003 version. There is a section where we select a list of
checks, display them on a form, and get a total count and sum of amounts. We
have two variable SQL statements that accomplisth this that evaluate to the
following:
The first selects all of the checks and displays them on the form:
SQLCmd = "SELECT CheckID, BankName, CheckCode AS Cd, CheckNumber AS Nmbr,
Payee, DateIssued AS IssuedDate, Format(Amount, '#,##0.00') AS Amt,
CheckStatus AS St, DDate(DateReconciled) AS ReconDate, DDate(UpdateDate) AS
ChangeDate, UpdateBy FROM [Check] INNER JOIN Bank ON Check.BankID =
Bank.BankID WHERE Check.BankID = 2 AND Check.Payee LIKE '*altec*' ;"
Me.lstChecks.RowSource = SQLCmd
The second evaluates the count and sum and is used to generate a DAO
recordset:
SQLCmd = "SELECT Sum(Amount) AS SumOfAmount, Count(Amount) AS CountOfAmount
FROM [Check] INNER JOIN Bank ON Check.BankID = Bank.BankID WHERE Check.BankID
= 2 AND Check.Payee LIKE '*altec*' ;"
Set rstTemp = New ADODB.Recordset
rstTemp.Open SQLCmd, gConn, adOpenKeyset, adLockPessimistic, adCmdText
rstTemp.MoveFirst
TotalAmount = rstTemp("SumOfAmount")
ChecksSelected = rstTemp("CountOfAmount")
With this basis, the problem is that the second Select using the recordset
produces one record with essentially null values (total amount is null and
checks selected is 0). If you consider these two select statements, they are
nearly identical, especially with respect to the Where clause. So why does
one work and the other does not?
A couple of other points: I have tried everything I can to make these
identical, including hard coding the select statements, etc. with no effect.
I have also taken these Select statements and implemented them in Access
queries, and they work just fine. Finally, I should note that these
statements work when the Where clause does not have a "Like" operator in them.
Does anyone have any ideas?
Access 97 to the 2003 version. There is a section where we select a list of
checks, display them on a form, and get a total count and sum of amounts. We
have two variable SQL statements that accomplisth this that evaluate to the
following:
The first selects all of the checks and displays them on the form:
SQLCmd = "SELECT CheckID, BankName, CheckCode AS Cd, CheckNumber AS Nmbr,
Payee, DateIssued AS IssuedDate, Format(Amount, '#,##0.00') AS Amt,
CheckStatus AS St, DDate(DateReconciled) AS ReconDate, DDate(UpdateDate) AS
ChangeDate, UpdateBy FROM [Check] INNER JOIN Bank ON Check.BankID =
Bank.BankID WHERE Check.BankID = 2 AND Check.Payee LIKE '*altec*' ;"
Me.lstChecks.RowSource = SQLCmd
The second evaluates the count and sum and is used to generate a DAO
recordset:
SQLCmd = "SELECT Sum(Amount) AS SumOfAmount, Count(Amount) AS CountOfAmount
FROM [Check] INNER JOIN Bank ON Check.BankID = Bank.BankID WHERE Check.BankID
= 2 AND Check.Payee LIKE '*altec*' ;"
Set rstTemp = New ADODB.Recordset
rstTemp.Open SQLCmd, gConn, adOpenKeyset, adLockPessimistic, adCmdText
rstTemp.MoveFirst
TotalAmount = rstTemp("SumOfAmount")
ChecksSelected = rstTemp("CountOfAmount")
With this basis, the problem is that the second Select using the recordset
produces one record with essentially null values (total amount is null and
checks selected is 0). If you consider these two select statements, they are
nearly identical, especially with respect to the Where clause. So why does
one work and the other does not?
A couple of other points: I have tried everything I can to make these
identical, including hard coding the select statements, etc. with no effect.
I have also taken these Select statements and implemented them in Access
queries, and they work just fine. Finally, I should note that these
statements work when the Where clause does not have a "Like" operator in them.
Does anyone have any ideas?