No results in query

[Guest]

I am very new to Access so please bear with me if it’s something really
obvious but I have a problem that’s completely baffled me! I’ve set up a
simple query to search for criteria, when I first set it up there were only 3
criteria I needed to search for (types of University School in the field
‘School’, and the year) and it worked fine. I’ve now added two more Schools
but when I search for these I get no results! This is a copy of the SQL view
from the unsuccessful query in case that helps…

SELECT [ExEx Noms].Title, [ExEx Noms].[First Name], [ExEx Noms].[Last Name],
[ExEx Noms].[Place of work], [ExEx Noms].Programme, [ExEx Noms].Component,
[ExEx Noms].From, [ExEx Noms].To, [ExEx Noms].School, [ExEx Noms].[Title
(Nominee)], [ExEx Noms].Forename, [ExEx Noms].Surname, [ExEx
Noms].Institution, [ExEx Noms].[Date to SQC], [ExEx Noms].[Date to ASC],
[ExEx Noms].[Date to AB], [ExEx Noms].[Date Confirmed], [ExEx Noms].Comments,
[ExEx Noms].[Proposed From], [ExEx Noms].[Proposed To]
FROM [ExEx Noms]
WHERE ((([ExEx Noms].To)="2007") AND (([ExEx Noms].School)="Social Studies"));

Any help would be greatly appreciated.

 

[Duane Hookom]

Do you have any records where the exact text in the To field is "2007" and
in the same record, the School field contains exactly "Social Studies"?

 

[Guest]

When troubleshooting a query, start from the simple and work towards the
complex.

Remove the Where clause in its entirety. Do you see all the records? You
should.

Next add back in this and see if you return the correct records. If so
change Social Studies to other schools and test them:

WHERE [ExEx Noms].School)="Social Studies" ;

Next remove the line above and replace it with this. Try some other To's and
see if you get the expected results.
WHERE [ExEx Noms].To)="2007" ;

Next run the following:

WHERE [ExEx Noms].To)="2007"
and [ExEx Noms].School)="Social Studies";

No records returned? It's possible that there are no records with both 2007
AND Social Studies. Run the following to see if 2007 OR Social Studies
returns records.

WHERE [ExEx Noms].To)="2007"
OR [ExEx Noms].School)="Social Studies";

 

[Guest]

Yes, there are just three records that match the criteria exactly for Social
Studies and 2007, i've triple checked that there's no mispell/typos but the
query doesn't seem to pick them up. Three doesn't seem like much but i'd like
to seperate them for a report and can't understand why it works for some
schools and not others? i checked the SQL view for the successful &
unsucessful ones & they're exactly the same if that makes any difference?

Do you have any records where the exact text in the To field is "2007" and
in the same record, the School field contains exactly "Social Studies"?

--
Duane Hookom
MS Access MVP

I am very new to Access so please bear with me if it's something really
obvious but I have a problem that's completely baffled me! I've set up a
simple query to search for criteria, when I first set it up there were
only 3
criteria I needed to search for (types of University School in the field
'School', and the year) and it worked fine. I've now added two more
Schools
but when I search for these I get no results! This is a copy of the SQL
view
from the unsuccessful query in case that helps.

SELECT [ExEx Noms].Title, [ExEx Noms].[First Name], [ExEx Noms].[Last
Name],
[ExEx Noms].[Place of work], [ExEx Noms].Programme, [ExEx Noms].Component,
[ExEx Noms].From, [ExEx Noms].To, [ExEx Noms].School, [ExEx Noms].[Title
(Nominee)], [ExEx Noms].Forename, [ExEx Noms].Surname, [ExEx
Noms].Institution, [ExEx Noms].[Date to SQC], [ExEx Noms].[Date to ASC],
[ExEx Noms].[Date to AB], [ExEx Noms].[Date Confirmed], [ExEx
Noms].Comments,
[ExEx Noms].[Proposed From], [ExEx Noms].[Proposed To]
FROM [ExEx Noms]
WHERE ((([ExEx Noms].To)="2007") AND (([ExEx Noms].School)="Social
Studies"));

Any help would be greatly appreciated.

 

[Guest]

Thank you! That was really helpful - I think I can see why it's not returning
results, I have two sets of 2007 in the records (referring to the end date of
two different things, that relate to the same person but have different start
dates) and the query doesn't seem to pick these entries up. I think I need to
rethink how I present records which have two entries in them like these..
You've already been very helpful but if you have any ideas on this aspect
that would be great, if not thanks again!

When troubleshooting a query, start from the simple and work towards the
complex.

Remove the Where clause in its entirety. Do you see all the records? You
should.

Next add back in this and see if you return the correct records. If so
change Social Studies to other schools and test them:

WHERE [ExEx Noms].School)="Social Studies" ;

Next remove the line above and replace it with this. Try some other To's and
see if you get the expected results.
WHERE [ExEx Noms].To)="2007" ;

Next run the following:

WHERE [ExEx Noms].To)="2007"
and [ExEx Noms].School)="Social Studies";

No records returned? It's possible that there are no records with both 2007
AND Social Studies. Run the following to see if 2007 OR Social Studies
returns records.

WHERE [ExEx Noms].To)="2007"
OR [ExEx Noms].School)="Social Studies";




--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am very new to Access so please bear with me if it’s something really
obvious but I have a problem that’s completely baffled me! I’ve set up a
simple query to search for criteria, when I first set it up there were only 3
criteria I needed to search for (types of University School in the field
‘School’, and the year) and it worked fine. I’ve now added two more Schools
but when I search for these I get no results! This is a copy of the SQL view
from the unsuccessful query in case that helps…

SELECT [ExEx Noms].Title, [ExEx Noms].[First Name], [ExEx Noms].[Last Name],
[ExEx Noms].[Place of work], [ExEx Noms].Programme, [ExEx Noms].Component,
[ExEx Noms].From, [ExEx Noms].To, [ExEx Noms].School, [ExEx Noms].[Title
(Nominee)], [ExEx Noms].Forename, [ExEx Noms].Surname, [ExEx
Noms].Institution, [ExEx Noms].[Date to SQC], [ExEx Noms].[Date to ASC],
[ExEx Noms].[Date to AB], [ExEx Noms].[Date Confirmed], [ExEx Noms].Comments,
[ExEx Noms].[Proposed From], [ExEx Noms].[Proposed To]
FROM [ExEx Noms]
WHERE ((([ExEx Noms].To)="2007") AND (([ExEx Noms].School)="Social Studies"));

Any help would be greatly appreciated.