Query Does Not Pick Up Data

A

antmorano

I'm having a problem with new records that I put in through my table
in Access. When I go to the query where it should come up it
doesn't. I only have to lines of criteria that are not very long.
This is a retiree database and there are 7 people which don't show
up. Three are similar in that they are both single. Two are similar
in that they are both married and and their spouses are under 65 and
the final two are similar in that they are both married and they are
over 65 and their spouses are under. I don't know why the query isn't
picking up the new records since there are similar records already in
the query. Here is the SQL view of the query. Any input would be
appreciated.

SELECT [All Plans].[Chain Sequence Number], [All Plans].[Retiree Last
Name], [All Plans].[Retiree First Name], [All Plans].[Retiree SSN],
[All Plans].[Plan], [All Plans].[Fund], [All Plans].[Retiree DOB],
[All Plans].[Retiree Gender], [All Plans].[Retiree Eligibility Date],
[All Plans].[Retirement Date], [All Plans].[Address], [All Plans].
[City], [All Plans].[State], [All Plans].[Zip], [All Plans].[Control
Group], [All Plans].[Spouse First Name], [All Plans].[Spouse Last
Name], [All Plans].[Spouse SSN], [All Plans].[Spouse DOB], [All Plans].
[Spouse Gender], [All Plans].[Spouse Eligibility Date], [All Plans].
[Retiree DOD], [All Plans].[Spouse DOD], [All Plans].[Date of Entry],
[All Plans].[Status of Coverage- Retiree], [All Plans].[Status of
Coverage- Spouse], IIf([Retiree DOD] Is Null,Abs(DateDiff("yyyy",
[Retiree DOB],Date())-IIf(Format([Retiree
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))) AS [Retiree Age],
IIf([Spouse DOD] Is Null,Abs(DateDiff("yyyy",[Spouse DOB],Date())-
IIf(Format([Spouse DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))) AS
[Spouse Age]
FROM [All Plans]
WHERE ((([All Plans].[Chain Sequence Number])="5001") And (([All
Plans].[Plan])="VA" Or ([All Plans].[Plan])="VAL" Or ([All Plans].
[Plan])="VB" Or ([All Plans].[Plan])="VBL") And (([All Plans].
[Fund])="Welfare") And (([All Plans].[Retiree Eligibility
Date])<Date()) And (([All Plans].[Status of Coverage-
Retiree])<>"CANCELLED") And ((IIf([Retiree DOD] Is
Null,Abs(DateDiff("yyyy",[Retiree DOB],Date())-IIf(Format([Retiree
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))<65)) Or ((([All Plans].
[Chain Sequence Number])="5001") And (([All Plans].[Plan])="VA" Or
([All Plans].[Plan])="VAL" Or ([All Plans].[Plan])="VB" Or ([All
Plans].[Plan])="VBL") And (([All Plans].[Fund])="Welfare") And (([All
Plans].[Spouse Eligibility Date])<Date()) And (([All Plans].[Status of
Coverage- Spouse])<>"CANCELLED") And ((IIf([Spouse DOD] Is
Null,Abs(DateDiff("yyyy",[Spouse DOB],Date())-IIf(Format([Spouse
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))<65));


-Anthony Morano
Pension Fund Intern
 
J

Jeff Boyce

Anthony

The WHERE clause of your query is ... lengthy.

When I have a query that isn't returning rows I expect, I start over. I
create a new query that has NO selection criterion. When it is working as I
would expect, I add a single criterion and re-test. I continue this
build/test sequence until I find the change that "breaks" the query. That
way I know where to look.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm having a problem with new records that I put in through my table
in Access. When I go to the query where it should come up it
doesn't. I only have to lines of criteria that are not very long.
This is a retiree database and there are 7 people which don't show
up. Three are similar in that they are both single. Two are similar
in that they are both married and and their spouses are under 65 and
the final two are similar in that they are both married and they are
over 65 and their spouses are under. I don't know why the query isn't
picking up the new records since there are similar records already in
the query. Here is the SQL view of the query. Any input would be
appreciated.

SELECT [All Plans].[Chain Sequence Number], [All Plans].[Retiree Last
Name], [All Plans].[Retiree First Name], [All Plans].[Retiree SSN],
[All Plans].[Plan], [All Plans].[Fund], [All Plans].[Retiree DOB],
[All Plans].[Retiree Gender], [All Plans].[Retiree Eligibility Date],
[All Plans].[Retirement Date], [All Plans].[Address], [All Plans].
[City], [All Plans].[State], [All Plans].[Zip], [All Plans].[Control
Group], [All Plans].[Spouse First Name], [All Plans].[Spouse Last
Name], [All Plans].[Spouse SSN], [All Plans].[Spouse DOB], [All Plans].
[Spouse Gender], [All Plans].[Spouse Eligibility Date], [All Plans].
[Retiree DOD], [All Plans].[Spouse DOD], [All Plans].[Date of Entry],
[All Plans].[Status of Coverage- Retiree], [All Plans].[Status of
Coverage- Spouse], IIf([Retiree DOD] Is Null,Abs(DateDiff("yyyy",
[Retiree DOB],Date())-IIf(Format([Retiree
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))) AS [Retiree Age],
IIf([Spouse DOD] Is Null,Abs(DateDiff("yyyy",[Spouse DOB],Date())-
IIf(Format([Spouse DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))) AS
[Spouse Age]
FROM [All Plans]
WHERE ((([All Plans].[Chain Sequence Number])="5001") And (([All
Plans].[Plan])="VA" Or ([All Plans].[Plan])="VAL" Or ([All Plans].
[Plan])="VB" Or ([All Plans].[Plan])="VBL") And (([All Plans].
[Fund])="Welfare") And (([All Plans].[Retiree Eligibility
Date])<Date()) And (([All Plans].[Status of Coverage-
Retiree])<>"CANCELLED") And ((IIf([Retiree DOD] Is
Null,Abs(DateDiff("yyyy",[Retiree DOB],Date())-IIf(Format([Retiree
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))<65)) Or ((([All Plans].
[Chain Sequence Number])="5001") And (([All Plans].[Plan])="VA" Or
([All Plans].[Plan])="VAL" Or ([All Plans].[Plan])="VB" Or ([All
Plans].[Plan])="VBL") And (([All Plans].[Fund])="Welfare") And (([All
Plans].[Spouse Eligibility Date])<Date()) And (([All Plans].[Status of
Coverage- Spouse])<>"CANCELLED") And ((IIf([Spouse DOD] Is
Null,Abs(DateDiff("yyyy",[Spouse DOB],Date())-IIf(Format([Spouse
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))<65));


-Anthony Morano
Pension Fund Intern
Click to expand...
 
A

antmorano

Jeff- I appreciate the response. I did start over. I originally had
9 lines of criteria and now I realized that I only needed 2. The
reason why it is lengthy is b/c i have two fields which calculate ages
of two individuals. If I didn't have that then it would be much
shorter. It is only the new data that I put in which is not being
picked up. The old data is being picked up fine.
 
J

Jeff Boyce

Anthony

If the query isn't picking up the "new data", then the "new data" is somehow
different than the "old data". Try re-inspecting the data directly in the
table and see if you spot any other similarities/differences in comparison
with pre-existing data.

One thing that commonly causes trouble is a date/time field ... if date AND
time are stored, but only "date" is used as a selection criterion.

Of course, you would want to re-run the query AFTER adding new records to
see them.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
D

David Cox

You can shorten the OR clauses ...
....(([All Plans].[Plan]) IN("VA","VAL" ,"VB","VBL")

....


Jeff Boyce said:
Anthony

The WHERE clause of your query is ... lengthy.

When I have a query that isn't returning rows I expect, I start over. I
create a new query that has NO selection criterion. When it is working as
I would expect, I add a single criterion and re-test. I continue this
build/test sequence until I find the change that "breaks" the query. That
way I know where to look.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm having a problem with new records that I put in through my table
in Access. When I go to the query where it should come up it
doesn't. I only have to lines of criteria that are not very long.
This is a retiree database and there are 7 people which don't show
up. Three are similar in that they are both single. Two are similar
in that they are both married and and their spouses are under 65 and
the final two are similar in that they are both married and they are
over 65 and their spouses are under. I don't know why the query isn't
picking up the new records since there are similar records already in
the query. Here is the SQL view of the query. Any input would be
appreciated.

SELECT [All Plans].[Chain Sequence Number], [All Plans].[Retiree Last
Name], [All Plans].[Retiree First Name], [All Plans].[Retiree SSN],
[All Plans].[Plan], [All Plans].[Fund], [All Plans].[Retiree DOB],
[All Plans].[Retiree Gender], [All Plans].[Retiree Eligibility Date],
[All Plans].[Retirement Date], [All Plans].[Address], [All Plans].
[City], [All Plans].[State], [All Plans].[Zip], [All Plans].[Control
Group], [All Plans].[Spouse First Name], [All Plans].[Spouse Last
Name], [All Plans].[Spouse SSN], [All Plans].[Spouse DOB], [All Plans].
[Spouse Gender], [All Plans].[Spouse Eligibility Date], [All Plans].
[Retiree DOD], [All Plans].[Spouse DOD], [All Plans].[Date of Entry],
[All Plans].[Status of Coverage- Retiree], [All Plans].[Status of
Coverage- Spouse], IIf([Retiree DOD] Is Null,Abs(DateDiff("yyyy",
[Retiree DOB],Date())-IIf(Format([Retiree
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))) AS [Retiree Age],
IIf([Spouse DOD] Is Null,Abs(DateDiff("yyyy",[Spouse DOB],Date())-
IIf(Format([Spouse DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))) AS
[Spouse Age]
FROM [All Plans]
WHERE ((([All Plans].[Chain Sequence Number])="5001") And (([All
Plans].[Plan])="VA" Or ([All Plans].[Plan])="VAL" Or ([All Plans].
[Plan])="VB" Or ([All Plans].[Plan])="VBL") And (([All Plans].
[Fund])="Welfare") And (([All Plans].[Retiree Eligibility
Date])<Date()) And (([All Plans].[Status of Coverage-
Retiree])<>"CANCELLED") And ((IIf([Retiree DOD] Is
Null,Abs(DateDiff("yyyy",[Retiree DOB],Date())-IIf(Format([Retiree
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))<65)) Or ((([All Plans].
[Chain Sequence Number])="5001") And (([All Plans].[Plan])="VA" Or
([All Plans].[Plan])="VAL" Or ([All Plans].[Plan])="VB" Or ([All
Plans].[Plan])="VBL") And (([All Plans].[Fund])="Welfare") And (([All
Plans].[Spouse Eligibility Date])<Date()) And (([All Plans].[Status of
Coverage- Spouse])<>"CANCELLED") And ((IIf([Spouse DOD] Is
Null,Abs(DateDiff("yyyy",[Spouse DOB],Date())-IIf(Format([Spouse
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))<65));


-Anthony Morano
Pension Fund Intern
Click to expand...
Click to expand...
 
A

antmorano

Jeff, Thank You so much for your help. I got the database working
perfectly. I truly appreciate all the input.

-Anthony Morano
Pension Fund Inter
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Specify Date to Calculate 1
Criteria Expression Problem 3
Do Not Include Criteria 5
Confused About Criteria 2
Query Does Not Count "2" Records 1
MS Project. Real progress of the project, % complete 1
Save of Linked file does not retain changes 3
Need help with Between dates on birthday calculation 9

Top