Access VBA Variable = Maximum Access table value

Introduction

Unlike most of my posts to date, this has to do with some VBA code in an Access standard module.

Project Description

This Access project has an Account table and every month we run a set of queries that links Excel worksheets, loads the data into the MonthlyBilling table, then updates the LastBillingYearMonth ("YYYYMM") field for the Account and Customer.    A Customer may have more than one account.

The end-point is to update another Excel Workbook, using embedded queries, to show sales and billing trends for each customer, over-all trends, latest month sales for largest 10 customers, and a 12 month history of the largest customer.

Situation-to-date

Original Operations mode

Since this was built as a "working prototype" and most of my Access skills were around tables and queries, the process had low-levels of automation beyond that/  Each month I would...

• Receive the input files and save them to a specified directory
• Link using the Access UI.  Linking was used instead of importing because the data was then loaded into a single table, each row stamped with the billing year and month.
• Resolve issues in the linking process, i.e. the providers decided on their own that they wanted to put an apostrophe in the Worksheet names, which caused an error when trying to link.
• Edit, with new month/year references, and run queries to add the input files to the MonthlyBilling table, 
• Edit, with new month/year references, and run a query to add an Account if needed, i.e. the billing input showed an account number not previously billed and add a new Customer for the Account
• Manually review the new accounts (they aren't linked to Customer records at this time, linking each to either the new Customer or an existing Customer.
• Run a query to delete any Customers that had no Accounts
• Run queries to update LastBillYearMonth in Account, then Customer, tables.
• Open the "live" file from the previous month,  (The "live" file has formulas and links/queries.  There is also a "published" version with no formulas or links/queries.
• Refresh the queries.
• SaveAs with new date stamp, still marked as the "live" version, 
• Copy-PasteValues for all worksheets.
• Disconnect data links.
• SaveAs, marked for publication.
• Close and distribute.

Improved Operations mode

To help with the front end of the process I wrote some code in a Excel "toolkit" that cleaned-up the worksheet names.

In the Access project file (the "live" file mentioned above), I translated the linking process and queries used above to DoCmd.TransferSpreadsheet and SQL statements, respectively.  Using constants I would change once, I did the editing mentioned above, then ran the DoCmd/SQL.

Change to make

Editing the constants was a task I wanted to eliminate, so I needed to create a way to take the LastBillYearMonth and increment it by a month.  

Early Paths

I tried a few things that I abandoned.

I tried to creating a userform that would then allow the user (me) to approve/abort based on what the application saw as the next month to run.

I tried to use the SQL that returned the value I needed.

I  did a Google search on "access vba run query return".  What I found was more than one person tried my method and the recommendation that one use "DLookup()" for this purpose. 

All of the examples showed the third argument as being <field> = <variable> so I thought I might try

 DLookup("LastYearMonthBilled", "Account", "LastYearMonthBilled"= DMax("LastYearMonthBilled", "Account"))

Kablooie!  Errors!  (No, I don't remember which errors.  I think the last had to do with invalid use of Null)

Solution

Using the Watch window, I realized the DMax() statement gave me what I needed.  The rest was easy!

Explanation

For explanatory purposes I am going to break this down into variables (Result1, Result2 etc) assigned to each step of the process

1. Grab the max of LastYearMonthBilledResult1 = DMax("LastYearMonthBilled", "Account")
2. Convert the yyyymm returned by DMax() into a real dateResult2 = Right(Result1 , 2) _                & "/1/" _                & LeftResult1 , 4)
3. Add 1 monthResult3 = DateAdd("m",1,Result2) 
4. Extract the year and month Result4A = Year(Result3)Result4B = Month(Result3)

Code

The result of the above is three functions

• fn_LastYearMonthBilled 
• Returns the max of LastYearMonthBilled
• Made stand-alone function because it is a piece of code already shown to have two uses and may have more.
• fn_NextMonthToProcess and fn_NextYearToProcess
• Both invoke fn_LastYearMonthBilled 
• Are identical expect the former uses the Month() function and the latter, Year().
• Use variable LastYearMonthBilled so that function fn_LastYearMonthBilled is only invoked once.

Function fn_NextYearToProcess() As Integer

    Dim LastYearMonthBilled As Double

    LastYearMonthBilled = fn_LastYearMonthBilled

    fn_NextYearToProcess = Year(DateAdd("m", 1, Right(LastYearMonthBilled, 2) _

                                                      & "/1/" _

                                                      & Left(LastYearMonthBilled, 4)))

End Function

Function fn_NextMonthToProcess() As Integer

    Dim LastYearMonthBilled As Double

    LastYearMonthBilled = fn_LastYearMonthBilled

    fn_NextMonthToProcess = Month(DateAdd("m", 1, Right(LastYearMonthBilled, 2) _

                                                      & "/1/" _

                                                      & Left(LastYearMonthBilled, 4)))

End Function

Function fn_LastYearMonthBilled() As Double

    fn_LastYearMonthBilled = DMax("LastYearMonthBilled", "Account")

End Function