V
Vylent Fyre
Hello, everyone! It's me again.. *oh no!*
Seriously, I'm having some issues with getting the dates and expressions I'm
using to pull the right information...
I'm taking the Begin Date, which is, for example, 9/8/2004, and my
expression is converting it to 10/1/2004 [1st Pmt Date Long]. The End Date,
for example is 9/8/2009, which I have another expression converting it to
9/1/2009 [Last Pmt Date Long]. Now, my problem lies in the expression I have
trying to pull the Monthly Amount if, for example, [Jan 2007] >= [1st Pmt
Date Long] [Jan 2007] <[Last Pmt Date Long],[Mthly Amount],0). It works fine
for January, but not for February. I did an Iif statement to tell me that
it's finding it to be TRUE or 0 if [1st Pmt Date Long] Between #2/1/2007# and
#2/1/2007# Or [1st Pmt Date Long] > #2/1/2007#, "True",0). My results are
quite interesting and I'm not able to pinpoint why - Below is a copy and
paste of the ending results in the query -
Month Begin Date End Date Mthly Amount 1st Pmt Date Long Last Pmt Date
Long Jan 2007 Feb 2007 1-2007 2-2007
1/1/2007 9/8/2004 9/8/2009 1,436.90 10/1/2004 10/1/2009 1/1/2007 2/1/2007 $1,436.90 0
1/1/2007 9/29/2004 9/29/2009 902.05 10/1/2004 10/1/2009 1/1/2007 2/1/2007 $902.05 0
1/1/2007 9/29/2004 9/29/2009 451.03 10/1/2004 10/1/2009 1/1/2007 2/1/2007 $451.03 0
1/1/2007 10/13/2004 10/13/2009 1,560.25 11/1/2004 11/1/2009 1/1/2007 2/1/2007 $1,560.25 0
1/1/2007 10/25/2004 10/25/2009 1,856.80 11/1/2004 11/1/2009 1/1/2007 2/1/2007 $1,856.80 0
1/1/2007 11/17/2004 11/17/2009 1,361.49 12/1/2004 12/1/2009 1/1/2007 2/1/2007 $1,361.49 0
1/1/2007 11/17/2004 11/17/2009 1,361.49 12/1/2004 12/1/2009 1/1/2007 2/1/2007 $1,361.49 0
1/1/2007 11/17/2004 11/17/2009 454.24 12/1/2004 12/1/2009 1/1/2007 2/1/2007 $454.24 0
1/1/2007 11/17/2004 11/17/2009 907.66 12/1/2004 12/1/2009 1/1/2007 2/1/2007 $907.66 0
1/1/2007 12/20/2004 12/20/2009 1,410.31 1/1/2005 1/1/2010 1/1/2007 2/1/2007 $1,410.31 0
1/1/2007 12/20/2004 12/20/2009 1,410.31 1/1/2005 1/1/2010 1/1/2007 2/1/2007 $1,410.31 0
1/1/2007 4/14/2005 4/14/2010 3,221.54 5/1/2005 5/1/2010 1/1/2007 2/1/2007 $3,221.54 True
1/1/2007 4/14/2005 4/14/2010 6,444.53 5/1/2005 5/1/2010 1/1/2007 2/1/2007 $6,444.53 True
1/1/2007 4/14/2005 4/14/2010 4,832.31 5/1/2005 5/1/2010 1/1/2007 2/1/2007 $4,832.31 True
It should be showing True for all of them. I'm not quite pinpointing why it
isn't. Any suggestions? I'm also pasting my SQL in here as well -
qry_Op Leases Future Pmts 2nd -
SELECT Month, [Lease #], [Yard #], [Begin Date], [End Date], [Mthly Amount],
[Curr Cap Cost], [1st Pmt Date Long], [Last Pmt Date Long], IIf([Month]=[1st
Pmt Date Long] Or [Month]>[1st Pmt Date Long],IIf([Month]<[Last Pmt Date
Long],[Mthly Amount],0)) AS [Total Paid], IIf([Total Paid]>0,[Cap
Cost]-[Total Paid],0) AS [Total Amount Left], Month AS [Jan 2007],
DateAdd("m",1,[Jan 2007]) AS [Feb 2007], DateAdd("m",1,[Feb 2007]) AS [Mar
2007], DateAdd("m",1,[Mar 2007]) AS [Apr 2007], DateAdd("m",1,[Apr 2007]) AS
[May 2007], DateAdd("m",1,[May 2007]) AS [Jun 2007], DateAdd("m",1,[Jun
2007]) AS [Jul 2007], DateAdd("m",1,[Jul 2007]) AS [Aug 2007],
DateAdd("m",1,[Jul 2007]) AS [Sep 2007], DateAdd("m",1,[Jul 2007]) AS [Oct
2007], DateAdd("m",1,[Jul 2007]) AS [Nov 2007], DateAdd("m",1,[Jul 2007]) AS
[Dec 2007], IIf([Jan 2007]=#1/1/2007# And [Jan 2007]>=[1st Pmt Date Long] And
[Jan 2007]<[Last Pmt Date Long],[Mthly Amount],0) AS [1-2007], IIf([1st Pmt
Date Long] Between #2/1/2007# And #2/28/2007# Or [1st Pmt Date
Long]>#2/1/2007#,"True",0) AS [2-2007]
FROM [qry_Op Leases Future Pmts 1st];
the bottom line I'm trying to obtain is I want it to bring the Monthly
Amount for each month as long as the 1st Pmt Date is = to or > than the month
provided, and is less than the Last Pmt Date.... If you know of a better
way, please let me know!
Thanks again to everyone in advance - Your assistance and time is always
appreciated! =)
VF
Seriously, I'm having some issues with getting the dates and expressions I'm
using to pull the right information...
I'm taking the Begin Date, which is, for example, 9/8/2004, and my
expression is converting it to 10/1/2004 [1st Pmt Date Long]. The End Date,
for example is 9/8/2009, which I have another expression converting it to
9/1/2009 [Last Pmt Date Long]. Now, my problem lies in the expression I have
trying to pull the Monthly Amount if, for example, [Jan 2007] >= [1st Pmt
Date Long] [Jan 2007] <[Last Pmt Date Long],[Mthly Amount],0). It works fine
for January, but not for February. I did an Iif statement to tell me that
it's finding it to be TRUE or 0 if [1st Pmt Date Long] Between #2/1/2007# and
#2/1/2007# Or [1st Pmt Date Long] > #2/1/2007#, "True",0). My results are
quite interesting and I'm not able to pinpoint why - Below is a copy and
paste of the ending results in the query -
Month Begin Date End Date Mthly Amount 1st Pmt Date Long Last Pmt Date
Long Jan 2007 Feb 2007 1-2007 2-2007
1/1/2007 9/8/2004 9/8/2009 1,436.90 10/1/2004 10/1/2009 1/1/2007 2/1/2007 $1,436.90 0
1/1/2007 9/29/2004 9/29/2009 902.05 10/1/2004 10/1/2009 1/1/2007 2/1/2007 $902.05 0
1/1/2007 9/29/2004 9/29/2009 451.03 10/1/2004 10/1/2009 1/1/2007 2/1/2007 $451.03 0
1/1/2007 10/13/2004 10/13/2009 1,560.25 11/1/2004 11/1/2009 1/1/2007 2/1/2007 $1,560.25 0
1/1/2007 10/25/2004 10/25/2009 1,856.80 11/1/2004 11/1/2009 1/1/2007 2/1/2007 $1,856.80 0
1/1/2007 11/17/2004 11/17/2009 1,361.49 12/1/2004 12/1/2009 1/1/2007 2/1/2007 $1,361.49 0
1/1/2007 11/17/2004 11/17/2009 1,361.49 12/1/2004 12/1/2009 1/1/2007 2/1/2007 $1,361.49 0
1/1/2007 11/17/2004 11/17/2009 454.24 12/1/2004 12/1/2009 1/1/2007 2/1/2007 $454.24 0
1/1/2007 11/17/2004 11/17/2009 907.66 12/1/2004 12/1/2009 1/1/2007 2/1/2007 $907.66 0
1/1/2007 12/20/2004 12/20/2009 1,410.31 1/1/2005 1/1/2010 1/1/2007 2/1/2007 $1,410.31 0
1/1/2007 12/20/2004 12/20/2009 1,410.31 1/1/2005 1/1/2010 1/1/2007 2/1/2007 $1,410.31 0
1/1/2007 4/14/2005 4/14/2010 3,221.54 5/1/2005 5/1/2010 1/1/2007 2/1/2007 $3,221.54 True
1/1/2007 4/14/2005 4/14/2010 6,444.53 5/1/2005 5/1/2010 1/1/2007 2/1/2007 $6,444.53 True
1/1/2007 4/14/2005 4/14/2010 4,832.31 5/1/2005 5/1/2010 1/1/2007 2/1/2007 $4,832.31 True
It should be showing True for all of them. I'm not quite pinpointing why it
isn't. Any suggestions? I'm also pasting my SQL in here as well -
qry_Op Leases Future Pmts 2nd -
SELECT Month, [Lease #], [Yard #], [Begin Date], [End Date], [Mthly Amount],
[Curr Cap Cost], [1st Pmt Date Long], [Last Pmt Date Long], IIf([Month]=[1st
Pmt Date Long] Or [Month]>[1st Pmt Date Long],IIf([Month]<[Last Pmt Date
Long],[Mthly Amount],0)) AS [Total Paid], IIf([Total Paid]>0,[Cap
Cost]-[Total Paid],0) AS [Total Amount Left], Month AS [Jan 2007],
DateAdd("m",1,[Jan 2007]) AS [Feb 2007], DateAdd("m",1,[Feb 2007]) AS [Mar
2007], DateAdd("m",1,[Mar 2007]) AS [Apr 2007], DateAdd("m",1,[Apr 2007]) AS
[May 2007], DateAdd("m",1,[May 2007]) AS [Jun 2007], DateAdd("m",1,[Jun
2007]) AS [Jul 2007], DateAdd("m",1,[Jul 2007]) AS [Aug 2007],
DateAdd("m",1,[Jul 2007]) AS [Sep 2007], DateAdd("m",1,[Jul 2007]) AS [Oct
2007], DateAdd("m",1,[Jul 2007]) AS [Nov 2007], DateAdd("m",1,[Jul 2007]) AS
[Dec 2007], IIf([Jan 2007]=#1/1/2007# And [Jan 2007]>=[1st Pmt Date Long] And
[Jan 2007]<[Last Pmt Date Long],[Mthly Amount],0) AS [1-2007], IIf([1st Pmt
Date Long] Between #2/1/2007# And #2/28/2007# Or [1st Pmt Date
Long]>#2/1/2007#,"True",0) AS [2-2007]
FROM [qry_Op Leases Future Pmts 1st];
the bottom line I'm trying to obtain is I want it to bring the Monthly
Amount for each month as long as the 1st Pmt Date is = to or > than the month
provided, and is less than the Last Pmt Date.... If you know of a better
way, please let me know!
Thanks again to everyone in advance - Your assistance and time is always
appreciated! =)
VF