Parameter query

[ScottXe]

I have a parameter query using a form to collect the data. I put LIKE and wildcard enclosing the parameter to bypass the criteria if the data box is empty. It works well. I have another parameter of date type that uses between two dates. How can I bypass the criteria if I leave the data boxes blank? Thanks!

 

[pbaldy]

Wrap each form reference in the Nz() function, using an appropriate date for each.

 

[jdraw]

Please show us the query SQL you are using- there may be options.

 

[Ranman256]

You may need 2 different queries...1 if null, 1 if not.

 

[pbaldy]

You may need 2 different queries...1 if null, 1 if not.

You would really do that?

 

[ScottXe]

Please show us the query SQL you are using- there may be options.

Below is the SQL statement.

SELECT tblSKU.SKUID, tblSKU.FY, tblSKU.MainCat, tblSKU.SubCat, tblSKU.Supplier, tblSKU.Brand, tblSKU.Model, tblSKU.SKU, tblSKU.TTLSKU, tblSKU.[Product Description], tblSKU.Market, tblSKU.Handled, tblSKU.Type, tblSKU.Engineer, tblSKU.Active, tblSKU.DoCSigned
FROM tblSKU
WHERE (((tblSKU.MainCat) Like "*" & [Forms]![frmSearchCriteria]![cboMainCat] & "*") AND ((tblSKU.SubCat) Like "*" & [Forms]![frmSearchCriteria]![cboSubCat] & "*") AND ((tblSKU.DoCSigned) Between [Forms]![frmSearchCriteria]![txtStartDate] And [Forms]![frmSearchCriteria]![txtEndDate]))
ORDER BY tblSKU.DoCSigned;

 

[pbaldy]

Didn't notice my suggestion, or didn't understand it? This type of thing:

Between Nz([Forms]![frmSearchCriteria]![txtStartDate],#1/1/1900#) And Nz([Forms]![frmSearchCriteria]![txtEndDate],#12/31/2099#)

 

[ScottXe]

Hi Paul,

I now understand your suggestion fully and tried it successfully. Many thanks!

 

[pbaldy]

Happy to help!