query based on a form

[U226RM2]

Hi, can anyone help? I have set up a query that looks at a list box on a form for one of its criteria and then a text box on the form for the user to also specify a date. A command button on the form runs the query. Is there any way for the query to work if the date field is unpopulated (as well as having the option to specify a date)?

Any help gratefully received x

 

[wazz]

you can supply alternate or default values if criteria are missing using the nz function or IIf function. if you need help with those perhaps post what you have so far for some tips.

 

[Gannet]

Same problem

I'm using the criteria in my query to pull records with this expression:

IIf(IsNull([Forms]![frmReportParameters]![BeginDate]),Nz([Forms]![frmReportParameters]![BeginDate]),Between [Forms]![frmReportParameters]![BeginDate] And [Forms]![frmReportParameters]![EndDate])

It's not working and I was wondering if it's possible to do it this way.

 

[wazz]

gannet, you seem to be testing for a BeginDate several times without taking action if it is not there.

one approach is you can test your dates separately to see if they are valid:
pseudo-code:
if isnull(begindate) then alertuser or do something
elseif isnull(enddate) then alertuser or do something
else alliswell, goahead

or supply alternate values with nz:
Between Nz([Forms]![frmReportParameters]![BeginDate],SupplyAlternateDateHere) And Nz([Forms]![frmReportParameters]![EndDate], SupplyAlternateDateHere)

dates also require that they be surrounded by #. sample from msdn:
Dim datHireDate As Date
datHireDate = #5-1-92#
Forms!Employees.Filter = "[HireDate] >= #" _
& datHireDate & "#"

link: http://msdn2.microsoft.com/en-us/library/aa172370(office.11).aspx (also click on the first link in the "See Also" section.

 

[Gannet]

Ok working.

I used the new Between syntax you provided to come up with this:

Between Nz([Forms]![frmReportParameters]![BeginDate],#1/1/1900#) And Nz([Forms]![frmReportParameters]![EndDate],Now())

I placed this in the criteria for my date and it seems to be working great.

Thanks for the help Wazz.

 

[wazz]

np. tnx for posting back with your solution.