Include 'empties' in query

  • Thread starter Thread starter H. Martins
  • Start date Start date
H

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
 
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
 
Hi Bruce,

Looks like the OR ... Is Null makes always all records available
LIKE [Forms].[Busca].[txtbxFiltroEMail] & "*"
 OR [Forms].[Busca].[txtbxFiltroEMail] Is Null
Click to expand...

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
 
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
 
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.
 
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
 
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
 
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.
 
That should have been "I would test it..." in the next to last paragraph.

BruceM said:
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.

Henry said:
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
Click to expand...
Click to expand...
 
Back
Top