Data missing from listbox but shows in underlying query????

  • Thread starter Thread starter Darleen
  • Start date Start date
D

Darleen

Very strange problem...
I noticed that one of my listboxes was missing some records, and so I
checked its underlying query (which is assigned through the
listbox.rowsource VB property) to find the problem. The strange thing
is that the missing records ARE present in the underlying query
results!

I've flipped views back and forth thinking I must be missing
something, but it's clear as day - when I look at the listbox, there
are 3 records (2 records missing). When I open the SQL statement
directly behind the listbox by pressing the 3 dots next to the
rowsource field in the property sheet and running that query, all 5
records are there.

The listbox has multiple "OR" conditions defined in the criteria. It
is always the same 2 categories of the criteria that are missing.
Here is the SQL behind the listbox:

SELECT tblPlanRevisions.RevisionID,
IIf([revlevel]=1,'M.Pln',IIf([revlevel]=2,'Div',IIf([revlevel]=3,'Comm',IIf([revlevel]=4,'C.Pln','Corp'))))
AS Scope, Format([revdate],'mm/dd/yy') AS [Rev Date],
tblPlanRevisions.Description
FROM tblPlanRevisions
WHERE (((tblPlanRevisions.RevLevel)=4) AND
((tblPlanRevisions.fkCommunityPlanID)=43)) OR
(((tblPlanRevisions.RevLevel)=3) AND
((tblPlanRevisions.fkCommunityID)=1)) OR
(((tblPlanRevisions.RevLevel)=2) AND
((tblPlanRevisions.fkDivisionID)=205)) OR
(((tblPlanRevisions.RevLevel)=1) AND
((tblPlanRevisions.fkMasterPlanID)="2051")) OR
(((tblPlanRevisions.RevLevel)=5));


I know this looks hairy, but I'm sure its not a syntax error b/c when
running this as a query, it works fine. It is only in the listbox
that certain records are missing. The categories that are missing are
the ones from the criteria of 'fkMasterplanid and 2015', and
'revlevel=5'.

Any ideas????
 
The first thing I would do in this situation, Darleen, is to double-check
data types. It appears that "fkMasterPlanID" is a text field while all other
fields used in the criteria are numeric fields. Are you sure this is the
case? I'd check the data types of each field used in the criteria, and make
sure that all references to text fields in the criteria have quotes around
the values, and all references to numeric fields do not.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Darleen said:
Very strange problem...
I noticed that one of my listboxes was missing some records, and so I
checked its underlying query (which is assigned through the
listbox.rowsource VB property) to find the problem. The strange thing
is that the missing records ARE present in the underlying query
results!

I've flipped views back and forth thinking I must be missing
something, but it's clear as day - when I look at the listbox, there
are 3 records (2 records missing). When I open the SQL statement
directly behind the listbox by pressing the 3 dots next to the
rowsource field in the property sheet and running that query, all 5
records are there.

The listbox has multiple "OR" conditions defined in the criteria. It
is always the same 2 categories of the criteria that are missing.
Here is the SQL behind the listbox:

SELECT tblPlanRevisions.RevisionID,
IIf([revlevel]=1,'M.Pln',IIf([revlevel]=2,'Div',IIf([revlevel]=3,'Comm',IIf(
[revlevel]=4,'C.Pln','Corp'))))
AS Scope, Format([revdate],'mm/dd/yy') AS [Rev Date],
tblPlanRevisions.Description
FROM tblPlanRevisions
WHERE (((tblPlanRevisions.RevLevel)=4) AND
((tblPlanRevisions.fkCommunityPlanID)=43)) OR
(((tblPlanRevisions.RevLevel)=3) AND
((tblPlanRevisions.fkCommunityID)=1)) OR
(((tblPlanRevisions.RevLevel)=2) AND
((tblPlanRevisions.fkDivisionID)=205)) OR
(((tblPlanRevisions.RevLevel)=1) AND
((tblPlanRevisions.fkMasterPlanID)="2051")) OR
(((tblPlanRevisions.RevLevel)=5));


I know this looks hairy, but I'm sure its not a syntax error b/c when
running this as a query, it works fine. It is only in the listbox
that certain records are missing. The categories that are missing are
the ones from the criteria of 'fkMasterplanid and 2015', and
'revlevel=5'.

Any ideas????
Click to expand...
 
Back
Top