Iif, And Expressions in a Query (Working with Dates)

V

Vylent Fyre

I am trying to create an expression that will look at the 1st Pmt Date Long
and if it's <= #1/1/2007# and #1/1/2007# < Last Pmt Date Long, to bring a
field called Total Payment Forward. So, if the 1st Pmt Date Long is 3/1/2004
and the Last Pmt Date Long is 10/1/2009, it should be bringing the Total
Payment Forward. At least, that is my goal. Below is my expression that I'm
using in the field called "Jan 2007" :

Jan 2007: IIf([1st Pmt Date Long]<=#1/1/2007# And #1/1/2007# < [Last Pmt
Date Long],[Total Payment Forward],0)

This only returns all the 1st Pmt Date Long dates of January (all years);
but it doesn't return any other month less than January 2007. I am sure I am
missing a vital piece of the expression to make this work right - I'm hoping
you can tell me! :) :)

Thank you all in advance for your time and help! :)

VF
 
D

Dale Fye

Vylent,

What does your table structure look like? Give us a sample of a couple of
records (only those fields that effect your result), and what you expect the
output to look like.

Dale
 
V

Vylent Fyre

Dale -

Thanks so much for the reply! :)

The 1st Pmt Date Long, Last Pmt Date Long, and Total Payment Forward are
expressions in the 1st query. The expression for Jan 2007 I am trying to
create (and for other months once I get this expression to work properly) are
being created in a 2nd query.

I am copying and pasting a few rows with just those columns so you can see
what I'm talking about below - (Query ran)


Month Booked Total Payment Forward 1st Pmt Date Long Last Pmt Date Long Jan
2007
11/1/2007 $464.86 3/1/2004 10/1/2008 $0.00
11/1/2007 $563.38 7/1/2004 2/1/2009 $0.00
11/1/2007 $556.98 11/1/2004 6/1/2009 $0.00
11/1/2007 $978.41 4/1/2005 11/1/2009 $0.00
11/1/2007 $1,061.46 5/1/2005 12/1/2009 $0.00
11/1/2007 $499.53 8/1/2005 3/1/2010 $0.00
11/1/2007 $1,033.04 1/1/2006 8/1/2010 $1,033.04
11/1/2007 $1,033.04 1/1/2006 8/1/2010 $1,033.04


Notice how in the Jan 2007 column, it's only bringing the Total Payment
forward for the 1st Pmt Date Long that is 1/1/2006 - The only thing similar
to that in my expression is <=1/1/2007, (January for month) but it should be
bringing the Total Payment Forward field for all of them since all of them
have dates that are less than 1/1/2007, and the Last Pmt Date Long is less
than 1/1/2007, as well.... Right?


Dale Fye said:
Vylent,

What does your table structure look like? Give us a sample of a couple of
records (only those fields that effect your result), and what you expect the
output to look like.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Vylent Fyre said:
I am trying to create an expression that will look at the 1st Pmt Date Long
and if it's <= #1/1/2007# and #1/1/2007# < Last Pmt Date Long, to bring a
field called Total Payment Forward. So, if the 1st Pmt Date Long is 3/1/2004
and the Last Pmt Date Long is 10/1/2009, it should be bringing the Total
Payment Forward. At least, that is my goal. Below is my expression that I'm
using in the field called "Jan 2007" :

Jan 2007: IIf([1st Pmt Date Long]<=#1/1/2007# And #1/1/2007# < [Last Pmt
Date Long],[Total Payment Forward],0)

This only returns all the 1st Pmt Date Long dates of January (all years);
but it doesn't return any other month less than January 2007. I am sure I am
missing a vital piece of the expression to make this work right - I'm hoping
you can tell me! :) :)

Thank you all in advance for your time and help! :)

VF
 
D

Dale Fye

Please post the entire SQL string from both your 1st and 2nd queries.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Vylent Fyre said:
Dale -

Thanks so much for the reply! :)

The 1st Pmt Date Long, Last Pmt Date Long, and Total Payment Forward are
expressions in the 1st query. The expression for Jan 2007 I am trying to
create (and for other months once I get this expression to work properly) are
being created in a 2nd query.

I am copying and pasting a few rows with just those columns so you can see
what I'm talking about below - (Query ran)


Month Booked Total Payment Forward 1st Pmt Date Long Last Pmt Date Long Jan
2007
11/1/2007 $464.86 3/1/2004 10/1/2008 $0.00
11/1/2007 $563.38 7/1/2004 2/1/2009 $0.00
11/1/2007 $556.98 11/1/2004 6/1/2009 $0.00
11/1/2007 $978.41 4/1/2005 11/1/2009 $0.00
11/1/2007 $1,061.46 5/1/2005 12/1/2009 $0.00
11/1/2007 $499.53 8/1/2005 3/1/2010 $0.00
11/1/2007 $1,033.04 1/1/2006 8/1/2010 $1,033.04
11/1/2007 $1,033.04 1/1/2006 8/1/2010 $1,033.04


