Results 1 to 8 of 8
  1. #1
    sportyaccordy is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    7

    Cannot get DLookUp to work with concatenated criteria

    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.

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Do you have any of this data in Access yet? If so, what does the data in your table look like?

    If you have that concatenated "factor" field in your data, what does it look like? Does it have the periods in it?

  3. #3
    sportyaccordy is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    7
    I got a solution from another forum that eliminates the need to concatenate completely. 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;
    I still don't quite understand the syntax though. Does Access use VBasic syntax or what? Where can I learn this stuff?

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    A very good book is Allen Taylor's SQL for Dummies.
    Don't let the title fool you, because is is comprehensive, has a very good index, and gets to the meat of the issues of creating queries.
    Highly recommended.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    http://www.mrexcel.com/forum/microso...p-problem.html

  6. #6
    sportyaccordy is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    7
    I did not realize the forums were linked. My apologies

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    They are not - and perhaps that is part of the issue. This means I work on your issue in one thread. Joe Blow works on it in another. Neither of us know this is happening so we are both trying to assist but blind to how it is developing elsewhere. Even worse, I spend time on it without knowing it is already solved elsewhere.
    Read up on any forum's rules on cross posting before you do so. It's not usually prohibited, but as a minimum courtesy, must be declared. Usually, if I discover a thread I've posted to is cross posted without it being declared, I am finished with it.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    For more info on SQL and vba see this material from Martin Green

    More -video tutorials playlist on Access and vba- Steve Bishop

    Good luck.

    Re:Cross Posting
    As micron said, the forums are not linked. But many people visit and participate in many forums. It is the fact that you have posed a question on multiple forums and did not advise the reader. Cross posting is OK--provided you let people (unpaid volunteers) know that you have done so.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-26-2015, 06:50 PM
  2. Can't get my Dlookup to work!
    By Maxelkat in forum Forms
    Replies: 3
    Last Post: 05-19-2011, 06:00 AM
  3. Would DLookup() work better here?
    By eww in forum Programming
    Replies: 2
    Last Post: 09-30-2010, 01:53 PM
  4. Replies: 2
    Last Post: 11-04-2009, 09:45 PM
  5. Replies: 1
    Last Post: 01-10-2006, 12:32 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums