Re: not matching values in where clause

On Mon, 10 Dec 2007 05:14:01 -0800, NathanG
<NathanG@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Something else is going on. "select * from BudgetFISWorkingData where
Accounts='A1000S'" does return those records with A1000S in the
Accounts field. So if you don't see that, your query is not what you
think it is.
To debug it try this:
dim sql as string
sql = "select * from BudgetFISWorkingData where Accounts = '" &
cmbAccounts.Value & "S'"
Set rst = cn.Execute(sql)
This gives you the opportunity to set a breakpoint and inspect the
value of "sql".
The other debug tool is to use Profiler in your SQL Server program
group. It can show you exactly what is sent to the server.

-Tom.




Hi,

Using a .adp file a form retrieves data based on the selected criteria in
drop down boxes on a form. This is the code

Set rst = cn.Execute("select * from BudgetFISWorkingData where CostCentre =
'" & cmbCostCentre.Value & "' and Accounts = '" & cmbAccounts.Value & "S' and
Scenarios = '" & gstrSelectedScenario & "' and Versions = '" &
gstrSelectedVersion & "' and Years = '" & cmbYears.Value & "'")

What it is doing is setting the record set (rst) to whatever the year,
scenario, version, account and costcentre is. You can see that for " &
cmbAccounts.Value & "S' there is the 'S' on the end. What is happeninf is
that the accounts with an 'S' on the end are not being retrieved. An account
is passed i.e. A1000 but it the criteria should force the where clause to
find 'A1000S'. It doesn't. It just finds A1000 which has no values and
returns zeros if

If rst.EOF Then
txtStatJan = 0
etc etc

How can a character be correctly added in VBA? I have tried different things
but I get a syntax or debug error.

Thanks,

Nathan
.