Cliff notes: I am trying to do a DLookUp with the following criterion: FieldX = FieldA & FieldB & FieldC & (FieldD-1), and no matter what I do I either get #Error or blanks even when I know there should be a result. I have successfully done this in Excel with VLookup & Index/Match but I need to move the platform over to Access.
Full story/background:
Here is the basic gist of what I am trying to do: I have monthly utility usage data for 1100 facilities, and I want to create a calculated field either in a table or a query that has the monthly consumption from a year prior. I want it to work so that each month I can just paste append the latest data and have everything auto-populate/calculate. I have this built in Excel and the basic gist of the sheet looks like this:
Store Service M Y Factor UsageCurr UsagePrev 927 Electric 1 2016 927.Electric.1.2016 99,016.380 110,664.827 1451 Electric 1 2016 1451.Electric.1.2016 89,037.421 89,726.454 1152 Electric 1 2016 1152.Electric.1.2016 116,550.295 114,177.016 337 Electric 1 2016 337.Electric.1.2016 151,148.733 146,284.852 547 Electric 1 2016 547.Electric.1.2016 114,710.405 120,170.768 343 Electric 1 2016 343.Electric.1.2016 121,358.067 113,979.354 2588 Electric 1 2016 2588.Electric.1.2016 105,565.336 111,689.966
So what I do is concatenate the relevant parameters (facility, utility service type, month and year) so I can have 1 field to lookup against; then to get last year's usage I check if the usage last year is even available, and then just match/index the factor/usage to get it... the formula in "UsagePrev" looks like this:
=IF(ISNA(INDEX([Usage],MATCH(CONCATENATE([@Store],"."[@Service],".",[@M],".",[@Y]-1),[Factor],0))),0,INDEX([Usage],MATCH(CONCATENATE([@Store],"."[@Service],".",[@M],".",[@Y]-1),[Factor],0)))
So to get the previous year's electric usage at facility 343 in January 2016, Excel would return the usage corresponding to the factor 343.Electric.1.2015. I can remove the dots if need be; I am trying to make this as simple as possible.
I pulled the Store through UsageCurr columns (creating that same concatenated Factor field) from Excel to Access and tried a wide range of things to get Access to calculate the UsagePrev.... DLookUp("Usage","Energy","Factor =" & [StoreNo] & [Service] & [M] & [Y]-1), creating a "PrevFactor" with the previous year and making the criteria for Factor to equal that, etc.... I keep getting nothing but errors for the resulting column. Is it a syntax error, am I asking too much of Access, not setting up DLookUp right or what? This is the only hangup keeping me from making the full migration to Access for this report.