As a newbie I am working through a problem. I have two table that have dates within them for Start and Finish. These are [PlacetEnd] and [PlaceStart]. My query instruction lets me type in freehand the date range I wish to query. I have it configured to capture the end date or to substitute todays date where that figure is null. I now need the freehand entry date to either take the date of entry or the actual date entered whichever is the most recent.
I hope the area where I think the problem is has been made bold type to highlight it in the existing SQL statement which is:
SELECT T_Placements.PlacementNumber, T_Placements.PlaceStart, T_Placements.PlacetEnd, T_Placements.RefAcceptDate, [RefAcceptDate]+56 AS 56Days, T_Placements.ClientID, T_EnterClientDemographics.ClientName, T_EnterClientDemographics.ClientSurName, [ClientName]+" "+[ClientSurName] AS ClntFullName, T_EnterClientDemographics.ClientDoB, DateDiff("yyyy",[ClientDoB],Now()) AS Age, T_Placements.CgID, T_EnterCareGiverDemographics.[CgName(s)], T_EnterCareGiverDemographics.CgSurName, [CgName(s)]+" "+[CgSurName] AS CgrFullName, T_EnterClientDemographics.RefOrg, T_EnterClientDemographics.RefSite, T_EnterClientDemographics.BdNtCgrPmnt, IIf(IsNull([PlacetEnd]),DateDiff("d",[PlaceStart],Date()),DateDiff("d",[PlaceStart],[PlacetEnd])) AS Bednights, ([Bednights])*[BdNtCgrPmnt] AS CgPmnt, T_EnterClientDemographics.PocketMoney
FROM T_EnterClientDemographics INNER JOIN (T_EnterCareGiverDemographics INNER JOIN T_Placements ON T_EnterCareGiverDemographics.CgID=T_Placements.CgI D) ON T_EnterClientDemographics.ClientID=T_Placements.Cl ientID WHERE (((T_Placements.PlaceStart)>=[Enter Start Date _ _/_ _/_ _ ]) And ((T_Placements.PlacetEnd)<=[Enter QueryEnd Date _ _/_ _/_ _ ])) Or (((IsNull(T_Placements.PlacetEnd))<>False))
ORDER BY T_EnterCareGiverDemographics.CgSurName;
I suspect that the answer to getting the [Enter Start Date _ _/_ _/_ _ ] to be the the triggering date for the datediff is also in that 1st expression. I need the program to look and if there is a date prior to, or equal to, the [Enter Start Date _ _/_ _/_ _ ] to use the [Enter Start Date _ _/_ _/_ _ ] but if later than that to use the actual date shown in the table