Notice how in the Jan 2007 column, it's only bringing the Total Payment
forward for the 1st Pmt Date Long that is 1/1/2006 - The only thing similar
to that in my expression is <=1/1/2007, (January for month) but it should be
bringing the Total Payment Forward field for all of them since all of them
have dates that are less than 1/1/2007, and the Last Pmt Date Long is less
than 1/1/2007, as well.... Right?


Dale Fye said:
Vylent,

What does your table structure look like? Give us a sample of a couple of
records (only those fields that effect your result), and what you expect the
output to look like.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Vylent Fyre said:
I am trying to create an expression that will look at the 1st Pmt Date Long
and if it's <= #1/1/2007# and #1/1/2007# < Last Pmt Date Long, to bring a
field called Total Payment Forward. So, if the 1st Pmt Date Long is 3/1/2004
and the Last Pmt Date Long is 10/1/2009, it should be bringing the Total
Payment Forward. At least, that is my goal. Below is my expression that I'm
using in the field called "Jan 2007" :

Jan 2007: IIf([1st Pmt Date Long]<=#1/1/2007# And #1/1/2007# < [Last Pmt
Date Long],[Total Payment Forward],0)

This only returns all the 1st Pmt Date Long dates of January (all years);
but it doesn't return any other month less than January 2007. I am sure I am
missing a vital piece of the expression to make this work right - I'm hoping
you can tell me! :) :)

Thank you all in advance for your time and help! :)

VF
 
V

Vylent Fyre

Thanks, Dale! Below is a copy and paste of both of my queries in SQL's -

(Note – I took out all the redundant names)

1st Query – ARI INSIGHTS 1

SELECT [Month Booked], Client, Vehicle, Div, Yard, [Bill Client Data], [In
Service], [Rental Amt], [Interest Amt], [Tax Amt], [Mgmt Fee], [Ins Fee],
[Total Billed], [Invoice Number], [Invoice Date], [Cap Cost], [Book Value],
[Mos Bill], [1st Bill], [Last Bill], [CLIENT DATA1], VIN, [Lease Term], [AFE
Number], IIf([In Service] Is Null,0,IIf([Lease Term] Is Null,0,IIf([Cap Cost]
Is Null,0,[Cap Cost]/[Lease Term]))) AS [ARI Final Amount], IIf(NZ([Lease
Term])=0,0,IIf([Lease Term]=36,[Cap Cost]*0.02778,IIf([Lease Term]=44,[Cap
Cost]*0.0225,IIf([Lease Term]=50,[Cap Cost]*0.0199987,IIf([Lease
Term]=55,[Cap Cost]*0.01819,IIf([Lease Term]=60,[Cap Cost]*0.01667)))))) AS
[Term Payment], NZ([Cap Cost])/1000*0.4 AS [Admin Fee], IIf([Month
Booked]<[Last Pmt Date Long],[Cap Cost]-[Term Payment]*[Current Mos
Billed],0) AS [Curr Cap Cost], IIf([Month Booked]<[Last Pmt Date Long],[Book
Value]-[Term Payment]*[Current Mos Billed],0) AS [Curr BV], NZ([Curr Cap
Cost])*(0.053472737/12) AS [Curr Interest], DateDiff("m",[1st Pmt Date
Long],[Month Booked])+1 AS [Current Mos Billed], IIf([Curr Interest]<0,[ARI
Final Amount],IIf([Month Booked]<[Last Pmt Date Long],[ARI Final
Amount]+[Admin Fee]+[Curr Interest]+[Mgmt Fee],0)) AS [Total Payment
Forward], NZ(DateAdd("m",+1,[In Service])) AS [1st Pmt Date Long Org],
NZ(DateAdd("m",[Lease Term],[1st Pmt Date Long])) AS [Last Pmt Date Long
Org], NZ([1st Pmt Date Month] & "/1/" & [1st Pmt Date Year]) AS [1st Pmt Date
Long], NZ([Last Pmt Date Month] & "/1/" & [Last Pmt Date Year]) AS [Last Pmt
Date Long], NZ(Month([1st Pmt Date Long Org])) AS [1st Pmt Date Month],
NZ(Month([Last Pmt Date Long Org])) AS [Last Pmt Date Month], NZ(Year([1st
Pmt Date Long Org])) AS [1st Pmt Date Year], NZ(Year([Last Pmt Date Long
Org])) AS [Last Pmt Date Year], Format$(NZ([1st Pmt Date
Long]),"mmm-yyyy",0,0) AS [1st Pmt Date], Format$(NZ([Last Pmt Date
Long]),"mmm-yyyy",0,0) AS [Last Pmt Date], Year([Month Booked]) AS [Booked
Year]
FROM [tbl_ARI INSIGHTS]
GROUP BY [Month Booked], Client, Vehicle, Div, Yard, [Bill Client Data], [In
Service], [Rental Amt], [Interest Amt], [Tax Amt], [Mgmt Fee], [Ins Fee],
[Total Billed], [Invoice Number], [Invoice Date], [Cap Cost], [Book Value],
[Mos Bill], [1st Bill], [Last Bill], [CLIENT DATA1], VIN, [Lease Term], [AFE
Number]
HAVING ((([In Service])>0) AND (([Lease Term])>0));


