Inconsistent recordcount in query

[dmkAlex]

I have an adodc object in my form. When I execute the following query statement using openrecordset statement, I have 400 records. I got the same number of record when I use the adodc.recordsource/ adodc.refresh statements.

SELECT Inventory.Description, Sales.ItemNo, Sales.SalesDate, Sales.Qty, Sales.Amount, Sales.SH, Sales.BuyerID, Sales.PaidDate, Sales.Shipto, Sales.npbDate, Sales.Remark FROM Sales INNER JOIN Inventory ON Sales.InvCode = Inventory.InvCode Where (((Sales.SalesDate) >= Date() - 3)) ORDER BY Sales.SalesDate, Sales.ItemNo, Sales.BuyerID;

However, when I added the folllowing condition:

And ((Sales.[ItemNo]) Like '*202549*')

The query definition becomes:

SELECT Inventory.Description, Sales.ItemNo, Sales.SalesDate, Sales.Qty, Sales.Amount, Sales.SH, Sales.BuyerID, Sales.PaidDate, Sales.Shipto, Sales.npbDate, Sales.Remark FROM Sales INNER JOIN Inventory ON Sales.InvCode = Inventory.InvCode Where (((Sales.SalesDate) >= Date() - 3)) And ((Sales.[ItemNo]) Like '*202549*') ORDER BY Sales.SalesDate, Sales.ItemNo, Sales.BuyerID;

And my openrecordset statement returns 1 record, while the adodc method return no record.

Why is the inconsistency? What did I do wrong?

Thanks.

Alex

 

[dmkAlex]

I did further testing. It seems that the adodc.recordsource doesn't like the keyworkd "LIKE".

Would anyone know a work around?

Thanks.

Alex

 

[ArtieChoke]

You don't say what database you're using, but SQL Server uses %, not * as a wildcard. Not sure whether that's a standard or not...

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[dmkAlex]

The database is Access. I am using vb6 sp3.
Thx

fun

 

[JoeAtWork]

Try using % instead of *. ADO usually uses the more standard SQL. I think behind the scenes the OLEDB provider for Access will translate the % back to a *.

Also, be aware that the RecordCount property is not always accurate, depending on Recordset type, database, etc.

 

[dmkAlex]

Tried that and still doesn't work.

I wonder if it is the "LIKE" operator that ADO doesn't like.

Any suggestion what else and where else I should try?

Thanks.

Alex

 

[johnwm]

Is ItemNo a numeric field or a text field? As far as I know 'LIKE' only works on text

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[dmkAlex]

OK, with all the input and hours of testing, here is what I find out:

#1. There is inconsistency betwee Access SQL and ADO SQL regarding LIKE. Acces likes "*", and ADO likes "%".

#2. LIKE works with string only, not numeric.

#3. For single character wildcat, use one %, otherwise, use %% in front and between the string.

Here brings up another question: in a numeric field like ItemNo, which normally is a 10-digit number, how do I find the record by inputting only a partial number. For example, ItemNo is 123456890, how can I enter 23456 and find a match?

Thanks.

Alex

 

[gmmastros]

I'm better at SQL Server, but in many instances, the SQL is similar. In SQL Server, you could do...

And Convert(VarChar(10), Sales.ItemNo) Like '%202549%'

In this example, you are using the like operator to compare strings because you converted the integer value to a varchar before doing the comparison.

I know Access must have a way to convert an integer to a string representation of the integer value. STR ?

Try...

[blue]And Str(Sales.ItemNo) Like '%202549%'[/blue]


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dmkAlex]

Exactly what I want.
Thanks a lot.

Alex