Can I get rid off the blank item in drop down list?

[Guest]

Hi everyone,

I have a combo box with its RowSource based on a query. Its RowSource
property looks like this:

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings ORDER BY [DWGNo];

Because there are some blank records in the DWGNo field of
qryReidProductDrawings, the drop down list also contains a blank item as a
selection option. Can I somehow get rid off the blank item from the combo
box's drop down list? Any suggestion is appreciated.

Regards,
Sam

 

[Jeff Boyce]

Uhmmm? Is there a reason why you couldn't/shouldn't delete the blank
records? In other words, if you have a blank record, what does it tell you?
If you have multiple blank records, what are they telling you?

If that isn't viable, modify your query with a WHERE clause to exclude
blanks.

 

[fredg]

Hi everyone,

I have a combo box with its RowSource based on a query. Its RowSource
property looks like this:

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings ORDER BY [DWGNo];

Because there are some blank records in the DWGNo field of
qryReidProductDrawings, the drop down list also contains a blank item as a
selection option. Can I somehow get rid off the blank item from the combo
box's drop down list? Any suggestion is appreciated.

Regards,
Sam

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings ORDER BY [DWGNo]
Where [DWGNo] is not null;

 

[Guest]

Hi Fred,
Thanks for your help. But the Where clause causes a syntax error (missing
operator) in query expression '[DWGNo] Where [DWGNo] is not null'

How to fix this?

Regards,
Sam

Hi everyone,

I have a combo box with its RowSource based on a query. Its RowSource
property looks like this:

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings ORDER BY [DWGNo];

Because there are some blank records in the DWGNo field of
qryReidProductDrawings, the drop down list also contains a blank item as a
selection option. Can I somehow get rid off the blank item from the combo
box's drop down list? Any suggestion is appreciated.

Regards,
Sam

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings ORDER BY [DWGNo]
Where [DWGNo] is not null;

 

[Guest]

Hi Jeff,

Sorry for the confusion. I should really say blank 'field' instead. My query
also has a number of fields other than DWGNo, and there are some records that
hasn't been assigned a drawing number yet (hence blank), but I don't really
want the blank item to be included in the combo box.

Fred has kindly suggested using the Where clause which you did as well.

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings ORDER BY [DWGNo]
Where [DWGNo] is not null;

But an error was encountered, which says "syntax error (missing operator) in
query expression [DWGNo] Where [DWGNo] is not null"

How should this be fixed?

Regards,
Sam

Uhmmm? Is there a reason why you couldn't/shouldn't delete the blank
records? In other words, if you have a blank record, what does it tell you?
If you have multiple blank records, what are they telling you?

If that isn't viable, modify your query with a WHERE clause to exclude
blanks.

--
Good luck

Jeff Boyce
<Access MVP>

Hi everyone,

I have a combo box with its RowSource based on a query. Its RowSource
property looks like this:

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings ORDER BY [DWGNo];

Because there are some blank records in the DWGNo field of
qryReidProductDrawings, the drop down list also contains a blank item as a
selection option. Can I somehow get rid off the blank item from the combo
box's drop down list? Any suggestion is appreciated.

Regards,
Sam

 

[Jeff Boyce]

Sam

A "blank" in a field might be a null, or might be a zero-length string,
depending on the property of that field set in the table definition...

--
Good luck

Jeff Boyce
<Access MVP>

Hi Fred,
Thanks for your help. But the Where clause causes a syntax error (missing
operator) in query expression '[DWGNo] Where [DWGNo] is not null'

How to fix this?

Regards,
Sam

Hi everyone,

I have a combo box with its RowSource based on a query. Its RowSource
property looks like this:

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings ORDER BY [DWGNo];

Because there are some blank records in the DWGNo field of
qryReidProductDrawings, the drop down list also contains a blank item as a
selection option. Can I somehow get rid off the blank item from the combo
box's drop down list? Any suggestion is appreciated.

Regards,
Sam

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings ORDER BY [DWGNo]
Where [DWGNo] is not null;

 

[John Vinson]

But an error was encountered, which says "syntax error (missing operator) in
query expression [DWGNo] Where [DWGNo] is not null"

How should this be fixed?

Reverse the order of the operations: the WHERE clause must come before
the ORDER BY clause in SQL:

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings
Where [DWGNo] is not null
ORDER BY [DWGNo];


John W. Vinson[MVP]

 

[fredg]

Hi Fred,
Thanks for your help. But the Where clause causes a syntax error (missing
operator) in query expression '[DWGNo] Where [DWGNo] is not null'

How to fix this?

Regards,
Sam

Hi everyone,

I have a combo box with its RowSource based on a query. Its RowSource
property looks like this:

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings ORDER BY [DWGNo];

Because there are some blank records in the DWGNo field of
qryReidProductDrawings, the drop down list also contains a blank item as a
selection option. Can I somehow get rid off the blank item from the combo
box's drop down list? Any suggestion is appreciated.

Regards,
Sam

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings ORDER BY [DWGNo]
Where [DWGNo] is not null;

Yeah, I just added the where clause at the end, but I should have
placed in before the Order By.
.... Where [DWGNo] is not null Order By [DWGNo];

 

[Guest]

Thanks for the clarification, Fred. Much appreciated

Regards,
Sam

Hi Fred,
Thanks for your help. But the Where clause causes a syntax error (missing
operator) in query expression '[DWGNo] Where [DWGNo] is not null'

How to fix this?

Regards,
Sam

On Sun, 10 Apr 2005 03:27:03 -0700, Sam Kuo wrote:

Hi everyone,

I have a combo box with its RowSource based on a query. Its RowSource
property looks like this:

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings ORDER BY [DWGNo];

Because there are some blank records in the DWGNo field of
qryReidProductDrawings, the drop down list also contains a blank item as a
selection option. Can I somehow get rid off the blank item from the combo
box's drop down list? Any suggestion is appreciated.

Regards,
Sam

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings ORDER BY [DWGNo]
Where [DWGNo] is not null;

Yeah, I just added the where clause at the end, but I should have
placed in before the Order By.
.... Where [DWGNo] is not null Order By [DWGNo];