Results 1 to 7 of 7
  1. #1
    Vbinet1 is offline Novice
    Windows 8 Access 2016
    Join Date
    Sep 2017
    Posts
    4

    variable filled dlookup query


    Hi guys.

    Having trouble with a messy dlookup giving me an unexpected result

    (DLookup("'" & [f] & "'", "RGLN", "[DAYI]=" & "'" & [a] & "'"))

    Where f and a are variables

    I should be getting a null value based on what is in the table but the result it is returning is equal to the value of variable f rather than looking in the field named by f to tell me what is there for record a. I'm sure i've got the syntax wrong for what I'm trying to achieve and am hoping one of you experts can point out the missing/extra whatever it needs...

    Thanks in advance

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Maybe
    Dlookup("[f]","RGLN","[DAYI] = '" & [a] & "'")

    This assumes that f and DAYI are fields in RGLN. If a is also in RGLN, the lookup makes no sense.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Where are you using this expression? Is it in VBA? What are f and a? If they are VBA variables then both would not be within quote marks. No brackets around VBA variables. Brackets delimit name of Access object (field, table, query, control, form, report). What data type is DAYI?

    Dlookup("[" & f & "]", "RGLN", "[DAYI] = '" & a & "'")


    Why would the field be variable? Do you have multiple similar fields saving same kind of data? That indicates a non-normalized data structure.
    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.

  4. #4
    Vbinet1 is offline Novice
    Windows 8 Access 2016
    Join Date
    Sep 2017
    Posts
    4
    Thanks June. I'll give that a swing.

    Both f and a are string variables in vba.

    And the purpose of the sub is to build a visual matrix based on a calendar schedule, using an access table as the back end. So the f variable represents the time slot while the a variable represents the day. The dlookup in question is checking that there are no entries already in the time slot before a new entry is recorded.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    So table has a field for each time slot?
    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.

  6. #6
    Vbinet1 is offline Novice
    Windows 8 Access 2016
    Join Date
    Sep 2017
    Posts
    4
    Yes.

    I know it's a little odd, but I'm trying to give similar functionality to the existing app which was built in excel, and the user is quite reliant on having the visual representation of the schedule.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You might eventually encounter a requirement for the normalized structure and that would mean a UNION query to rearrange the data.

    It is a balancing act between normalization and ease of data entry/output. "Normalize till it hurts, denormalize till it works."
    Last edited by June7; 09-16-2017 at 08:13 PM.
    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.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-09-2013, 02:37 AM
  2. Newbie. How do I use DLookup with a variable please?
    By graffiti in forum Programming
    Replies: 2
    Last Post: 12-12-2011, 08:59 AM
  3. Use a variable in a DLOOKUP?
    By leftylee in forum Forms
    Replies: 9
    Last Post: 07-27-2011, 02:00 PM
  4. Dlookup in query using Global variable
    By newwales in forum Access
    Replies: 1
    Last Post: 06-03-2011, 03:47 PM
  5. Help Using Variable in DLookup Statement
    By bcmarshall in forum Access
    Replies: 9
    Last Post: 12-02-2010, 12:44 AM

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