Suppress Blank Values in a Lookup Field

[kpit]

Yes, I know that using Lookup Fields in tables is not a great idea, but I'm picking up from another person under a huge time crunch, so am trying to make due. I have a form with a Document field that looks up a list of documents from a master document list (SELECT MLTABLE.Document_ID, MLTABLE.[Doc#, Title, Version (Date)] FROM MLTABLE ORDER BY MLTABLE.[Doc#, Title, Version (Date)] It works GREAT; however, due to the nature of the master document list, there are blanks in the Doc#, Title, Version (Date) field that is displayed, so when you click the drop-down list, the choices appear to be blank - you have to scroll past the blanks to find the alpha list of documents. How can I tell the look-up to NOT list the blank records in the drop-down list?



Any assistance you can lend is MOST appreciated!

Kimberley

[davegri]

SELECT MLTABLE.Document_ID, MLTABLE.[Doc#, Title, Version (Date)] FROM MLTABLE WHERE MLTABLE.[Doc#, Title, Version (Date)] IS NOT NULL ORDER BY MLTABLE.[Doc#, Title, Version (Date)]

[Minty]

I appreciate you didn't design this , but I think you just became a very early contender for this months award for worst field name

Comma's, special characters, and parenthesised reserved word all in one go !

[kpit]

that worked perfectly...so easy I should have figured it out. The people who entered the document titles also sometimes put blank spaces in front of the actual title...is there a way to suppress those AND have those entries put into alpha order with the items that do not have blank spaces in front of the title?

[kpit]

You're telling me!!! The problem is it feeds from another table and when they tried to change the Access field name the links didn't work well. CRAZY! I could go on for weeks about it...Thanks for the laugh though!

[davegri]

Comma's, special characters, and parenthesised reserved word all in one go !

Agreed. If the rest of the DB is like this, debugging time might be boosted tenfold!

[davegri]

.is there a way to suppress those AND have those entries put into alpha order with the items that do not have blank spaces in front of the title?

You could run some update queries to remove leading and trailing spaces via the TRIM function. Take care though, that this might have unforeseen effects elsewhere and needs careful analysis.