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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
Click to expand...

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings ORDER BY [DWGNo]
Where [DWGNo] is not null;
 
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


fredg said:
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
Click to expand...

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings ORDER BY [DWGNo]
Where [DWGNo] is not null;
Click to expand...
 
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


Jeff Boyce said:
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>

Sam Kuo said:
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
Click to expand...
Click to expand...
 
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>

Sam Kuo said:
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


fredg said:
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
Click to expand...

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings ORDER BY [DWGNo]
Where [DWGNo] is not null;
Click to expand...
Click to expand...
 
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?
Click to expand...

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]
 
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

fredg said:
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
Click to expand...

SELECT DISTINCT [DWGNo] FROM qryReidProductDrawings ORDER BY [DWGNo]
Where [DWGNo] is not null;
Click to expand...
Click to expand...

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];
 
Thanks for the clarification, Fred. Much appreciated

Regards,
Sam

fredg said:
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

fredg said:
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;
Click to expand...
Click to expand...

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];
Click to expand...
 
Back
Top