listbox row source - exclude values 1

[MADDPM]

Hi all,
If this has already been answered, please refer me to previous thread.

Currently using MSAccess2007/Windows XPPro

I have created a form that pops-up when certain reports are opened that has a listbox listing academic years (text):
Name: lstAcademicYear
Multi Select: Simple
Row Source Type: Table/Query

The listbox works beautifully; however, there are four values at the top of the list which I need to exclude from the available choices for users: "Pending", "N/A", "Inactive", "Continuing"

The original Row Source is:
SELECT DISTINCT CONTRACT_TERM.AY_related_to_year_term FROM CONTRACT_TERM UNION SELECT "All" FROM CONTRACT_TERM ORDER BY CONTRACT_TERM.AY_related_to_year_term DESC;

When I try to exclude say the value "Pending" using WHERE, it doesn't work.
SELECT DISTINCT CONTRACT_TERM.AY_related_to_year_term FROM CONTRACT_TERM UNION SELECT "All" FROM CONTRACT_TERM WHERE CONTRACT_TERM.AY_related_to_year_term <>'Pending'
ORDER BY CONTRACT_TERM.AY_related_to_year_term DESC;

nor does using "NOT IN"
SELECT DISTINCT CONTRACT_TERM.AY_related_to_year_term FROM CONTRACT_TERM UNION SELECT "All" FROM CONTRACT_TERM WHERE CONTRACT_TERM.AY_related_to_year_term NOT IN ('Pending', 'N/A')
ORDER BY CONTRACT_TERM.AY_related_to_year_term DESC;

Any suggestions? (I'd really like to keep this SQL in the Row Source - rather than another separate VBA script - but I'm open to suggestions.)
Thanks-
Colleen

 

[dhookom]

You need to move the where clause:

SELECT DISTINCT CONTRACT_TERM.AY_related_to_year_term 
FROM CONTRACT_TERM 
WHERE  CONTRACT_TERM.AY_related_to_year_term NOT IN ('Pending', 'N/A')
UNION 
SELECT "All" 
FROM CONTRACT_TERM 
ORDER BY CONTRACT_TERM.AY_related_to_year_term DESC

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?

SELECT AY_related_to_year_term
FROM CONTRACT_TERM
WHERE AY_related_to_year_term NOT IN ('Pending','N/A','Inactive','Continuing')
UNION SELECT 'All'
FROM CONTRACT_TERM
ORDER BY 1 DESC;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MADDPM]

Duane - Works perfectly! Thank so, so much.
-Colleen

 

[MADDPM]

PHV (MIS)- tried this, but I'm getting a syntax error. Since Duane's solution worked, I'll stick with that.
Many thanks for your time though.
-Colleen

 

[PHV]

I'm getting a syntax error
Really ?
Which code is highlighted ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886