query/sql criteria help using forms

[rede96]

I am trying to create a query to be used by a report where the criteria are based on certain controls in a form.

EG ‘forms![FormName]![Control]’

As the fields are date fields, I am using 2 controls in the form and the ‘Between’ operator to choose a ‘From’ and ‘To’ date to search.

EG ‘between forms![FormName]![Control1]) and forms![FormName]![Control2]’

Not all of the records in the various date fields have dates in them, some are just null.

I wanted the query to look at the form and see that if the date controls in the form were blank (or null) then show all records for that field in the query. If the controls in the form have dates in them, then just filter the records in the query that meet that critera. (i.e. the dates in the control.)

The problem is I can’t find a way to do it!

I tried this in the query criteria for a particular date field:

Between [Forms]![Frm_Returns]![ReturnsFrom] And Forms]![Frm_Returns]![ReturnsTo] Or Not Like "*" Or Not Like IIf(IsNull([Forms]![Frm_Returns]![ReturnsFrom]),"!","*")

That seems to work for just one of the date fields, but I have 8 date fields in the query and 8 controls in the form that I need to enter criteria in a similar way. When I try doing this on more then 1 field, it just doesn’t work!

Can anyone help please?

 

[Jon K]

You can put the criteria for each date field in a new column in the query grid like this (using one new column for one date field):-

-----------------------------------------------
Field: [DateFieldName] between [Forms]![FormName]![Control1] and [Forms]![FormName]![Control2] OR [Forms]![FormName]![Control1] Is Null OR [Forms]![FormName]![Control2] Is Null

Show: uncheck

Criteria: True
----------------------------------------------

Now when either [Control1] or [Control2] is left blank, all the records for that field, including null values, will be returned.

The True in the Criteria row tells Access the expression in the Field row is a criterion.
.

 

[rede96]

Thanks for tip Jon.

I've also been messing around with generating queries by forms, using SQL. It looks like it will take a bit to get my head around it but it should do all tha I need.

Thanks again.

Red