simple SQL

[markjkubicki]

I've got the following query as the data source for a form:
(I do not want any records where the value of [Rep Agency ] is null to be displayed)...

SELECT tblManufacturers.*
FROM tblManufacturers
WHERE (((tblManufacturers.[Rep Agency]) Is Not Null))
ORDER BY tblManufacturers.[Rep Agency], tblManufacturers.ManufacturerName;

It seems to me that I've got the query correct. I've even added a simple text box to the form with the data source:

=Nz([rep Agency],"null")

yet still the records are included; that text box blatantly returns "null" values.

perplexed, yet with thanks in advance..
Mark

[CJ_London]

they are probably not null but zero length strings, try chaning your criteria to

WHERE (((nz(tblManufacturers.[Rep Agency],"")) <>""))

[pbaldy]

Try

WHERE tblManufacturers.[Rep Agency] Is Not Null AND tblManufacturers.[Rep Agency] <> ""

[ssanfu]

When you execute the current query, how many records are returned?

If you then remove the criteria and execute the query, how many records are returned?


Text boxes have Control source, not data source.
For a bound control, the control source should not have the equals sign, just the name of the field the control is bound to.

[markjkubicki]

1651 records with the filter
1724 records without it
... so obviously some of the null records were not

(?) confused then... why was the text box with the control source
=Nz([rep Agency],"null")

Showing the value "null" for some of the records, while for others it did show a value that matched the entry in the record?
I would have expected that any record as such (with a null value) would have been filtered out at the form's recordsource? no?
for now, let's leave it as a redundant question (and a lesson for tomorrow...)

WHERE (((nz(tblManufacturers.[Rep Agency],"")) <>""))

WORKED!!! and for that: BIG thanks