IS null

[Guest]

I have a continuous form based on a query. In the form header, I have unbound
textboxes relating to the controls in the continuous form. The parameters for
the query are the values in the unbound controls. This all works as expected.

In order to avoid nulls, I havent put IS null criteria in the query.this
gives precise results. however there are many records which have info in one
field (say post code) and no in other (say last name)
this records are not displayed.

IF I take the is null criteria, then the search displays huge lists.

IS there a way around this?

I have the sql below

SELECT uniquew.[Supporter No], uniquew.[Post Code], uniquew.[Last Name],
uniquew.[First Name]
FROM uniquew
WHERE (((uniquew.[Supporter No]) Like [Forms]![main]![Text20] & "*") AND
((uniquew.[Post Code]) Like [Forms]![main]![Text15] & "*") AND
((uniquew.[Last Name]) Like "*" & [Forms]![main]![Text31] & "*") AND
((uniquew.[First Name]) Like "*" & [Forms]![main]![Text45] & "*"));

 

[Guest]

Try using your Is Not Null as an OR statement. If you are using the Query
Design window, simply add Is Not Null, on separate criteria lines, in each
column you want to filter by.

Good luck.

Sharkbyte

 

[Guest]

thanks

that doesnt do anything. infact the query displays all records without blanks

Try using your Is Not Null as an OR statement. If you are using the Query
Design window, simply add Is Not Null, on separate criteria lines, in each
column you want to filter by.

Good luck.

Sharkbyte

I have a continuous form based on a query. In the form header, I have unbound
textboxes relating to the controls in the continuous form. The parameters for
the query are the values in the unbound controls. This all works as expected.

In order to avoid nulls, I havent put IS null criteria in the query.this
gives precise results. however there are many records which have info in one
field (say post code) and no in other (say last name)
this records are not displayed.

IF I take the is null criteria, then the search displays huge lists.

IS there a way around this?

I have the sql below

SELECT uniquew.[Supporter No], uniquew.[Post Code], uniquew.[Last Name],
uniquew.[First Name]
FROM uniquew
WHERE (((uniquew.[Supporter No]) Like [Forms]![main]![Text20] & "*") AND
((uniquew.[Post Code]) Like [Forms]![main]![Text15] & "*") AND
((uniquew.[Last Name]) Like "*" & [Forms]![main]![Text31] & "*") AND
((uniquew.[First Name]) Like "*" & [Forms]![main]![Text45] & "*"));