Date Fields

[smalty]

I have a form that includes a 'Date From' and a 'Date To' field. These values are passed to a selection query. I don't have a problem when these fields are used ie. dates are entered by the user. My problem is I want the user to be able to leave these fields blank and have the query select all records. I know it's probably really simple to solve .....just cannot get my head round it.

Any suggestions appreciated.

Smalty

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[smalty]

SELECT tClient.FullName, Sum(tFeesComms.TotFeesCommGross) AS SumOfTotFeesCommGross, Sum(tFeesComms.TotFeesCommNet) AS SumOfTotFeesCommNet, Sum(tFeesComms.OnGoingGrossFee) AS SumOfOnGoingGrossFee, Sum(tFeesComms.OnGoingNetFee) AS SumOfOnGoingNetFee, Sum(tFeesComms.TotRemunerationGross) AS SumOfTotRemunerationGross, Sum(tFeesComms.TotRemunerationNet) AS SumOfTotRemunerationNet
FROM tClient INNER JOIN tFeesComms ON tClient.ID = tFeesComms.ClientID
GROUP BY tClient.FullName, tClient.ClientStatus, tFeesComms.AdviceDate
HAVING (((Sum(tFeesComms.TotRemunerationGross))>0) AND ((tClient.ClientStatus)="Signed Up") AND [highlight #EF2929]((tFeesComms.AdviceDate) Between [Forms]![Switchboard]![DtFrom] And [Forms]![Switchboard]![DtTo]));
[/highlight]

 

[PHV]

SELECT tClient.FullName, Sum(tFeesComms.TotFeesCommGross) AS SumOfTotFeesCommGross, Sum(tFeesComms.TotFeesCommNet) AS SumOfTotFeesCommNet, Sum(tFeesComms.OnGoingGrossFee) AS SumOfOnGoingGrossFee, Sum(tFeesComms.OnGoingNetFee) AS SumOfOnGoingNetFee, Sum(tFeesComms.TotRemunerationGross) AS SumOfTotRemunerationGross, Sum(tFeesComms.TotRemunerationNet) AS SumOfTotRemunerationNet
FROM tClient INNER JOIN tFeesComms ON tClient.ID = tFeesComms.ClientID
WHERE tClient.ClientStatus)="Signed Up"
AND (tFeesComms.AdviceDate>=[Forms]![Switchboard]![DtFrom] OR [Forms]![Switchboard]![DtFrom] IS NULL)
AND (tFeesComms.AdviceDate<=[Forms]![Switchboard]![DtTo] OR [Forms]![Switchboard]![DtTo] IS NULL)
GROUP BY tClient.FullName, tClient.ClientStatus, tFeesComms.AdviceDate
HAVING Sum(tFeesComms.TotRemunerationGross))>0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[smalty]

That works now when no dates are entered but if dates are entered nothing is selected

 

[smalty]

Apologies.......it is working.............thank you for your very prompt help

regards
Smalty