By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,831 Members | 1,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,831 IT Pros & Developers. It's quick & easy.

Looping through records to compare multiple dates

P: 1
Access 2013. Looking for VBA code to cycle through sales orders and determine the most likely sales lead to associate the sale with (the most recent lead that is earlier than the sale). The common data is [ClientCode], but there could be any number of lead records and any number of sales records.

LeadtoJobLink1 - table showing the leads by date and id:
ClientCode LeadDate LeadMasterID
122804 6/23/2010 66930
122804 1/8/2019 270820

LeadtoJobLink2 - table showing the sales by sales date and order number ([SONo):
ClientCode SONo SaleDate
122804 123364 8/25/2011
122804 136290 1/14/2013
122804 234850 1/31/2019
122804 235891 3/8/2019

Desired Result - table with SONo and the LeadMasterID that is most likely the lead for that sale:

SONo LeadMasterID
123364 66930 (because sale is after first lead, but before next lead)
136290 66930 (again, this sale date is after first lead, but predates next lead)
234850 270820 (sale occurs after 2nd lead date and before any additional)
235891 270820 (sale occurs after 2nd lead date and before any additional)

Again, I need to have this cycle to .eof for both sales and leads as they could occur with any frequency between 1 and 40 possible events.

I am struggling getting my head around this one. Thanks.
1 Week Ago #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,302
You shouldn't be looking to loop through records. You should be looking to prepare some SQL code to link the tables together and to select the matching record based on a matching [ClientCode] and the maximum [LeadDate] from [LeadtoJobLink1] which is less than or equal to the [SaleDate] of [LeadtoJobLink2].

Does that make sense?

You really need to start the work yourself. Hopefully I've pointed you in the right direction to get you going.
1 Week Ago #2

Post your reply

Sign in to post your reply or Sign up for a free account.