vba SQL query: <> condition ignored in WHERE clause (1 Viewer)

[Kendall]

Hi,

Maybe this is a stupid question, but I think this should be pretty straight forward. I have some rows in a table, and I want to select all of the rows that matches an given criteria and does NOT match another.

So far I have

SELECT field1...fieldN FROM table WHERE [employeeId] = '" & empId & "' AND [ProductionNumber] <> '" & BlendCode & "';

Since this is a test table, there are only two rows in it. The above query returns both rows, even though it shouldn't, because one of them has the value of BlendCode in ProductionNumber. The same query works properly as a stored procedure. Doesn't matter what order I put the conditions in, and I can't seem to get it to work using brackets or parenthesis either.

System is Access97.

Why is the <> condition being ignored?

MANY MANY thanks,

Kendall

 

[Galaxiom]

Where do EmpID and BlendCode variables come from? Check they are getting their values. My guess is they are empty.

View the text of the query after the values are concatenated to see that it reads as expected.

 

[Kendall]

Both values are set in a 'stub' form that tests a new function. Values for both variables are set in the form, and passed to the function. Neither are empty and the SQL look good. If I use only the <> condition, then the query returns only 1 row as expected. But I can't do that, because there will be multiple rows for different EmployeeIDs.

 

[spikepl]

stringvalues require '' , numbers do not, check the types

and otherwise, debug.print your sql string, stick into the query designer and play with it there

 

[Kendall]

Hello again,

I used the debug.print statement as suggested, and then copy/pasted the result directly into the SQL view of the Query Designer. Upon running the query, I got exactly the result I should get; this result is different from the result I get when the VBA module runs the query.

To remove any ambiguity, please allow me to paste the SQL query.

SELECT ActiveBlends.Company, ActiveBlends.Location, ActiveBlends.ProductionNumber, ActiveBlends.RoutingSequence, ActiveBlends.Ln_No FROM ActiveBlends WHERE ProductionNumber <> 'Blend1' AND EmployeeId = '000081';

(Yes, both fields are text strings.)

And here is where I get even more confused: In a message box, I display the .RecordCount property, it says "2". Ok, so there's two records, right? Well maybe not. I loop through the records, as

Do While Not rsTest.EOF
  msgbox rsTest!ProductionNumber, vbOkOnly
  rsTest.MoveNext
Loop

Which you would expect to loop through twice, and produce two message boxes, since rsTest.RecordCount is 2. Except it only does it once.

So now I'm confused...how many records are there REALLY? .RecordCount says one thing, but looping through says something different.

 

[Galaxiom]

The query itself is only returning one record as you found with the test recordset
If you returned the RecordCount of a form's recordset would include the NewRecord.

Seems like you have better post a cut down sample database that demonstrates the problem.

Compact and zip it before posting.

 

[Kendall]

The query itself is only returning one record as you found with the test recordset
If you returned the RecordCount of a form's recordset would include the NewRecord.

Seems like you have better post a cut down sample database that demonstrates the problem.

Compact and zip it before posting.

I think you hit the nail on the head here. What is happening is that a validation function is calling a lookup function that queries a table for certain records. The lookup function then returns these records as a RecordSet to the validation function. Both of these function reside in a vba module, whereas the call to the validation function happens in a form.

I'll try to put together a smaller database that exhibits the same issue.