Searching based on form shows only records with values

[atom]

Hello everyone,
I have a query that returns some results based on user input in form.
The field in query is text .
In the criteria of this query I have tried the following:
1.

Like Nz([Forms]![SearchForm]![txt];"*")

2.

Like "*"&[Forms]![SearchForm]![txt]&"*"

3.

Like [Forms]![SearchForm]![txt]&"*"

The field in the query contains blank values.
So whatever I tried I get only the records where the field in the query has values.


Is there another way to display all values and only the specified when user searches?


thank you in advance

[June7]

The field is text type for a date value?

Using Like and wildcard doesn't really make sense for a date value. Do users input only a partial date as criteria?

[atom]

My wrong .
I edited my post .
The user enters text as input

[June7]

Your post still states 'The field in query is text .'

Like and wildcard doesn't return records with null, no matter what the datatype is. I seldom use query parameters because of their inflexibility. Review this http://allenbrowne.com/ser-62.html

[atom]

Yes the field in my query is text
I will check the link you posted
Thank you for your replies

[June7]

You have date values in a text datatype field - why?

[atom]

Lol !Big misunderstanding ....
The user enters text as criteria in a text field. The date thing ,was a typographical error at first post .
I posted

Like Nz([Forms]![SearchForm]![txtDate];"*") 

by mistake
I should post

Like Nz([Forms]![SearchForm]![txt];"*")

Thank you for your patience

[orange]

What exactly do you expect or want this to find?

Like Nz([Forms]![SearchForm]![txt];"*")

Details of Nz() are here http://www.techonthenet.com/access/f...dvanced/nz.php

If you use Like and you want to find things that:
start with whatever is in
[Forms]![SearchForm]![txt], you would write
... Like [Forms]![SearchForm]![txt] & "*"

end with whatever is in
[Forms]![SearchForm]![txt], you would write
... Like "*" & [Forms]![SearchForm]![txt]

contain whatever is in
[Forms]![SearchForm]![txt], you would write
... Like "*" & [Forms]![SearchForm]![txt] & "*"

[atom]

Thanks for your response.
My problem is that whatever criteria I enter on query I get records that contain values , ignoring blanks .
If I enter

or IsNull

in criteria , the search doesn't work obviously

[orange]

So you want everything including blanks or nulls etc?


Suppose you had records:
.........{Searched field}
A........XYZ
B........JKL
C........[NULL] or blank
D........123K

and you searched

.....SearchField Like "*" & "K" & "*"

What records do you want returned? If this example isn't clear enough, or not addressing your issue, please make up an example that highlights your intent.

[June7]

So the field is a date datatype?

Did you review the Allen Browne link?

[atom]

So you want everything including blanks or nulls etc?


Suppose you had records:
.........{Searched field}
A........XYZ
B........JKL
C........[NULL] or blank
D........123K

and you searched

.....SearchField Like "*" & "K" & "*"

What records do you want returned? If this example isn't clear enough, or not addressing your issue, please make up an example that highlights your intent.

That's exactlyp what I want.

My results are :


B........JKL
D........123K

but when the search field is left blank I have
A........XYZ
B........JKL
D........123K

[atom]

The field is text and the user's input is text also .
Date was a typo I did in first post!

Thank you for your replies