Using If IsNull and DLookup to populate table based on historic data

[SamDennis11]

Hi everyone,

First post alert - go easy on me!

I'm relatively new to the VBA universe, but I'm trying to improve my knowledge and have run into a roadblock whilst trying to update a forecasting tool/table using some code.

In short, I have historic forecast data from the last 3 financial years (Jul-Jun) and want to populate the next financial year based on last year's data. Some members of the wider team have already begun forecasting for those months, so I need to populate all cells that have not yet been updated using last year's number as a base, so as not to wipe out their work. The update will be large as we have thousands of customers and tens of thousands of products that need to be updated.

My initial idea is to use an If IsNull DLookup to identify the cells that need populating and then use last year's data if the result is 'Null'. However, I'm stuck on the last bit and getting an error with the 'Then' section. Not sure what I need to do next. Any help would be greatly appreciated!!!

Here is where I have got to:

Private Sub Update_F23_Click()
Dim strSQL As String

If IsNull(DLookup("Forecast", "qry_ForecastData", "MasterCustomerID = " & Me.MasterCustomerID & " AND MasterProductID = " & Me.MasterProductID & " AND Mth = #01/07/2022#")) Then
=(DLookup("Forecast", "qry_ForecastData", "MasterCustomerID = " & Me.MasterCustomerID & " AND MasterProductID = " & Me.MasterProductID & " AND Mth = #01/07/2021#")) - this is giving me error: 'Compile Error. Expected: line number or label or statement or end of statement

Thanks in advance,
Sam

 

[June7]

First of all, databases do not have cells, they have records and fields. Spreadsheets have cells.

Next, please post lengthy code between CODE tags for easier readability.

Last, need something to receive the result of DLookup.

If IsNull(DLookup("Forecast", "qry_ForecastData", "MasterCustomerID = " & Me.MasterCustomerID & " AND MasterProductID = " & Me.MasterProductID & " AND Mth = #01/07/2022#")) Then
Me!fieldname = DLookup("Forecast", "qry_ForecastData", "MasterCustomerID = " & Me.MasterCustomerID & " AND MasterProductID = " & Me.MasterProductID & " AND Mth = #01/07/2021#")
End If

 

[Minty]

You are thinking in a very row by row fashion, when you should really be looking at a set based solution.
You can easily determine the empty field(s) by selecting them in a query where they are null.

SELECT MyField FROM MyTable WHERE MyField Is Null

This would give the starting point to check the data that hasn't been updated yet.
You can then run an update query based on that data set.

How many fields are you looking at?
You may have to create a loop in VBA to do the updates based on a field by field basis, if you can't join the tables from last year very easily.

 

[NauticalGent]

First post alert - go easy on me!

No guarantees there, especially if you are going to use Excel terminology when working with Access! But, you WILL get first-rate assistance so it all works out.

Welcome to AWF!

 

[The_Doc_Man]

There are many ways to approach this, but I chose to break it up into two steps for clarity.

Query2 is a self-join query (i.e. table joined to / compared against itself) that builds a list of records having nulls in the current year's forecast field (C.Forecast) and allows you to find the previous year's forecast field (P.Forecast) based on matching the customer and product IDs and jugging dates. This method doesn't need to show you previous customer and product IDs since they will have to match the corresponding current IDs. Just to be clear to the point of pedantry, in this self-join query, "C" represents "Current" and "P" means "Previous" or "Prior."

Query2:

SELECT C.Forecast as CurrForecast,
    C.MasterCustomerID,
    C.MasterProductID,
    C.Mth,
    P.Forecast As PrevForecast,
    P.Mth
FROM qry_ForecastData As C
    INNER JOIN qry_ForecastData As P
ON C.MastercustomerID = P.MasterCustomerID
    AND C.MasterProductID = P.MasterProductID
    AND P.Mth = DateAdd( "yyyy", -1, C.Mth )
WHERE IsNull( C.ForeCast ) = TRUE
    AND DatePart( "yyyy", C.Mth ) = 2022 ;

Then when you have the list, it becomes trivial to update it. NOTE that Query2 has a WHERE clause that will guarantee ONLY a null for the current forecast amount. Therefore, since all the filtration and alignment has been managed in Query2, the only part left is to do the update. I left convenient field names in Query2 so you would have decent mnemonic symbolic names.

Update Query2 Set CurrForecast = PrevForecast ;

When you execute the Update query, it is a one-and-done operation. Next year, you change 2022 to 2023 and do it again. And using the constant for the current year was again for clarity, though it would be trivial to use DatePart( "yyyy", Now() ) to make it good for all time.

This is NOT the only way you could do this, but it shows the action in two steps. Your optimum answer will at least RESEMBLE this solution even if not exactly match it.

The best part of doing it this way is that you can match up the records in that SELECT query and VIEW them all to see how many you've got and yet not change anything. You can include other fields for the purpose of visual verification by just opening Query2. Finally, since we can't see qry_ForecastData, there is the chance that you could rewrite Query2 to directly use the table that is the source of your data rather than a query.

EDITED for improved clarity - The_Doc_Man