SQL Query

[uplink600]

Good afternoon

Could someone please have a look at the enclosed SQL (on a word doc) and advise a correction on the text coloured red. It relates to a parameter query where the user selects a date range. At present the query only works when dates are entered. If the date text boxes are left empty the query won't work but I'm unsure about the Is Null part.

Thanks

VC

 

[DLB]

SELECT [Ashworth Leeds - Industry Project Table].[Record Number],
[Ashworth Leeds - Industry Project Table].[Kerridge Ref],
[Ashworth Leeds - Industry Project Table].[Account No],
[Ashworth Leeds - Industry Project Table].[Quote Date],
[Ashworth Leeds - Industry Project Table].[Customer Name],
[Ashworth Leeds - Industry Project Table].[Sales Person],
[Ashworth Leeds - Industry Project Table].[Project Name],
[Ashworth Leeds - Industry Project Table].[Project Value]
FROM [Ashworth Leeds - Industry Project Table]
WHERE ((([Quote Date] Between [Forms]![Project Search]![txtdate1] And [Forms]![Project Search]![txtDate2] Or [Forms]![Project Search]![txtdate1] Is Null Or [Forms]![Project Search]![txtDate2] Is Null)=True) AND
(([Customer Name]=[Forms]![Project Search]![txtCustName] Or [Forms]![Project Search]![txtCustName] Is Null)=True) AND
(([Project Value]>[Forms]![Project Search]![txtValue] Or [Forms]![Project Search]![txtValue] Is Null)=True) AND
(([Sales Person]=[Forms]![Project Search]![cboNames] Or [Forms]![Project Search]![cboNames] Is Null)=True) AND
(([Account No]=[Forms]![Project Search]![txtAccount] Or [Forms]![Project Search]![txtAccount] Is Null)=True));


When either txtDate1 or txtdate2 is left blank, all Quote Dates will be returned
i.e. criteria for [Quote Date] takes effect only when both dates are entered.