Search yields no results to calculate number of days based on Rang

[KiowaPilot]

I am trying to create a query that calculates the number of days remaining
for multiple individuals within a six month window based on their birthmonth
and what window based on the current date. I am easily able to calculate the
number of days remaining in both windows but I only want Info for the current
window for a report.
I believe that I have the WHERE clause correct but I don't know where I am
going wrong.

SELECT Pilots.LName, [Pilots.End1stSemi]-(Date()) AS 1st,
[Pilots.End2ndSemi]-(Date()) AS 2nd
FROM Pilots
WHERE (((Date()) Between [Start1stSemi] And [End1stSemi] Or (Date()) Between
[Start2ndSemi] And [End2ndSemi]));

the Table, Pilots Looks Like:

Lname BirthMonth Start1stSemi End1stSemi Start2ndSemi
End2ndSemi
Chris Aug 1 Sep 09 28 Feb 10 1 Mar 09
31 Aug 09
Mike Sep 1 Oct 08 31 Mar 09 1 Apr 09
30 Sep 09
Harold May 1 Jun 10 30 Nov 10 1 Dec 08
31 May 09
Joe Jan 1 Feb 09 31 Jul 09 1 Aug
09 31 Jan 10

If todays date was 3 Mar 09. What I am trying to do would look like:

Lname Daystillend
Chris 181
Mike 28
Harold 89
Joe 150

 

[Steve Schapel]

Kiowa,

You didn't mention what results you are getting, so we can compare the
actual output with the desired output. Can you let us know please?

Are the Start1stSemi, End1stSemi, Start2ndSemi, End2ndSemi fields of
Date/Time data type?

Your query will return data in 3 fields, i.e. LName, [1st], [2nd]. So where
does Daystillend come into it?

 

[KiowaPilot]

I would like a number value as a result which, I have done. But the result
that I have gives me two columns. 1st and 2nd. the output that I am trying to
get would be the number value from one of the two colums (1st or 2nd) that is
derived from the Pilots table End1stSemi or End2ndSemi based on a date range
that contains todays Date.
Daystillend, "thanks" apparently isnt in the SQL and probably is one reason
why it isnt working right. Can you/someone still help please?

What i currently have is:

Lname 1st 2nd
Chris 362 181
Mike 28 211
Harold 89 272
Joe 150 334

Kiowa,

You didn't mention what results you are getting, so we can compare the
actual output with the desired output. Can you let us know please?

Are the Start1stSemi, End1stSemi, Start2ndSemi, End2ndSemi fields of
Date/Time data type?

Your query will return data in 3 fields, i.e. LName, [1st], [2nd]. So where
does Daystillend come into it?

Lname BirthMonth start1stSemi End1stSemi Start2ndSemi End2ndSemi
Chris Aug 1 Sep 09 28 Feb 10 1 Mar 09 31 Aug 09
Mike Sep 1 Oct 08 31 Mar 09 1 Apr 09 30 Sep 09
Harold May 1 Jun 10 30 Nov 10 1 Dec 08 31 May 09
Joe Jan 1 Feb 09 31 Jul 09 1 Aug 09 31 Jan 10

If todays date was 3 Mar 09. What I am trying to do would look like:

Lname Daystillend
Chris 181
Mike 28
Harold 89
Joe 150

What i currently have is:

Lname 1st 2nd
Chris 362 181
Mike 28 211
Harold 89 272
Joe 150 334

 

[Steve Schapel]

Kiowa,

Ah, ok, now I understand.

Try it like this:

SELECT Pilots.LName, IIf(Date() Between [Start1stSemi] And
[End1stSemi],[End1stSemi]-Date(),IIf(Date() Between [Start2ndSemi] And
[End2ndSemi],[End2ndSemi]-Date(),0)) AS Daystillend
FROM Pilots

 

[pb110005-2351]

I am trying to create a query that calculates the number of days remaining
for multiple individuals within a six month window based on their
birthmonth
and what window based on the current date. I am easily able to calculate
the
number of days remaining in both windows but I only want Info for the
current
window for a report.
I believe that I have the WHERE clause correct but I don't know where I am
going wrong.

SELECT Pilots.LName, [Pilots.End1stSemi]-(Date()) AS 1st,
[Pilots.End2ndSemi]-(Date()) AS 2nd
FROM Pilots
WHERE (((Date()) Between [Start1stSemi] And [End1stSemi] Or (Date())
Between
[Start2ndSemi] And [End2ndSemi]));

the Table, Pilots Looks Like:

Lname BirthMonth Start1stSemi End1stSemi Start2ndSemi
End2ndSemi
Chris Aug 1 Sep 09 28 Feb 10 1 Mar
09
31 Aug 09
Mike Sep 1 Oct 08 31 Mar 09 1 Apr
09
30 Sep 09
Harold May 1 Jun 10 30 Nov 10 1 Dec
08
31 May 09
Joe Jan 1 Feb 09 31 Jul 09 1
Aug
09 31 Jan 10

If todays date was 3 Mar 09. What I am trying to do would look like:

Lname Daystillend
Chris 181
Mike 28
Harold 89
Joe 150

 

[pb110005-2351]

I am trying to create a query that calculates the number of days remaining
for multiple individuals within a six month window based on their
birthmonth
and what window based on the current date. I am easily able to calculate
the
number of days remaining in both windows but I only want Info for the
current
window for a report.
I believe that I have the WHERE clause correct but I don't know where I am
going wrong.

SELECT Pilots.LName, [Pilots.End1stSemi]-(Date()) AS 1st,
[Pilots.End2ndSemi]-(Date()) AS 2nd
FROM Pilots
WHERE (((Date()) Between [Start1stSemi] And [End1stSemi] Or (Date())
Between
[Start2ndSemi] And [End2ndSemi]));

the Table, Pilots Looks Like:

Lname BirthMonth Start1stSemi End1stSemi Start2ndSemi
End2ndSemi
Chris Aug 1 Sep 09 28 Feb 10 1 Mar
09
31 Aug 09
Mike Sep 1 Oct 08 31 Mar 09 1 Apr
09
30 Sep 09
Harold May 1 Jun 10 30 Nov 10 1 Dec
08
31 May 09
Joe Jan 1 Feb 09 31 Jul 09 1
Aug
09 31 Jan 10

If todays date was 3 Mar 09. What I am trying to do would look like:

Lname Daystillend
Chris 181
Mike 28
Harold 89
Joe 150