wrong search results

[guest]

Hi,
I have problem with my form for searching records. It contains sub form which open full table with all records and combo boxes for searching fields. I have problems with date search combo box. if all date fields are filled everything is fine, but if i have empty date field than that record containing empty date field is added to search results no mater what field i am searching with. my after update event for searching records is macro containing where conditions ="[Fieldname] = " & "'" & [Screen].[ActiveControl] & "'". my query criteria for date field is Like "*" & [forms]![Formname].[comboname] & "*" or is null. what i get when i search is this in this example searching was done with first combo box entering value 10 and I get first two records which supposed to be and third record with empty date field which is added but aint supposed to be shown. same thing happens if i search with date field or any other. i get search results plus record with empty date field.
thanks
is been send too pcreview too not answer. here is the link https://www.pcreview.co.uk/search/112657/?c[users]=unknownuser&o=date

[Welshgasman]

So the date field is Null and you ask for

my query criteria for date field is Like "*" & [forms]![Formname].[comboname] & "*" or is null.

is been send too pcreview too not answer. here is the link https://www.pcreview.co.uk/search/112657/?c[users]=unknownuser&o=date

This has been crossposted at https://www.access-programmers.co.uk...-table.332915/

[guest]

In query is null is added to get all records in the searching. if i delete is null from the query that record will be omitted from searching. Solution?

[Welshgasman]

In query is null is added to get all records in the searching. if i delete is null from the query that record will be omitted from searching. Solution?

Which is what you are after?, Yes?

and third record with empty date field which is added but aint supposed to be shown

[guest]

I want query to show all records for searching. i might search for data even from the record with empty date field. If I exclude that record from searching i might miss what I need to find and wont be shown in the search. I dont know how to fix this. I get one or another. searching all records with added plus record or exact search if searching is not related to that record (in case it is related will be missing).

[June7]

Wildcards are really only useful for text data. Review http://allenbrowne.com/ser-62.html

[Welshgasman]

I want query to show all records for searching. i might search for data even from the record with empty date field

You search on what you need to search on.
Anytime you use Or Null on a field, that is what you are going to get, regardless of what you are searching on. So if that Null date field record had 10, you would have had that with your search, but you brought that in becauuse you said OR null for the date field, so you would also get every record with a Null in that date field.

It is called logic for a reason.

[guest]

For now I replaced empty date field with date 1-1-1900.
Thanks

[guest]

I made it sort of with little cheating. I changed empty date field with 1-1-1900 and then with conditional formatting made font color same as background color so it looks like empty but because there is something search work too. Problem solved.

[Welshgasman]

You will likely forget that in a couple on months or so? then be wondering why something does not work.

[moke123]

I'm not a fan of placeholder records either. I prefer to deal with the nulls.

Is your date field a date datatype? I ask because the 8th column over appears to be a date field but some records contain a \ which would indicate it is text datatype.

I think I'd try using a filter rather than a query.

[guest]

table contain all types of data field: text, numeric, date and link. / is instead of empty field maybe more familiar for you will be N/A. i tried to eliminate empty fields they may be problematic. I only use them when i must to have empty field. if there is no combo box for some field it is not important field is empty or not.

[guest]

Problem is that i am a novice. don't know macro sql vba or anything like that.

[mike60smart]

Hi
Can you upload a copy of the database?

[Welshgasman]

The problem is you are asking for a null date and then getting any that exist?