Null fields

M

MikeP

Hi,

I'm creating a search engine to find records on a table with multiple
criteria in different fields. Some of the fields are missing or incomplete.
So far I've been using:

Like "*" & [Forms]![Search]![Status] & "*"

in the criteria fields in query builder to narrow down the criteria
selections in successive pulldown menus. If the Status control is left blank
this only returns the fields where there are entries. I want it to find all
the fields including blank ones if the user leaves the control blank.

this is an SQL example of the same pulldown box containing the options
remaining based on previous criteria entered

SELECT DISTINCT dbo_relQIS.Attrib07 FROM dbo_relQIS WHERE (((dbo_relQIS.Grp)
Like "*" & Forms!Search!Group & "*") And ((dbo_relQIS.Attrib01) Like "*" &
Forms!Search!Status & "*")) ORDER BY dbo_relQIS.Attrib07;


Thanks in advance
 
K

KARL DEWEY

Use this for criteria --
Like "*" & [Forms]![Search]![Status] & "*" OR Is Null
 
M

MikeP

That would return Null records even if there was a specific text string
entered in Status. I only want Null records to show if the Status control is
left blank.

KARL DEWEY said:
Use this for criteria --
Like "*" & [Forms]![Search]![Status] & "*" OR Is Null


MikeP said:
Hi,

I'm creating a search engine to find records on a table with multiple
criteria in different fields. Some of the fields are missing or incomplete.
So far I've been using:

Like "*" & [Forms]![Search]![Status] & "*"

in the criteria fields in query builder to narrow down the criteria
selections in successive pulldown menus. If the Status control is left blank
this only returns the fields where there are entries. I want it to find all
the fields including blank ones if the user leaves the control blank.

this is an SQL example of the same pulldown box containing the options
remaining based on previous criteria entered

SELECT DISTINCT dbo_relQIS.Attrib07 FROM dbo_relQIS WHERE (((dbo_relQIS.Grp)
Like "*" & Forms!Search!Group & "*") And ((dbo_relQIS.Attrib01) Like "*" &
Forms!Search!Status & "*")) ORDER BY dbo_relQIS.Attrib07;


Thanks in advance
Click to expand...
Click to expand...
 
G

Gary Walter

PMFBI
I think Karl meant

Like "*" & [Forms]![Search]![Status] & "*"
OR [Forms]![Search]![Status] Is Null

MikeP said:
That would return Null records even if there was a specific text string
entered in Status. I only want Null records to show if the Status control
is
left blank.

KARL DEWEY said:
Use this for criteria --
Like "*" & [Forms]![Search]![Status] & "*" OR Is Null


MikeP said:
Hi,

I'm creating a search engine to find records on a table with multiple
criteria in different fields. Some of the fields are missing or
incomplete.
So far I've been using:

Like "*" & [Forms]![Search]![Status] & "*"

in the criteria fields in query builder to narrow down the criteria
selections in successive pulldown menus. If the Status control is left
blank
this only returns the fields where there are entries. I want it to find
all
the fields including blank ones if the user leaves the control blank.

this is an SQL example of the same pulldown box containing the options
remaining based on previous criteria entered

SELECT DISTINCT dbo_relQIS.Attrib07 FROM dbo_relQIS WHERE
(((dbo_relQIS.Grp)
Like "*" & Forms!Search!Group & "*") And ((dbo_relQIS.Attrib01) Like
"*" &
Forms!Search!Status & "*")) ORDER BY dbo_relQIS.Attrib07;


Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
Top