Include 'empties' in query

[H. Martins]

Hi,

Like [Forms].[Busca].[txtbxFiltroEMail].[text] & "*"

works ok except when the above criteria = "*" and the field is empty.
Then the record is not retrieved.

What is the code to include the 'empties'?

Thanks
Henry

 

[BruceM]

Most likely you do not want to use the text property. You should probably
have something like:

LIKE [Forms].[Busca].[txtbxFiltroEMail] & "*"
OR [Forms].[Busca].[txtbxFiltroEMail] Is Null

 

[Henry]

Hi Bruce,

Looks like the OR ... Is Null makes always all records available

LIKE [Forms].[Busca].[txtbxFiltroEMail] & "*"
 OR [Forms].[Busca].[txtbxFiltroEMail] Is Null

Anyway, I didn't find the way to add "OR [Forms].[Busca].
[txtbxFiltroEMail] Is Null" in 'SQL Statement: Query Builder'

I added "LIKE [Forms].[Busca].[txtbxFiltroEMail] is null" (without ")
and it created me another column having "([tblAlunos].[strEMail]) Like
[Forms].[Busca].[txtbxFiltroEMail].[text]" with "is null" in the OR
line.

Thanks
Henry

 

[Henry]

Hi.

I found out that this code seems to work in the Criteria line of the
query builder.

Like [Forms].[Busca].[txtbxFiltroEMail].[text] & "*" Or Is Null

Henry

 

[BruceM]

I may have led you in the wrong direction, but I'm not sure I see how you
are arriving where you want to be. What you are posting as the criteria for
a field will return all records in which that field contains at least the
text in [Forms].[Busca].[txtbxFiltroEMail], or records containing nothing in
that field.

The text property applies only if a control has the focus. You should leave
..[text] off of the end. The text property has limited uses. This is not
one of them.

 

[Henry]

Bruce,

About the .text property, you were right. Fortunately the requery was
issued by a (TextBox) OnKeyUp - that's why it worked. Just in case I
changed to .value.

Indeed, Like [Forms].[Busca].[txtbxFiltroEMail].[value] & "*" Or Is
Null
retrieves records with empties. Without "Or Is Null" it doesn't.

Of course, I may be missing something.

Henry

 

[Henry]

Bruce,

I had it right and made it wrong.

Indeed it must me .text because the requery is triggered by a OnKeyUp.

The searching process goes as we type.

Indeed it made sense to me that just * would retrieve anything include
empties, but it looks otherwise. Looks like * retrieves anything as
long as there is anything (bogus sentence?).

Henry

 

[BruceM]

Doing a requery after every key stroke seems like a lot of overhead. I
would let them type, then click a search button or something like that.

Assuming [Forms].[Busca].[txtbxFiltroEMail] = "Micro" then if the criteria
for a field is:

Like [Forms].[Busca].[txtbxFiltroEMail].[text] & "*"

it should retrieve records with field values starting with "Micro" such as
Microsoft and Microscope, but not Sun Microsystems". For that you would
need:
Like "*" & [Forms].[Busca].[txtbxFiltroEMail].[text] & "*"

I'm not convinced the text property is working as you intend. I would text
it by putting it into the query, maybe by adding it as a calculated field:
TestText: [Forms].[Busca].[txtbxFiltroEMail].[text]

The trouble seems to be that if [Forms].[Busca].[txtbxFiltroEMail].[text]
has no value, the expression is just the wildcard, which would indeed return
all records. Actually, I think it would return just records that are not
null in that field. To return nulls too I think you will need to test
specifically using Is Null in an expression.

 

[BruceM]

That should have been "I would test it..." in the next to last paragraph.

Doing a requery after every key stroke seems like a lot of overhead. I
would let them type, then click a search button or something like that.

Assuming [Forms].[Busca].[txtbxFiltroEMail] = "Micro" then if the criteria
for a field is:

Like [Forms].[Busca].[txtbxFiltroEMail].[text] & "*"

it should retrieve records with field values starting with "Micro" such as
Microsoft and Microscope, but not Sun Microsystems". For that you would
need:
Like "*" & [Forms].[Busca].[txtbxFiltroEMail].[text] & "*"

I'm not convinced the text property is working as you intend. I would
text it by putting it into the query, maybe by adding it as a calculated
field:
TestText: [Forms].[Busca].[txtbxFiltroEMail].[text]

The trouble seems to be that if [Forms].[Busca].[txtbxFiltroEMail].[text]
has no value, the expression is just the wildcard, which would indeed
return all records. Actually, I think it would return just records that
are not null in that field. To return nulls too I think you will need to
test specifically using Is Null in an expression.

Bruce,

I had it right and made it wrong.

Indeed it must me .text because the requery is triggered by a OnKeyUp.

The searching process goes as we type.

Indeed it made sense to me that just * would retrieve anything include
empties, but it looks otherwise. Looks like * retrieves anything as
long as there is anything (bogus sentence?).

Henry