Hi, I have the following qry which I need to filter to show only records which dates are between one month before the current date and all the following records, example:
if the current date is 26th April I will need all records from the 1st of March onwards, if the date is 1st May I will need all the ecords form the 1st of April.
This is the SQL I have got:
SELECT tblTrip.IDTrip, tblTrip.TripDate, tblTrip.NDays, tblTrip.FlyingTime, tblTrip.TAFB, tblTrip.DutyPayRate, [TAFB]*[DutyPayRate] AS [Duty Pay], tblTrip.PSR, tblTrip.DutyType, qryFlightTimeOperation.[Tot Flying Time], qryFlightTimeOperation.[Tot Sectors], qryFlightTimeOperation.[Non Operating Sectors], qryFlightTimeOperation.[Operating Sectors], Sum(tblAdditionalAllowances.AmmountAdditionalAllow ances) AS SumOfAmmountAdditionalAllowances
FROM (tblTrip LEFT JOIN qryFlightTimeOperation ON tblTrip.IDTrip = qryFlightTimeOperation.IDTrip) LEFT JOIN tblAdditionalAllowances ON tblTrip.IDTrip = tblAdditionalAllowances.IDTrip
GROUP BY tblTrip.IDTrip, tblTrip.TripDate, tblTrip.NDays, tblTrip.FlyingTime, tblTrip.TAFB, tblTrip.DutyPayRate, [TAFB]*[DutyPayRate], tblTrip.PSR, tblTrip.DutyType, qryFlightTimeOperation.[Tot Flying Time], qryFlightTimeOperation.[Tot Sectors], qryFlightTimeOperation.[Non Operating Sectors], qryFlightTimeOperation.[Operating Sectors]
ORDER BY tblTrip.TripDate;

Thanks Marco

Hi -

Looking at your sql, I'm left unsure what/where you are trying to filter.

Having said that, you can copy/paste the following function to a standard module and call as shown
to return the 1st day of the first full month preceding the input date:

Public Function QuikDate(dteNow As Date) As Date
'*******************************************
'Re: http://www.access-programmers.co.uk/forums/showthread.php?t=170887
'Purpose: Return 1st day (date) of 1st full
' month preceding the input date
'Coded by: raskew
'Inputs: 1) ? quikdate(#4/26/09#)
' 2) ? quikdate(#5/1/09#)
'Output: 1) 3/1/2009
' 2) 4/1/2009
'*******************************************

QuikDate = DateSerial(year(dteNow), month([dteNow]) - 1, 1)

End Function

HTH - Bob

Thanks Bob, where will I then place the function? Does that go in the criteria in the qyery?
Thanks

Here's your query SQL in somewhat readable format. As I pointed out before
Looking at your sql, I'm left unsure what/where you are trying to filter I'll take a guess and say it's tblTrip.TripDate, and as you've explained filter to show only records which dates are between one month before the current date and all the following records. So, placing your sql into design view and having copied/pasted Function QuikDate() to a standard module, in the criteria cell of tblTrip.TripDate:
>= QuikDate(date())

SELECT
tblTrip.IDTrip
, tblTrip.TripDate
, tblTrip.NDays
, tblTrip.FlyingTime
, tblTrip.TAFB
, tblTrip.DutyPayRate
, [TAFB]*[DutyPayRate] AS [Duty Pay]
, tblTrip.PSR
, tblTrip.DutyType
, qryFlightTimeOperation.[Tot Flying Time]
, qryFlightTimeOperation.[Tot Sectors]
, qryFlightTimeOperation.[Non Operating Sectors]
, qryFlightTimeOperation.[Operating Sectors]
, Sum(tblAdditionalAllowances.AmmountAdditionalAllow ances) AS SumOfAmmountAdditionalAllowances
FROM
(tblTrip
LEFT JOIN
qryFlightTimeOperation
ON
tblTrip.IDTrip = qryFlightTimeOperation.IDTrip)
LEFT JOIN
tblAdditionalAllowances
ON
tblTrip.IDTrip = tblAdditionalAllowances.IDTrip
GROUP BY
tblTrip.IDTrip
, tblTrip.TripDate
, tblTrip.NDays
, tblTrip.FlyingTime
, tblTrip.TAFB
, tblTrip.DutyPayRate
, [TAFB]*[DutyPayRate]
, tblTrip.PSR
, tblTrip.DutyType
, qryFlightTimeOperation.[Tot Flying Time]
, qryFlightTimeOperation.[Tot Sectors]
, qryFlightTimeOperation.[Non Operating Sectors]
, qryFlightTimeOperation.[Operating Sectors]
ORDER BY
tblTrip.TripDate;

Please post back with your results.

Bob

Thanks very much, it works perfectly!
Marco