Date Logic... (1 Viewer)

[MSherfey]

I hope this isn't a duplicate request but I couldn't find anything in my searches.

I need to show when a customer had maintenance on a product spanning numerous years. They may have dropped maintenance and then restarted later.

I have a [Maint Start:] and [Maint End:] field in the table which helps. I'm looking to show a field for year-Q1 through year-Q4 and a yes/no in the data depending on if they are a current customer for that time.

Customer ID FY05-Q1 FY05-Q2 FY05-Q3 FY05-Q4.....FY09-Q4
123456 Yes Yes No Yes No

I am hitting a logic snag here. I can figure out how many days they have maintenance (DateDiff) but comparing that to the different quarter dates is confusing me.

Once I get past the problem of figuring out if they are covered for the various quarters on a specific purchase, I then have to figure out how to show only one result for all their purchase. For example, if they make 5 purchases, one for each year, how do I get this to show one one line and not 5 separate lines or worse yet, 20 separate lines (years * quarters).

Any help in the right direction would be great. Here's what I have so far, but it doesn't seem to be working.

FY05-Q1: IIf(([Invoice Data]![Maint Start:]+DateDiff("d",[Invoice Data]![Maint Start:],[Invoice Data]![Maint End:])) Between 11/1/2004 And 1/31/5,"Yes","No")

 

[Fifty2One]

I am confused by what you want to do but here are some good date calculation helps...
http://www.databasedev.co.uk/ms_access_date_functions.html

 

[MSherfey]

Thanks for the link.

What I'm having trouble with is finding if a date range is within another date range.

For example: The customer has maintenance from Jan 1, 2008 through December 31, 2008. Comparing that to each quarter in the year and confirming they have maintenance. Does their current coverage fit within Q1? Does their current coverage fit within Q2? etc... This of course would go back a few years as well as have very different dates which may not fit so easily.

I can easily find their coverage range with the Maint_Start and Maint_End fields, but how to see if that falls within our quarters is confusing. (We don't use calendar quarters of course)

Does that make more sense?