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

    Dlookup Problem

    Hi all,



    I am trying to get the following Dlookup to work and its not


    =DLookUp([qry123]![Total],[qry123],"Week =" & [qry123].[Week] And "HomeTeam=" & [qry123].[Team])



    Has anyone got any ideas please


    Many thanks in advance



    Steve

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Sometimes you will get a better understanding if you google what you are trying to do first - e.g. dlookup - before asking a question.

    there are a number of things wrong with the syntax and not quite clear how you are using it, so better you learn how dlookup works, so take a look at this link.

    https://support.office.com/en-us/art...ad=US&fromAR=1

    in particular, look at the last example which sounds like it is close to what you are trying to do

    If still a problem, come back with your revised code.

  3. #3
    pinkpanther6666 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    11
    Ajax

    Many thanks for your help and i have read the page and changed my code to the following:-

    =DLookUp("[Total]","qry123","[Week] =" & [Forms]![frmFixtures]![Week] And "[Team] =" & [Forms]![frmFixtures]![HomeTeam])

    and it works sort of as every record in the form has the same value and it definatley shouldnt

    any help


    Many thanks again in advance

    Steve

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Find a way (either with Debug.Print or a MsgBox) to display: "[Week] =" & [Forms]![frmFixtures]![Week] And "[Team] =" & [Forms]![frmFixtures]![HomeTeam]

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It may be because your quotes aren't quite right

    =DLookUp("[Total]","qry123","[Week] =" & [Forms]![frmFixtures]![Week] & " And [Team] =" & [Forms]![frmFixtures]![HomeTeam])

    Also, if this code is in the frmFixtures form then all you need is

    =DLookUp("[Total]","qry123","[Week] =" & [Week] & " And [Team] =" & [HomeTeam])

    Next, just to confirm, Team is a number and not text?

    finally, please clarify

    it works sort of as every record in the form has the same value and it definatley shouldn't
    Is your form single or continuous? is it a subform? - please provide some example data and/or screenshot to illustrate the problem

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Team/HomeTeam certainly sounds more like a Text Field than a Number...and even Week could be Numerical or Text. We really need to know the Datatypes of each, in order to advise you.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    pinkpanther6666 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    11
    Ajax and all,

    Many thanks for your reply, and sorry for the delay posting very busy with work and home life
    I have tried the following and it works

    =DLookUp("[Total]","qry123","[Week] =" & [Forms]![frmFixtures]![Week] & " And [Team] =" & [Forms]![frmFixtures]![HomeTeam])

    and yes Team is a Number

    Many many thanks for your help

    Steve

  8. #8
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    'work and home life' has a way of doing that!


    Glad you got it working!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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

Similar Threads

  1. Problem with DlookUp
    By zylosan in forum Programming
    Replies: 10
    Last Post: 10-18-2013, 07:46 AM
  2. DLookUP problem
    By Symlink in forum Access
    Replies: 7
    Last Post: 03-28-2013, 11:03 AM
  3. VBA DlookUp problem
    By luli in forum Programming
    Replies: 1
    Last Post: 01-06-2013, 09:32 PM
  4. Problem with DLookup
    By MichealShinn in forum Programming
    Replies: 5
    Last Post: 01-25-2012, 09:08 AM
  5. DLookup problem
    By the_rock in forum Programming
    Replies: 3
    Last Post: 01-11-2012, 02:36 AM

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