Hi, Please see the SQL from my query so far...
SELECT [Task Screen Data].[Task Reference], [Task Screen Data].Client, [Task Screen Data].[Administrator Dealing], [Task Screen Data].[Date Received], [Task Screen Data].[Task Type], [Task Screen Data].[Member Task], [Task Screen Data].[Administrator Completed], [Task Screen Data].Completed
FROM [Task Screen Data]
WHERE (([Task Screen Data].[Task Reference]=Forms!SearchForm2!tskref1 Or Forms!SearchForm2!tskref1 Is Null) And ([Task Screen Data].Client=Forms!SearchForm2!cname1 Or Forms!SearchForm2!cname1 Is Null) And ([Task Screen Data].[Administrator Dealing]=Forms!SearchForm2!admin1 Or Forms!SearchForm2!admin1 Is Null) And ([Task Screen Data].[Date Received]=Forms!SearchForm2!daterecd1 Or Forms!SearchForm2!daterecd1 Is Null) And ([Task Screen Data].[Task Type]=Forms!SearchForm2!tskdesc1 Or Forms!SearchForm2!tskdesc1 Is Null) And ([Task Screen Data].[Member Task]=Forms!SearchForm2!memtsk1 Or Forms!SearchForm2!memtsk1 Is Null) And ([Task Screen Data].[Administrator Completed]=Forms!SearchForm2!admin2 Or Forms!SearchForm2!admin2 Is Null) And ([Task Screen Data].Completed=Forms!SearchForm2!complete1 Or Forms!SearchForm2!complete1 Is Null));
This was originally set up to return only the fields that match the parameters selected from a form (searchform2). It has the nulls in so that any of the parameters on the form can be left blank if the user wishes.
I want to change the section in bold to a between date (again input from the form). I have added a new field on the form [daterecd2] for the end date and was going to use the original field above [daterecd1] as the start date.
How do I change the code to allow the betwen dates to be included, whilst also allowing them to be left blank if required (i.e. if only the other filelds are required by the user to search). I am not sure how to do this as my ideas dont seem to be working.
Any ideas would be appreciated thanks.
SELECT [Task Screen Data].[Task Reference], [Task Screen Data].Client, [Task Screen Data].[Administrator Dealing], [Task Screen Data].[Date Received], [Task Screen Data].[Task Type], [Task Screen Data].[Member Task], [Task Screen Data].[Administrator Completed], [Task Screen Data].Completed
FROM [Task Screen Data]
WHERE (([Task Screen Data].[Task Reference]=Forms!SearchForm2!tskref1 Or Forms!SearchForm2!tskref1 Is Null) And ([Task Screen Data].Client=Forms!SearchForm2!cname1 Or Forms!SearchForm2!cname1 Is Null) And ([Task Screen Data].[Administrator Dealing]=Forms!SearchForm2!admin1 Or Forms!SearchForm2!admin1 Is Null) And ([Task Screen Data].[Date Received]=Forms!SearchForm2!daterecd1 Or Forms!SearchForm2!daterecd1 Is Null) And ([Task Screen Data].[Task Type]=Forms!SearchForm2!tskdesc1 Or Forms!SearchForm2!tskdesc1 Is Null) And ([Task Screen Data].[Member Task]=Forms!SearchForm2!memtsk1 Or Forms!SearchForm2!memtsk1 Is Null) And ([Task Screen Data].[Administrator Completed]=Forms!SearchForm2!admin2 Or Forms!SearchForm2!admin2 Is Null) And ([Task Screen Data].Completed=Forms!SearchForm2!complete1 Or Forms!SearchForm2!complete1 Is Null));
This was originally set up to return only the fields that match the parameters selected from a form (searchform2). It has the nulls in so that any of the parameters on the form can be left blank if the user wishes.
I want to change the section in bold to a between date (again input from the form). I have added a new field on the form [daterecd2] for the end date and was going to use the original field above [daterecd1] as the start date.
How do I change the code to allow the betwen dates to be included, whilst also allowing them to be left blank if required (i.e. if only the other filelds are required by the user to search). I am not sure how to do this as my ideas dont seem to be working.
Any ideas would be appreciated thanks.
