Date Expressions & Calculations

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
 
D

Dale Fye

Vylent,

And you want these values to show up in columns? This is a job for a
spreadsheet, not a database, although you can do it. Try changing the lines
that refer to the Months with the following:

[Jan 2007]:IIf(DateSerial(2007, 1, 1,) BETWEEN [1st Pmt Date Long]
AND [Last Pmt Date Long],[Mthly Amount],0)
[Feb 2007]:IIf(DateSerial(2007, 2, 1,) BETWEEN [1st Pmt Date Long]
AND [Last Pmt Date Long],[Mthly Amount],0)
[Mar 2007]: IIf(DateSerial(2007, 3, 1,) BETWEEN [1st Pmt Date Long]
AND [Last Pmt Date Long],[Mthly Amount],0)

HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



Vylent Fyre said:
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
Click to expand...
 
V

Vylent Fyre

Dale,

Thanks so much for the response! :) I totally agree with you that this
normally would be the job for Excel, however, the file is over 100 megs and
it's just not feasible to keep it in Excel now.... (I'm using 2003)

I used the formula you suggested (a new one on me - Never tried out
DateSerial!) It still didn't quite produce the results that is needed. Since
1/1/2007, 2/1/2007, and 3/1/2007 are all between the 1st Pmt Date Long and
Last Pmt Date Long, it should be showing the Monthly Amount for each month,
right? I'm thinking that maybe it isn't liking the expression I used to get
the [1st Pmt Date Long] and [Last Pmt Date Long]. Since the original Begin
Date was (example) 9/28/04, I did several expressions to get it to where it
would become 10/1/2004, and the same for the Last Pmt Date Long. I created
these expressions in the 1st query, and the SQL Is below for the 1st query
which has the expressions I used for the 1st Pmt Dates and Last Pmt Dates
(I'm sure there's a better way... I thought this worked, but perhaps it
didn't and that's what my problem is?)

SELECT Month, [Lease #], [Yard #], [Begin Date], [End Date], [Cap Cost],
[Vin/Serial], [Lease Term], [Asset #], [tbl_Op Leases Future 1510
%'s].Percentage AS [Div %], [tbl_Op Leases Future Yard %'s].[Exp Percentage]
AS [Yard %], [Cap Cost]*[Percentage]*[Exp Percentage] AS [Base Amount], [Base
Amount]/[Lease Term] AS [Mthly Amount], IIf([Month]<[Last Pmt Date Long],[Cap
Cost]-[Mthly Amount],0) AS [Curr Cap Cost], NZ(DateAdd("m",+1,[Begin Date]))
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]
FROM ([tbl_Op Leases Future 1510 %'s] INNER JOIN [tbl_Op Leases Future Pmts]
ON [tbl_Op Leases Future 1510 %'s].[Lease #] = [Lease #]) INNER JOIN [tbl_Op
Leases Future Yard %'s] ON ([Lease #] = [tbl_Op Leases Future Yard
%'s].[Lease#]) AND ([Yard #] = [tbl_Op Leases Future Yard %'s].Yard)
GROUP BY Month, [Lease #], [Yard #], [Begin Date], [End Date], [Cap Cost],
[Vin/Serial], [Lease Term], [Asset #], [tbl_Op Leases Future 1510
%'s].Percentage, [tbl_Op Leases Future Yard %'s].[Exp Percentage]
HAVING ((([Begin Date])>0) AND (([Lease Term])>"0"));


Here's a copy of the 2nd query once ran:


Month Begin Date End Date Mthly Amount 1st Pmt Date Long Last Pmt Date
Long Total Paid Total Amount Left 1-2007 2-2007 3-2007
1/1/2007 9/8/2004 9/8/2009 1,436.90 10/1/2004 10/1/2009 $1,436.90 $84,777.10 $0.00 $0.00 $0.00
1/1/2007 9/29/2004 9/29/2009 902.05 10/1/2004 10/1/2009 $902.05 $80,282.95 $0.00 $0.00 $0.00
1/1/2007 9/29/2004 9/29/2009 451.03 10/1/2004 10/1/2009 $451.03 $80,733.97 $0.00 $0.00 $0.00
1/1/2007 10/13/2004 10/13/2009 1,560.25 11/1/2004 11/1/2009 $1,560.25 $92,054.75 $0.00 $0.00 $0.00
1/1/2007 10/25/2004 10/25/2009 1,856.80 11/1/2004 11/1/2009 $1,856.80 $109,551.20 $0.00 $0.00 $0.00
1/1/2007 11/17/2004 11/17/2009 1,361.49 12/1/2004 12/1/2009 $1,361.49 $243,731.51 $0.00 $0.00 $0.00
1/1/2007 11/17/2004 11/17/2009 1,361.49 12/1/2004 12/1/2009 $1,361.49 $243,731.51 $0.00 $0.00 $0.00
1/1/2007 11/17/2004 11/17/2009 454.24 12/1/2004 12/1/2009 $454.24 $244,638.76 $0.00 $0.00 $0.00
1/1/2007 11/17/2004 11/17/2009 907.66 12/1/2004 12/1/2009 $907.66 $244,185.34 $0.00 $0.00 $0.00
1/1/2007 12/20/2004 12/20/2009 1,410.31 1/1/2005 1/1/2010 $1,410.31 $167,826.69 $1,410.31 $0.00 $0.00
1/1/2007 12/20/2004 12/20/2009 1,410.31 1/1/2005 1/1/2010 $1,410.31 $167,826.69 $1,410.31 $0.00 $0.00


And here's the SQL of the 2nd query to ensure I added your formulas correctly:


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], IIf(DateSerial(2007,1,1) Between [1st Pmt Date Long] And [Last Pmt
Date Long],[Mthly Amount],0) AS [1-2007], IIf(DateSerial(2007,2,1) Between
[1st Pmt Date Long] And [Last Pmt Date Long],[Mthly Amount],0) AS [2-2007],
IIf(DateSerial(2007,3,1) Between [1st Pmt Date Long] And [Last Pmt Date
Long],[Mthly Amount],0) AS [3-2007]
FROM [qry_Op Leases Future Pmts 1st];


Vylent Fyre said:
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
Click to expand...
 
V

Vylent Fyre

I went back to the 1st query and changed the 1st Pmt Date and Last Pmt Dates
using the DateSerial function and now it all works perfectly!! =)

Thanks so much, Dale! Awesome!!


Dale Fye said:
Vylent,

And you want these values to show up in columns? This is a job for a
spreadsheet, not a database, although you can do it. Try changing the lines
that refer to the Months with the following:

[Jan 2007]:IIf(DateSerial(2007, 1, 1,) BETWEEN [1st Pmt Date Long]
AND [Last Pmt Date Long],[Mthly Amount],0)
[Feb 2007]:IIf(DateSerial(2007, 2, 1,) BETWEEN [1st Pmt Date Long]
AND [Last Pmt Date Long],[Mthly Amount],0)
[Mar 2007]: IIf(DateSerial(2007, 3, 1,) BETWEEN [1st Pmt Date Long]
AND [Last Pmt Date Long],[Mthly Amount],0)

HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



Vylent Fyre said:
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
Click to expand...
Click to expand...
 

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

Similar Threads

Iif, And Expressions in a Query (Working with Dates) 4
Filters within Queries 1
Crosstab Query based on Ordinal Values 6
Keep unique values 1
Query Help 5
Most recent date prior to test date. 1
WCG Stats Tuesday 01 August 2023 2
WCG Stats Sunday 01 October 2023 3

Top