The 'Select' query sometime does not return all the records with .

[Guest]

The 'Select' query sometime does not return all the records, if used with
'IN' operator, even if that records exists in the table
For example..
'Select * From ModelMaster where model in ('A','B','C')'
This will sometimes returns partial recordset, even if all the records
available. (Frequently in Complex queries). This also occurs with Update
query.

 

[Allen Browne]

The simple example you gave should not fail, so there must be some other
factors at work here.

Find one where it does fail. Is the query based in local Access tables?
Attached Access tables? ODBC-connected tables? ...?

What is the data type of the field in this WHERE clause? If it is a Number
of size Decimal, see:
http://members.iinet.net.au/~allenbrowne/bug-08.html
Could Access be misunderstanding the data type (e.g. if it is a calculated
field)? See:
http://members.iinet.net.au/~allenbrowne/ser-45.html

Are the other phrases in the WHERE clause? Are combinations of AND/OR
bracketed so they are unambiguous?

Are there other joins in the query? Are you using appropriate joins
(INNER/outer)? Are there any joins that involve a literal value rather than
a field from a table? These are known to fail:
http://members.iinet.net.au/~allenbrowne/bug-10.html

Is the box checked under:
Tools | Options | Tables/Queries | ANSI 92?

Does the same query return a different number of records if sorted
differently? That could occur if an index is corrupted. Does a
compact/repair solve the problem?

Hopefully some of those ideas will spark a useful lead for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"Sanjay Kumar Limbikai" <Sanjay Kumar (e-mail address removed)>
wrote in message
news:[email protected]...