I am currently wanting to know how to find out two different dates from a list of dates for each customer. I can find out the first date for each customer but I need to know their 6 month and 1 year visit.

But the problem is not all the customer will have visited exactly 6 months or a year afterwards. I just need to know how to figure out the closest date to 6 months and a year.

The customers details are currently stored in a table, each having their own unique ID and the visits are stored in a table each with a unique ID.

Any help would be great :)

Try the following select statement -

rsVisit.Open "SELECT * FROM MyTable WHERE DateVisited >= DateValue('" & txtDate.Text & "')", cnReport, adOpenStatic, adLockOptimistic 'Adjust the txtdate as you wish to say 6 months or 12 months etc.

But what if they had a visit 5 months after their first visit, how would i look for the nearest date to the 6 month mark?

Thanks

Use a if then statement with the select statement -

Dim TodaysDate As Date, DateVisited As Date

TodaysDate = Date 'Will display the today date.
DateVisited = Date - 30 'Deduct one month from today.

'The select statement will now look for visits that is bigger or equal to 5 months and forward. Again, play around with the dates to get exactly what you need.

rsVisit.Open "SELECT * FROM MyTable WHERE DateVisited >= DateValue('" & DateVisited & "')", cnReport, adOpenStatic, adLockOptimistic 'Adjust the txtdate as you wish to say 6 months or 12 months etc.
This article has been dead for over six months. Start a new discussion instead.