Query by Form Criteria Issues

[A Sleeve of Oreos]

[Solved] Query by Form Criteria Issues

Hi everyone,

I'm relatively new to access, and a new member to this forum.

I have a Query by Form for a database search. I have two date controls on the form, "begin_date" and "end_date". The goal is for the query to return the records between the two user-entered dates. However, if the user omits the end_date, the records after the begin_date should be returned. Likewise, if the user omits the begin_date, all records before the end_date should be returned. Finally, if the user omits both dates, all records should be returned. Is this possible to actually write in one criteria section in the query?

Thus far, I have attempted a few iterations of the above. My current one only returns records when both date entries are omitted (The input dates I've tested were targeted to specifically return records). Below is what I've put in the criteria section.

Like IIf((Nz([Forms]![Database_Search]![Begin_Date_Search])+Nz([Forms]![Database_Search]![End_Date_Search]))=0,"*",IIf(IsNull([Forms]![Database_Search]![End_Date_Search])=True,([Quote_Work_Sheet_Entries].[Quote_Date])>[Forms]![Database_Search]![Begin_Date_Search],IIf(IsNull([Forms]![Database_Search]![Begin_Date_Search])=True,([Quote_Work_Sheet_Entries].[Quote_Date])<[Forms]![Database_Search]![End_Date_Search],([Quote_Work_Sheet_Entries].[Quote_Date])>=[Forms]![Database_Search]![Begin_Date_Search] And ([Quote_Work_Sheet_Entries].[Quote_Date])<=[Forms]![Database_Search]![End_Date_Search])))

In this case [Quote_Date] is the field in the table [Quote_Work_Sheet_Entries] being searched. I would greatly appreciate any help!

Thanks, Andre

 

[plog]

Can any of your start dates be before 1/1/1901? Can any of your end dates be after 12/31/2300?

I'd use those as the defaults when no data is supplied and call it a day.

 

[A Sleeve of Oreos]

Can any of your start dates be before 1/1/1901? Can any of your end dates be after 12/31/2300?

I'd use those as the defaults when no data is supplied and call it a day.

Thanks for the quick response plog, those date boundaries would definitely work! By defaults, do you mean for the true parts of my nested iif statements to be "Between" #1/1/1901# and end_date, and Between begin_date and #12/31/2300#? I have tried implementing your suggestion in this way, but I still only have records returned if both dates are omitted. I feel like my syntax is wrong or something. Here's what I have now:

Like IIf((Nz([Forms]![Database_Search]![Begin_Date_Search])+Nz([Forms]![Database_Search]![End_Date_Search]))=0,"*",IIf(IsNull([Forms]![Database_Search]![End_Date_Search])=True,([Quote_Work_Sheet_Entries].[Quote_Date]) Between [Forms]![Database_Search]![Begin_Date_Search] And #12/31/2300#,IIf(IsNull([Forms]![Database_Search]![Begin_Date_Search])=True,([Quote_Work_Sheet_Entries].[Quote_Date]) Between #1/1/1901# And [Forms]![Database_Search]![End_Date_Search],([Quote_Work_Sheet_Entries].[Quote_Date]) Between [Forms]![Database_Search]![Begin_Date_Search] And [Forms]![Database_Search]![End_Date_Search])))

 

[plog]

Scrub all that. In design view of your query, bring down the Start date from your datasource. Then under that, in the criteria section use a simple iif statement to determine what to compare it too. Something like this:

>iif(IsNull([FormInputStartDate]), "1/1/1900", [FormInputStartDate])

Then do the corresponding thing for the End date.

 

[BeeJayEff]

Personally speaking, I don't like such long and complex expressions. I would probably add two unbound fields to the form, say Start_Date and End_Date, each with its Visible property set to No. I would then have a Search Command Button, whose OnClick event would first set Start_Date to #1/1/1901# then overwrite that value with Begin_Date_Search if it is not null. Similar idea for End_Date. Then the query needs only a single Between Start_Date and End_Date clause.

Edited : plog got in first with a similar principle !

 

[plog]

I don't like users opening queries, nor do I trust those idiots to input good data. If I were to do this I would create a report upon my query, build a form for user's to submit data, then have a button for them to open the report. The report would build a filter string using the input values (after validating them) and then open the report to just the records that meet their criteria by using the DoCmd.OpenReport method.

 

[A Sleeve of Oreos]

Thank you both for your excellent solution ideas. Based on your suggestions, I wrote a couple of "if then" statements in the on-click event on my "Run Seach" button. My query criteria is just a simple "Between" statement now, and everything works like a charm!

Also, I agree with you plog, when it comes to users opening queries. I'll be following your report opening suggestion.

Thanks again!

 

[fusiondesigner]

Thank you both for your excellent solution ideas. Based on your suggestions, I wrote a couple of "if then" statements in the on-click event on my "Run Seach" button. My query criteria is just a simple "Between" statement now, and everything works like a charm!

Also, I agree with you plog, when it comes to users opening queries. I'll be following your report opening suggestion.

Thanks again!

Makes sense.