2nd Query – ARI INSIGHTS MONTHLY

SELECT [Month Booked], Vehicle, Div, Yard, [In Service], [Lease Term], [AFE
Number], [ARI Final Amount], [Term Payment], [Admin Fee], [Curr Cap Cost],
[Curr BV], [Curr Interest], [Current Mos Billed], [Total Payment Forward],
IIf([Month Booked]=[1st Pmt Date Long] Or [Month Booked]>[1st Pmt Date
Long],IIf([Month Booked]<[Last Pmt Date Long],[Total Payment
Forward]*[Current Mos Billed],0)) AS [Total Paid], IIf([Total Paid]>0,[Cap
Cost]-[Total Paid],0) AS [Total Amount Left], IIf([Month Booked]=[1st Pmt
Date Long] Or [Month Booked]>[1st Pmt Date Long] And [Month Booked]<[Last Pmt
date Long],[Lease Term]-[Current Mos Billed],0) AS [Mos Rmng], IIf([1st Pmt
Date Long]<=#1/1/2007# And #1/1/2007#<[Last Pmt Date Long],[Total Payment
Forward],0) AS [Jan 2007
FROM [ARI INSIGHTS 1];


Dale Fye said:
Please post the entire SQL string from both your 1st and 2nd queries.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Vylent Fyre said:
Dale -

Thanks so much for the reply! :)

The 1st Pmt Date Long, Last Pmt Date Long, and Total Payment Forward are
expressions in the 1st query. The expression for Jan 2007 I am trying to
create (and for other months once I get this expression to work properly) are
being created in a 2nd query.

I am copying and pasting a few rows with just those columns so you can see
what I'm talking about below - (Query ran)


Month Booked Total Payment Forward 1st Pmt Date Long Last Pmt Date Long Jan
2007
11/1/2007 $464.86 3/1/2004 10/1/2008 $0.00
11/1/2007 $563.38 7/1/2004 2/1/2009 $0.00
11/1/2007 $556.98 11/1/2004 6/1/2009 $0.00
11/1/2007 $978.41 4/1/2005 11/1/2009 $0.00
11/1/2007 $1,061.46 5/1/2005 12/1/2009 $0.00
11/1/2007 $499.53 8/1/2005 3/1/2010 $0.00
11/1/2007 $1,033.04 1/1/2006 8/1/2010 $1,033.04
11/1/2007 $1,033.04 1/1/2006 8/1/2010 $1,033.04


Notice how in the Jan 2007 column, it's only bringing the Total Payment
forward for the 1st Pmt Date Long that is 1/1/2006 - The only thing similar
to that in my expression is <=1/1/2007, (January for month) but it should be
bringing the Total Payment Forward field for all of them since all of them
have dates that are less than 1/1/2007, and the Last Pmt Date Long is less
than 1/1/2007, as well.... Right?


Dale Fye said:
Vylent,

What does your table structure look like? Give us a sample of a couple of
records (only those fields that effect your result), and what you expect the
output to look like.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I am trying to create an expression that will look at the 1st Pmt Date Long
and if it's <= #1/1/2007# and #1/1/2007# < Last Pmt Date Long, to bring a
field called Total Payment Forward. So, if the 1st Pmt Date Long is 3/1/2004
and the Last Pmt Date Long is 10/1/2009, it should be bringing the Total
Payment Forward. At least, that is my goal. Below is my expression that I'm
using in the field called "Jan 2007" :

Jan 2007: IIf([1st Pmt Date Long]<=#1/1/2007# And #1/1/2007# < [Last Pmt
Date Long],[Total Payment Forward],0)

This only returns all the 1st Pmt Date Long dates of January (all years);
but it doesn't return any other month less than January 2007. I am sure I am
missing a vital piece of the expression to make this work right - I'm hoping
you can tell me! :) :)

Thank you all in advance for your time and help! :)

VF
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top