Date comparisons in query

[DPCpresto via AccessMonster.com]

I have some linked table data from our server DB, that I need to run a few
times a month, where one of the pulled data columns gives me a column of data
(formatted as text) labeled "CC_Expiry" that is displayed as MM/YY. I created
a calculated column which gives a "Final Pay Date" (based on a date column
"StartDate" ( imported as mm/dd/yyyy) formatted as a DATE in dd/mm/yyyy. My
problem is to create an expression that gives me all customers with "dates"
in the "cc_expiry" column that are less than or equal to (<=) the "Final Pay
Date." My goal is to have alist of customers that have credit cards that
will expire before the final payment is due, so that I can send them notice
to update the credit card on file. I have tried various iterations of
DatePart, DateFormat, etc...but I just can't seem to get it to work and give
the results I need. It is a small dataset, so I can "eyeball" the overall
table, but getting it done by calculation would be sweet! Any help
appreciated.

 

[John Spencer]

CDate(CC_Expiry) will return the first day of the month as a date. So you
could use that with DateAdd to get the first day of the following month.
DateAdd("m",1,CDate(CC_Expiry))
Then as long as the Final pay date is before that calculated date, you should
get the desired result

If the following is True then the credit card can make the payment.
[FinalPayDate] < DateAdd("m",1,CDate(CC_Expiry))

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County