Results 1 to 11 of 11
  1. #1
    2stew is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2017
    Posts
    13

    DLookup Using Text Fields

    I have a Table 1-Data containing "Location" & "Name" where location is a Text field containing numbers that all begin with a "0" (example: 0175, 0135, etc). I have a second Table 2-Properties that contains "Effective Date", "Location", "Name" & "Percentage". "Location" on Table 2 is also a Text field. My query is pulling in all fields from Table 1 and I want a DLookup to find the "Percentage" from Table 2. I've tried many times and cannot get it to work. I don't want to join the tables because later on there will be multiple records in Table 2 with the same "Location" but different effective dates that I will later have to use as part of the lookup. Currently I have no duplicates. I cannot get the code to work. The code I have been using follows:


    dlookup ("[Properties]![Percentage]","Properties", "[Data]![Location]=" & [Properties]![Location])

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The correct syntax is here http://access.mvps.org/access/general/gen0018.htm

    Code:
    dlookup("[Percentage]","Properties","Location = '" &  [Properties].[Location] & "'"
    But you should still use a join, and add the effective dates as part of your join.

  3. #3
    2stew is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2017
    Posts
    13
    Minty.
    I tried your coding and I get a pop-up window to enter a value for "Properties.Location". I went back in and put brackets around everything and it ran but pull in all blanks.
    Last edited by 2stew; 12-12-2017 at 01:52 PM. Reason: added more comments

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The value that is surrounded by quotes, in this case Properties.Location, must come from a variable. Either from something inside the subroutine or a field on the form that is currently open. Show us the entire line(s) of code that you are using.

  5. #5
    2stew is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2017
    Posts
    13
    This is the coding I was trying to use:
    dlookup("[Percentage]","Properties","Location = '" & [Properties].[Location] & "'")
    >Percentage is what I'm looking up within the Properties table and Location is the field name within the Data table that I'm using in the Query that it needs to match to. Hope that makes sense.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Where are you using this expression? In another query? Post that full SQL statement. Or in a textbox on form or report?

    If you need to pull value from Properties, makes no sense to reference Properties in the parameter. Parameter must be a field in query or textbox on form.

    SELECT Data.*, DLookup("[Percentage]", "Properties", "Location = '" & [Location] & "'") AS Pct FROM Data;

    Domain aggregates can perform slowly in large dataset.

    Could have an autonumber field in Properties and save the ID as foreign key in Data.

    Advise not to use exact same field name in multiple tables. Also, should not use spaces nor punctuation/special characters (underscore only exception) in names. And Name is a reserved word and really should not use reserved words as names for anything.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    2stew is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2017
    Posts
    13
    I have a table called Data that contains the following fields: Acctg Date, Location, Property
    There is a second table called Properties that contain the following fields: Effective Date, Location, Property, Percentage

    I created a query that pulls in the following from the Data table: Acctg Date, Location. I then created the field Expr1 that contains the Dlookup. The Dlookup is supposed to find the Percentage within the table Properties by matching the Location field within the Data table against the Location field within the Properties table. Per Minty's suggestion, I also created a join between the tables for Location.

    below is the sql coding:
    SELECT [Data].[Acctg Date], [Data].Location, DLookUp("[Percentage]","[Properties]","[Location] = ' " & [Properties].[Location] & " ' ") AS Expr1
    FROM [Data] LEFT JOIN Properties ON [Data].Location = Properties.Location
    WHERE ((([Data].[Acctg Date])=#11/1/2017#));

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Did you try suggestion in post 6? I may have edited it after you initially read.

    If you are going to join the tables there is no need for DLookup.

    I doubt joining on date fields will work unless AcctgDate has same values as EffectiveDate. Joining on date and location would be a compound key.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    2stew is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2017
    Posts
    13
    In the Data Table I changed Location to PropertyID.
    In the Properties Table I changed Location to LOCRC.
    Reviewed the fields and No Auto Numbering was found, removed all spaces and special characters.
    Right now the Effective Date is all the same but in the future the Percentages will be changing. I will be needing to look up the correct percentage based on the Effective Date & PropertyID/LOCRC. The joining of the table will work for now because all the effective dates are the same but I don't expect that to last very long. Was trying to get this right first & then tackle the dates afterwards.

    So now this is what the coding looks like:
    SELECT [Data].AcctgDate, [Data].PropertyID, DLookUp("[Percentage]","[Properties]","[PropertyID] = ' " & [Properties].[LOCRC] & " ' ") AS Expr1
    FROM [Data] LEFT JOIN Properties ON [Data].[PropertyID] = Properties.[LOCRC]
    WHERE ((([Data].AcctgDate)=#11/1/2017#));

    It will run but it still pulls in blank data.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    As stated in post 6 "If you need to pull value from Properties, makes no sense to reference Properties in the parameter. Parameter must be a field in query or textbox on form." Field references are switched.

    DLookUp("[Percentage]", "[Properties]", "[LOCRC] = '" & [PropertyID] & "'")

    And again, DLookup not needed if you can join the tables. If you use DLookup then don't bother with join.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    2stew is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2017
    Posts
    13
    It worked! Thank you so much!

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

Similar Threads

  1. Replies: 7
    Last Post: 11-06-2017, 02:45 AM
  2. Replies: 14
    Last Post: 05-30-2015, 09:22 PM
  3. Replies: 9
    Last Post: 12-30-2014, 11:58 AM
  4. Replies: 9
    Last Post: 04-18-2014, 08:51 PM
  5. DLookup with Text
    By Kirsti in forum Programming
    Replies: 8
    Last Post: 06-26-2012, 07:56 PM

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