DLookUp problem

[sportyaccordy]

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 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.

 

[xenou]

In a database you must put literal text in quotes. So if Service is test it must be in single quotes:

... & "'" & [Service] And &"'" & ...

Also your "factor" has dots in it so if you want to match you must put those in.

This could also be done in a query or subquery, with the same criteria as in this DLookup example. We don't need to concatenate. You *do* need to be careful about how you use DataTypes in your syntax:

DLookUp("Usage","Energy",
	"[StoreNo] = " & [StoreNo] & " AND "
	"[Service] = " & "'" & [Service] & "'" & " AND " &
	"[M] = " & [M] &  " AND " &
	"[Y] = " & [Y]-1
)

 

[sportyaccordy]

I tried your DLookUp and it came back with the following error:

"Syntax error (missing operator) in query expression"... when I click "OK" it selects the first [Service]

I'd imagine I'd have the same issue with a subquery....

 

[xenou]

Hi,
Post your query and we can look at it.

Note that if other fields besides service are text they are also subject to the same syntax rules (must be enclosed in quotes).

 

[sportyaccordy]

StoreNo was text rather than a number so I changed that.... still having the same issue though

Here is the query:

SELECT Energy.Factor, Energy.Usage, Energy.KBTUs, DLookUp("Usage","Energy"," [StoreNo] =" & [StoreNo] & " " AND " " [Service] =" " & "'" & [Service] "'" & " "AND" "[M] = " & [M] & " AND " & "[Y] =" & [Y] -1) AS UsagePrev
FROM Energy;

*EDIT* It worked! Thank you! There was a missing &, here is the functioning query:

SELECT Energy.Factor, Energy.Usage, Energy.KBTUs, DLookUp("Usage","Energy"," [StoreNo] =" & [StoreNo] & " AND " & "[Service] =" & "'" & [Service]  & "'" & "AND" & "[M] = " & [M] & " AND " & "[Y] =" & [Y] -1) AS UsagePrev
FROM Energy;

Is there somewhere I can reference criteria syntax rules for stuff like this in the future?

 

[xenou]

Just for the record here's how you write this as a query with joins:

SELECT 
	t1.Factor, 
	t1.Usage, 
	t1.KBTUs, 
	t2.Usage as UsagePrev
FROM 
	Energy t1
	INNER JOIN Energy t2
	ON 
		t1.StoreNo = t2.StoreNo
		AND t1.Service = t2.Service
		AND t1.M = t2.M
		and (t1.Y - 1) = t2.Y

I don't have any specific tips on syntax. This is just one of those things you have to know - it's part of SQL, more than Access. There is a special rule for dates too, so check that out as well:
MS Access: DLookup Function
(in this case, it's part of Access, more than SQL - just to keep you confused. Most other DB engines would use single quotes for dates, not these hashes).

 

[sportyaccordy]

Just for the record here's how you write this as a query with joins:

SELECT 
	t1.Factor, 
	t1.Usage, 
	t1.KBTUs, 
	t2.Usage as UsagePrev
FROM 
	Energy t1
	INNER JOIN Energy t2
	ON 
		t1.StoreNo = t2.StoreNo
		AND t1.Service = t2.Service
		AND t1.M = t2.M
		and (t1.Y - 1) = t2.Y

I don't have any specific tips on syntax. This is just one of those things you have to know - it's part of SQL, more than Access. There is a special rule for dates too, so check that out as well:
MS Access: DLookup Function
(in this case, it's part of Access, more than SQL - just to keep you confused. Most other DB engines would use single quotes for dates, not these hashes).

Thanks, I will keep that in mind. Subqueries will definitely come in handy for more complex stuff. Would a subquery have better peformance? I need to add about 3-4 columns like this and performance is abysmal with just one. I'd imagine it would help. What would a final complete query with StoreNo/Service/M/Y/Usage/UsagePrev look like using the subquery method?

As far as the syntax...DLookUp is Access/VBasic and not SQL though right? I thought SQL didn't have a lookup function and just worked off of queries/subqueries.

 

[sportyaccordy]

I couldn't get your query to work, so I made another query with the same fields as Energy just with Y=Y+1 so that in a join between it and Energy everything from it will be shifted back a year and called it "Prev". My question now is if I make another query pulling from the "Energy" table and the "Prev" query, will I have to always run "Prev" first for the union query to have everything up to date, or would running that union query also run any query it's linked to?

 

[xenou]

Hi, everything will be up to date when you run the union query (or any query that calls another query). I'm not sure how DLookup stacks up performance-wise. It should work more or less like a subquery but there may be ways of doing it faster if you have a lot of calls to DLookup at runtime.

 

[Micron]

I'm sure there are rules you can read up on here regarding cross posting. It is inconsiderate to get people to work on an issue that may already be resolved or is in progress elsewhere without declaring your other post.
Cannot get DLookUp to work with concatenated criteria