Relating VBA Function Output to Another Table in MS Access 2003

It is a functional limitation of MS Access. You cannot relate the output of a VBA function, displayed in a query, to data held in another table.

Hence, this presents a unique problem. In my case, I should just be using a SQL Server, but the project is going to be handed over to everyday users and the budget for it does not afford a customized front end.

I was trying to take a variety of dates held in field of records in Table 1, use a VBA function to round up the date to the 1st of the next month, and relate those dates to a date field in Table 2. Table 2 contained other data that I needed related to the transactions in Table 1.

Since I can't forge that relationship, I ended up having to make this MORE complex on a SIMPLER program to accomplish.

I developed a MS Excel spreadsheet, put in 30 years of dates (= A1+1 method), and then used an Excel function to round the dates up. I then imported this into MS Access as Table 3. The individual dates in Table 3 related to the dates in Table 1. The rounded up dates in Table 3 related to the 1st of the month only dates in Table 2.

There you have it - silly, but it works.

If you want a copy of the file, email me.