Parameter Query - Not Like

[Melissa]

I have a field in my database named ScheduleYear. It is a
text field with a length of 4 used to track whether a
person has submitted their travel schedule for the summer.

My parameter query will allow me to enter "2004", "*" or
enter through it to return all records. What I ALSO want
to be able to find with my query is who has not submitted
a travel schedule for 2004. So I want to use one
parameter query where the user enters "2004" or enters
something that results in Not Like 2004.

Can anyone help?

 

[John Viescas]

It entirely depends on your table design. Are people in one table and
travel schedule info in another? Assuming this is the case, you'll need a
query like:

SELECT *
FROM People
LEFT JOIN
(SELECT PersonID
FROM TravelInfo
WHERE TravelInfo.ScheduleYear LIKE [Enter Year] & "*") As TI
ON People.PersonID = TI.PersonID
WHERE TI.PersonID IS NULL

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)