none
Issues with the DLookup function RRS feed

  • Question

  • I am having issues with the DLookup function.

    What I want to perform is to use the DLookup function within a form's Design View.

    I may have noticed that I perform this function within the Control Source property in the Data Tab.

    =DLookUp([DDCSectionEntry],[DDCSections],[DDCSectionEntry]=[Forms]![DDCSections]![DDCSectionEntry])

    When I went to Form View, it generated a name error.

    Can anyone fix this problem?

    Regards,

    JohnDBCTX

    Monday, August 15, 2016 11:22 PM

Answers

  • DLookUp("[DDCSectionEntry]", "DDCSections", "[DDCSectionEntry]=[Forms]![DDCSections]![DDCSectionEntry]")

                            field                table or query               WHERE clause

    Lookups don't work if you use [] in the table field and the where clause is wrong. Don't use the "& and put quotes at the end. Like above. I also noticed you have a form and a table with the same name (DDCSections). That will cause all kinds of problems. Rename the form to DDCSectionsForm and change the where clause to "[DDCSectionEntry]=[Forms]![DDCSectionsForm]![DDCSectionEntry]"

    Tuesday, August 16, 2016 5:58 PM
  • Lawrence makes several good points.

    That said, "Lookups don't work if you use [] in the table field" is not true.  Brackets can be used within the table/query name without issue.  Try it out and you'll see it works fine.

    I also see no issue with using the & Forms... &  format.  It works fine.  There is also nothing wrong with the format Lawrence suggests (encapsulating everything within the same string) though.  Both work equally. 


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Tuesday, August 16, 2016 6:15 PM

All replies

  • Try:

    =DLookUp("[DDCSectionEntry]","DDCSections","[DDCSectionEntry]=[Forms]![DDCSections]![DDCSectionEntry]")

    Tuesday, August 16, 2016 12:52 AM
  • Try one of the following

    If DDCSectionEntry is a numeric data field

    =DLookUp("[DDCSectionEntry]", "[DDCSections]", "[DDCSectionEntry]=" & [Forms]![DDCSections]![DDCSectionEntry])

    If DDCSectionEntry is a text data field

    =DLookUp("[DDCSectionEntry]", "[DDCSections]", "[DDCSectionEntry]='" & [Forms]![DDCSections]![DDCSectionEntry] & "'")


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, August 16, 2016 1:57 PM
  • I just noticed that you are trying to lookup the value of DDCSectionEntry, yet you are using it to filter the result of the Dlookup which doesn't make sense?

    Could you explain the value(s) you have vs what you are trying to lookup (what table, what field name).


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Tuesday, August 16, 2016 2:00 PM
  • I have added a text box and a command button that I need to perform the following:

    Private Sub cmdFilter_Click()

    txtDDCSectionEntryFilter.Value=DLookUp("[DDCSectionEntry]", "[DDCSections]", "[DDCSectionEntry]=" & [Forms]![DDCSections]![DDCSectionEntry])

    End Sub

    When I clicked on the command button, its text box value has reset to 0.

    Can anyone help me improve this source code snippet?

    Regards,

    JohnDBCTX


    • Edited by JohnDBCTX Tuesday, August 16, 2016 3:29 PM claification
    Tuesday, August 16, 2016 3:28 PM
  • John.

    Both Lawrence and I have tried to help you.  Have you tried either of our suggestions?  What was the result?

    You also need to respond to my question regarding: "I just noticed that you are trying to lookup the value of DDCSectionEntry, yet you are using it to filter the result of the Dlookup which doesn't make sense?

    Could you explain the value(s) you have vs what you are trying to lookup (what table, what field name).".  It is impossible to perform a DLookUp to retrieve a field value while that same field to restrict the DLookUp.  You need to explain what you are after in better detail and someone here will be glad to help you piece the proper code together, but you need to provide us with the bigger picture so we can understand exactly what you have vs what you wish to retrieve.


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, August 16, 2016 5:52 PM
  • DLookUp("[DDCSectionEntry]", "DDCSections", "[DDCSectionEntry]=[Forms]![DDCSections]![DDCSectionEntry]")

                            field                table or query               WHERE clause

    Lookups don't work if you use [] in the table field and the where clause is wrong. Don't use the "& and put quotes at the end. Like above. I also noticed you have a form and a table with the same name (DDCSections). That will cause all kinds of problems. Rename the form to DDCSectionsForm and change the where clause to "[DDCSectionEntry]=[Forms]![DDCSectionsForm]![DDCSectionEntry]"

    Tuesday, August 16, 2016 5:58 PM
  • Lawrence makes several good points.

    That said, "Lookups don't work if you use [] in the table field" is not true.  Brackets can be used within the table/query name without issue.  Try it out and you'll see it works fine.

    I also see no issue with using the & Forms... &  format.  It works fine.  There is also nothing wrong with the format Lawrence suggests (encapsulating everything within the same string) though.  Both work equally. 


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Tuesday, August 16, 2016 6:15 PM