Multi Criteria Searchform

[cap.zadi]

Hello,

I am using a form with multi search parameters to retrieved the data from Query through report its working but no data display?


The report query having the below fields and criteria which is pulling data through a search form.


TestDate

Between [Forms]![Searchform].[cbotestdate1] And [Forms]![Searchform].[cbotestdate2] & "*"

Product

[forms]![searchform].[cboproduct] & "*"


ProdnDate

Between [Forms]![Searchform].[cboprdndate1] And [Forms]![Searchform].[cboprdndate2] & "*"

other fields which have no criteria

lenght

Like "*"



standard

Like "*"


can some one advise where i am mistaken please?


zee

[alansidman]

You have & "*" for your second combo date box but not your first. Why? Same for the ProdnDate. Oversight or for a reason.

[cap.zadi]

Yes Sir there is two date fields one is Test date and the other one is the sample production date.

Yes i think you are right i should use & "*" for the 1st combo box too. because the query is performed with out any error but no results so some thing is missing.

i will try by this way and will revert. any other suggestion?

[cap.zadi]

Sir,

I hope you are fine. i attach the database sample for search form and please advise where is mistaken?

Sir is if it starts to work then is it possible to add command for Print, preview and email the report?

so nice of you.

thanks

zee

[alansidman]

In your search form you have created a number of unbound combo boxes. This is a good start, but you have not set up any type of record source for these combo boxes. You need to link them to record source tables so that your users can select the appropriate products, kilns, etc. I would not make the dates combo boxes, but instead would format them as dates and let Access give you the Calendar Popup for them. Once you have set this up in your form, then you should be able to test the query for results.

In the code for the clear button, add a line me.requery or else the event to clear your search criteria may not be perfect. Post back once you get your combo boxes set and your calendar events set and we can take it to the next step.

[cap.zadi]

Hello Sir, good morning. nice to hear from you and thanks for appreciation. i will do accordingly and revert you with results. thanks for ur time.

[cap.zadi]

Sir,

I set up the record source for those unbound boxes like product and kilns but again the empty report is displaying?

please advise?

[alansidman]

Please post your db once again with your new lookup tables linked to your comboboxes. It is difficult to evaluate issues when you don't see the data and forms and queries.

[cap.zadi]

Sir here is db.

thanks

[alansidman]

I got it working. I deleted all the Like "*" in your underlying query as you don't need them. Additionally, because you are using an exact reference in your search table, you don't need the & "*" in your criteria for the other fields. That is generally used when you have a Like command in your criteria. In you SQL statements for your lookups, I added a group by and sort so that it only lists each item once and not multiple times as a selection.


Alan

[cap.zadi]

Hey Sir, Happy New Year. May bring prosperity and progress in your life and family.

i am sorry sir, it is not working with me. same condition.

please advise.

thanks

[June7]

It works only if values are input in all dates and Product and Kiln. If report doesn't show records then no records meet the input criteria. How do you want this to work?

[cap.zadi]

Sir, I would selection Criteria based on one or two or all combo boxes. like if i need report by Test Date so after filling up all other records must follow this date criteria only.

same if i select Test Date and then kiln so query must follow this and give all other records as it is meeting with above selection area.

so some times i need to select one box or some times two or more.

thanks

[June7]

I think Alan must have thought you wanted ALL the values to be provided. If you want to allow variable combinations of criteria, I think he will agree that the Like and wildcard will be needed for the Kiln and Product fields. Date ranges are another issue. Like and wildcard don't work with BETWEEN AND, at least I can't figure how if possible. Need to handle the null input. I suggest Nz function to set an alternate date value. From would be the earliest possible date or some date far in the past (maybe 1/1/1900) and To would be current date or some value far into the future. Omit criteria for other fields.

Between Nz([Forms]![Searchform].[cbotestdate1],#1/1/1900#) And Nz([Forms]![Searchform].[cbotestdate2],Date())

Between Nz([Forms]![Searchform].[cboprdndate1],#1/1/1900#) And Nz([Forms]![Searchform].[cboprdndate2],Date())

Like [forms]![searchform].[cboproduct] & "*"

Like [forms]![searchform].[cbokiln] & "*"

[alansidman]

Thanks June. You are right. I interpreted the OP needs as filling in all variable fields. Concur with the Like for Kiln and Products. I really like your idea on the NZ function. I am not sure I would have thought of that for use with a date. I will pack that one away in the back of my mind for future use.

Happy New Year to all.