Another Date Query

  • Thread starter Thread starter KiowaPilot
  • Start date Start date
K

KiowaPilot

I have to make a query based on two Tables, Table 1 (Flight_Input) contains
events for a list of individuals. Table 2 (Pilots) contains a list of
individuals with four additional columns, these four columns are Start and
End dates for two semi-annual periods that are based on Birth Month. The
query that I need to complete is the totals of certain events during the
current annual period that an individual is in. The SQL that I have is this,
but is not narrowing down the query to just the Start and End period that
includes the current Date.

TRANSFORM NZ(Sum(Flight_Input.HRS),0) AS SumOfHRS
SELECT Flight_Input.LName, Sum(Flight_Input.HRS) AS [Total Of HRS]
FROM Pilots LEFT JOIN Flight_Input ON Pilots.Lname=Flight_Input.Lname
WHERE (((Flight_Input.FLT_Date) Between [Start1stSemi] And [End1stSemi])
AND ((Date())<>False)) OR (((Flight_Input.FLT_Date) Between [Start2ndSemi]
And [End2ndSemi]) AND ((Date())<>False))
GROUP BY Flight_Input.LName
PIVOT Flight_Input.FS_ID;
 
This expression is always going to return true
Date()<>False
Because Date() is always going to return the current system date and that is
never going to be equal to false (0).

PERHAPS what you want is something along the lines of the following.

TRANSFORM NZ(Sum(Flight_Input.HRS),0) AS SumOfHRS
SELECT Flight_Input.LName
, Sum(Flight_Input.HRS) AS [Total Of HRS]
FROM Pilots LEFT JOIN Flight_Input ON Pilots.Lname=Flight_Input.Lname
WHERE
(Flight_Input.FLT_Date Between [Start1stSemi] And [End1stSemi]
AND Date() Between [Start1stSemi] And [End1stSemi])

OR (Flight_Input.FLT_Date Between [Start2ndSemi] And [End2ndSemi]
AND Date() Between [Start2ndSemi] And [End2ndSemi])
GROUP BY Flight_Input.LName
PIVOT Flight_Input.FS_ID;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
your awesome, I tried it that way but I must have goofed it somehow.

John Spencer MVP said:
This expression is always going to return true
Date()<>False
Because Date() is always going to return the current system date and that is
never going to be equal to false (0).

PERHAPS what you want is something along the lines of the following.

TRANSFORM NZ(Sum(Flight_Input.HRS),0) AS SumOfHRS
SELECT Flight_Input.LName
, Sum(Flight_Input.HRS) AS [Total Of HRS]
FROM Pilots LEFT JOIN Flight_Input ON Pilots.Lname=Flight_Input.Lname
WHERE
(Flight_Input.FLT_Date Between [Start1stSemi] And [End1stSemi]
AND Date() Between [Start1stSemi] And [End1stSemi])

OR (Flight_Input.FLT_Date Between [Start2ndSemi] And [End2ndSemi]
AND Date() Between [Start2ndSemi] And [End2ndSemi])
GROUP BY Flight_Input.LName
PIVOT Flight_Input.FS_ID;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have to make a query based on two Tables, Table 1 (Flight_Input) contains
events for a list of individuals. Table 2 (Pilots) contains a list of
individuals with four additional columns, these four columns are Start and
End dates for two semi-annual periods that are based on Birth Month. The
query that I need to complete is the totals of certain events during the
current annual period that an individual is in. The SQL that I have is this,
but is not narrowing down the query to just the Start and End period that
includes the current Date.

TRANSFORM NZ(Sum(Flight_Input.HRS),0) AS SumOfHRS
SELECT Flight_Input.LName, Sum(Flight_Input.HRS) AS [Total Of HRS]
FROM Pilots LEFT JOIN Flight_Input ON Pilots.Lname=Flight_Input.Lname
WHERE (((Flight_Input.FLT_Date) Between [Start1stSemi] And [End1stSemi])
AND ((Date())<>False)) OR (((Flight_Input.FLT_Date) Between [Start2ndSemi]
And [End2ndSemi]) AND ((Date())<>False))
GROUP BY Flight_Input.LName
PIVOT Flight_Input.FS_ID;
Click to expand...
Click to expand...
 
Back
Top