Filter Dates

mtagliaferri

Registered User.
Local time
Today, 12:38
Joined
Jul 16, 2006
Messages
550
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:
Code:
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.AmmountAdditionalAllowances) 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:

Code:
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
 
Last edited:
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())

Code:
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.AmmountAdditionalAllowances) 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
 

Users who are viewing this thread

Back
Top Bottom