wrong search results

[June7]

If you don't want records where date field Is Null then why do you include that in search criteria? Did you review the Allen Browne article I posted link for in post 6?

[Welshgasman]

If you respond to post #14, all Mike will do is go to another site and ask how to do it. :-(
That is his modus operandi.
Then we have a middleman who has no clue as to what the original person is requesting. Plus noone gets any benefit with the PMs :-(

[guest]

As i said before I fixed it for now. This database is based on document form and should look like one. i follow the rules for populating the document how it should be populated so i cant change some things. i am aware that many things can be improved shortened or added but for now i am happy that it works. it help me the most for finding documents.

[guest]

Hi Mike,
Thanks for your help. Soryy I cant upload a copy there is confindental data i cant offer publically but I can answer any question or i dont know maybe empty sample of table or something like that. write me if that is ok.

[guest]

Hi June7
For example one the date fields is empty all year and at end of the year when data is collected data is populated so it is not empty anymore. but over the year it is empty and if is null omitted from the searching so other data from that record form other fields is also omitted, that why i used null field too to have data from all records. as I said i don't know vba or something so i do it in the simplest way as i know to do.

[June7]

Your narrative seems contradictory and confusing. Not clear to me what you want. If field is null, do not retrieve record? Which is what you seem to accomplish by using dummy date. What is not clear to me is why simply excluding Or Is Null from criteria doesn't get what you expect.

Yes, can make copy of database then change confidential data to dummy values. However, if db is non-English, could be difficult to analyze.

[guest]

I will give example and hope show you what i mean. let say i have two records. every record has three fields. one field is data field. there are combo boxes for each field. when form is opened subform is populated with all records (two) and then i can search with one or more combo boxes. if one record contain empty date field subform will open populated only with one record no both (record that contain emptu date field will be omitted). in that way i will miss data from missing record. that data will not be searched no matter which combo box i choose. that why i need date field to have some value. I don't know how to get both records if data field is empty. i will make copy and change to english later.

[June7]

You mean "date" field, not "data" field.

Okay, think I am understanding now. I don't normally use dynamic parameter query. I use VBA to build filter criteria, hence the link I posted.

I also don't use macros so not sure how yours plays into this issue. I never use [Screen].[ActiveControl].

You never have shown your complete SQL statement and all 3 criteria. Are you using AND or OR? OR works for me without dummy value for null date field.

[guest]

Here is the database. delete date of receiving to see the difference. there are two records entered. Search.zip

[June7]

You provided a 64-bit version of accde. I run 32-bit Access so cannot open this db.

[guest]

Sorry I don't know how to convert it to 32-bit but thanks anyway.

[June7]

Provide the accdb version.

[guest]

here i send it again in accdb version. Search.zip

[June7]

Don't see need for main form to even have RecordSource.

Table will not allow 1-1-1900 because of ValidationRule. So exactly how did you use 1/1/1900 - calculate with Nz() in query? I tried that and embedded macro errors.

I am now back to confused. I added a record that does not have DateOfReceipt and has different Mark. I tried filtering on just BasicNumber and Mark criteria. The Mark criteria doesn't work - all records return.

Oh well, as long is it all works to your satisfaction...

[guest]

forget to delete validation rule when translating. i send you new version with deleted validation rule. mark can be different for each record. try now and you will see it works if not empty date field (work with 1-1-1900 too). if there is record with empty field that record is added to every search. Search.zip