locked
Dlookup in Query RRS feed

  • Question

  • Here is my scenario.  I have a report that pops open based on a Button_Click event.  I’m passing a few criteria to a SLQ query, which is controlled by some VBA code.  Everyhting works fine, except to the ability to display a field named ‘CompanyName’ from a table named ‘Customers’.  I have From (date) and To (date); these work fine.  I also have a ListBox, named lstCustomer.  This is the value that I want to pass to the query.

     

    I’m not good with Dlookups, but I did take a stab at it.  I put this in the ControlSource:

    =DLookUp("CompanyName","Customers","Criteria=[Forms]![ReportForm]![lstCust]'")

     

    Of course, that didn’t work.  What am I doing wrong?

     

    I can post the VBA, if necessary.  I don't think it is necessary; just adds to the confusion, I think.

    Wednesday, January 25, 2012 4:01 AM

Answers

  • >> These actually work:

    =[Forms]![ReportForm]![lstCust].[ItemData](0) <<

    Did you think they wouldn't ... LOL!!!

    ----

    Is your list box, by chance, marked as a Multi Select list box? If so, and you only want the user to have only one choice, then verify that the "Multi Select" property of the List Box control is set to 'None'.

    With the Multi Select property set to None, the use of the default property (Value) should indeed return the item that is selected....

    =[Forms]![ReportForm]![lstCust]


    Brent Spaulding | Access MVP
    • Edited by datAdrenalineMVP Friday, January 27, 2012 5:11 PM
    • Marked as answer by ryguy72 Friday, January 27, 2012 5:41 PM
    Friday, January 27, 2012 5:08 PM

All replies

  • Which field in the Customers table corresponds to lstCust? If it is, for example, a field named CustomerID, you can use

    =DLookUp("CompanyName","Customers","[CustomerID]=[Forms]![ReportForm]![lstCust]")
    

    Note that there is no single quote after lstCust.

    On the other hand, if the value if lstCust *is* the customer name itself, you don't need DLookup at all. Simply use

    =[Forms]![ReportForm]![lstCust]
    


    Regards, Hans Vogelaar
    Wednesday, January 25, 2012 7:11 AM
  • =DLookUp("CompanyName","Customers","Criteria=[Forms]![ReportForm]![lstCust]'")

     

    Hi ryguy72,

    In order to use the VALUE of [Forms]!ReportForm]![lstCust], you have to place it outside the doublequotes, as in

        =DLookUp("CompanyName","Customers","Criteria=" & [Forms]![ReportForm]![lstCust])

    supposed that lstCust is a numeric value.
    In case lstCust is of the string type, then you have to surround it with two doublequotes.

     


    Imb.

    Wednesday, January 25, 2012 3:11 PM
  • =DLookUp("CompanyName","Customers","Criteria=[Forms]![ReportForm]![lstCust]'")

     

    Hi ryguy72,

    In order to use the VALUE of [Forms]!ReportForm]![lstCust], you have to place it outside the doublequotes, as in

        =DLookUp("CompanyName","Customers","Criteria=" & [Forms]![ReportForm]![lstCust])

    supposed that lstCust is a numeric value.
    In case lstCust is of the string type, then you have to surround it with two doublequotes.

     


    Imb.


    And just to clarify Imb's post, Criteria= means that you put the FIELD NAME that you have in that table which you are looking for.  You do NOT use the actual word Criteria unless your field name is actually named Criteria.

     

    (sorry, I just thought that needed to be pointed out as it could be confusing to some)


    Bob Larson, Access MVP 2008-2010, 2011
    Wednesday, January 25, 2012 8:41 PM
  •  

    (sorry, I just thought that needed to be pointed out as it could be confusing to some)


    Bob Larson, Access MVP 2008-2010, 2011


    Thank you, Bob, for your punctuality.

     

    Imb.

    Wednesday, January 25, 2012 9:50 PM
  • I tried all combinations, nothing works.  This is where I am now:

    =DLookUp("Customer","Trades","CUST = [Forms]![ReportForm]![lstCust]")

    Customer = Field name that I want to display ion the TextBox

    Trades = Table name

    CUST = Field name in the Trades Table that is displayed in the lstCust (ListBox)

    ReportForm = Form name that has a couple ComboBoxes and a couple Buttons.

     

    I even switched the CUST and Customer; that did nothing.  Nothing is displayed in the TextBox with the Dlookup.

    Wednesday, January 25, 2012 10:15 PM
  • If CUST is numeric:

    =DLookUp("Customer","Trades","CUST=" & [Forms]![ReportForm]![lstCust])

    If CUST is text:

    =DLookUp("Customer","Trades","CUST=" & Chr(34) & [Forms]![ReportForm]![lstCust] & Chr(34))

     

    Also, if CUST isn't unique to a particular customer, you would need to use a field that is unique instead.


    Bob Larson, Access MVP 2008-2010, 2011
    • Edited by accessbob-pdx Wednesday, January 25, 2012 10:31 PM add extra info
    Wednesday, January 25, 2012 10:29 PM
  • Oh, and to go with my last post, you need to make sure that your list box is returning the value of CUST and not some ID number instead.   Most of the time a list box will show the value but the ID is the hidden field to which a list box is bound.
    Bob Larson, Access MVP 2008-2010, 2011
    Wednesday, January 25, 2012 10:32 PM
  • What is the Column Count of lstCust, what is its Row Source and what is its Bound Column?


    Regards, Hans Vogelaar
    Wednesday, January 25, 2012 10:38 PM
  • Cust is NOT unique; nothing in the table is unique and I don't see how there will ever be any totally unique IDs in this table.  So, I'm going back to the simple concept of passing the value to the TextBox straight from the Form:

    =[Forms]![ReportForm]![lstCust]

     

    So, now, lstCust has the 'Customer', and not the 'CUST' (I know it's a bit confusing).

     

    I still get nothing in the TextBox when the code runs and the Report pops open.  The between-dates is correct, but the 'Customer' is still completely blank.  Argh!!!

    Wednesday, January 25, 2012 11:17 PM
  • Where do you have this text box?  Is it in the Report header?
    Bob Larson, Access MVP 2008-2010, 2011
    Thursday, January 26, 2012 12:10 AM
  • yes!! it IS in the report header!!!
    Thursday, January 26, 2012 5:15 PM
  • Are you sure you set the control's CONTROL SOURCE to the reference and not the DEFAULT VALUE property?  I've accidentally made that mistake before.
    Bob Larson, Access MVP 2008-2010, 2011
    Thursday, January 26, 2012 9:07 PM
  • The Control Source Property for the ListBox is BLANK (i.e., nothing)

    The ListBox is populated this way:

    Name = 'lstCust'

    Row Source

    SELECT DISTINCT Trades.Customer
    FROM Trades
    WHERE (((Trades.Customer)<>""));

     

    The Control Source Property for the 'CustomerName' TextBox is:

    =[Forms]![ReportForm]![lstCust]

     


    The Control Source Property for the 'InvoiceDates' TextBox that shows dates is:

    =[Forms]![ReportForm]![cboFrom].[Value] & " to " & [Forms]![ReportForm]![cboTo].[Value]

    That works perfect!!

     

    I tried this for the 'CustomerName':

    =[Forms]![ReportForm]![lstCust].[Text]

    The parameter being passed is indeed Text; it gives me this:  #Name?

    =[Forms]![ReportForm]![lstCust].[Value] . . . gives me nothing at all (I thought this would actually cause an error).

     

    I can't do anything like this:

    =[Forms]![ReportForm]!&"[lstCust]"

    =[Forms]![ReportForm]!&'[lstCust]'

     

    This:

    ="[Forms]![ReportForm]![lstCust]"

    Gives this:

    [Forms]![ReportForm]![lstCust]


    • Edited by ryguy72 Thursday, January 26, 2012 10:54 PM
    Thursday, January 26, 2012 10:47 PM
  • Imb-hb ...

    >> In order to use the VALUE of [Forms]!ReportForm]![lstCust], you have to place it outside the doublequotes <<

    That is NOT true, DLookup() can resolve parameters that refer to Form control references since it is an Access function.  The advantage of NOT placing it outside the dbl-quotes is that the parameter will likely return a specific datatype, and therefor there is no need to worry with delimiters.


    Brent Spaulding | Access MVP
    Friday, January 27, 2012 5:21 AM
  • Imb-hb ...

    >> In order to use the VALUE of [Forms]!ReportForm]![lstCust], you have to place it outside the doublequotes <<

    That is NOT true, DLookup() can resolve parameters that refer to Form control references since it is an Access function.  The advantage of NOT placing it outside the dbl-quotes is that the parameter will likely return a specific datatype, and therefor there is no need to worry with delimiters.


    Brent Spaulding | Access MVP
    Hmm, interesting as I have had it fail before when inside the quotes but never fail (when used with correct delimiters) when concatenated in.
    Bob Larson, Access MVP 2008-2010, 2011
    Friday, January 27, 2012 5:30 AM
  • Do you have the item selected in the list box?  If no item is selected in the list box then the following reference will return a Null.

    =[Forms]![ReportForm]![lstCust]  {which is the same as [Forms]![ReportForm]![lstCust].[Value] since Value is the default property of a list box control}

     

    So ....

    If you do not have an item selected you will have to refer to the item you want directly via the .Column() property or .ItemData() property

    =[Forms]![ReportForm]![lstCust].[ItemData](0)  {will return the bound column value of the first item - row 0 - in the list box}

    You can use .Column() similarly {.Column let you choose a row and column index}, but since your list box only has one column, there is really no need to use it.

     


    Brent Spaulding | Access MVP
    Friday, January 27, 2012 5:51 AM
  • Imb-hb ...

    >> In order to use the VALUE of [Forms]!ReportForm]![lstCust], you have to place it outside the doublequotes <<

    That is NOT true, DLookup() can resolve parameters that refer to Form control references since it is an Access function.  The advantage of NOT placing it outside the dbl-quotes is that the parameter will likely return a specific datatype, and therefor there is no need to worry with delimiters.


    Brent Spaulding | Access MVP


    Hi Brent,

    Thank you for your remark. You are quite right that one may use the Form control reference in an Access function.

    But I experience a couple of drawbacks with this way of working.

    1. It works only for Access functions, and not for "home made" functions. and I do not want to distinguish between the two in how to pass an argument.

    2. Further you must specify the full name of the control in this syntax:
       Forms!formname!controlname
    Any generalization is not allowed, so you may not use form or control variables.

    3. In constructing SQL strings with the same logical selection you may not use this construction either.

     

    So, in my work towards generalization and re-use of code, I never use your proposed syntax, but I realize that this is a personal choice. Thanks to this generalization, I do not even have to bother about datatype syntax either in many cases.

     

    Imb.

    Friday, January 27, 2012 9:20 AM
  • Hello Imb.

    >> But I experience a couple of drawbacks with this way of working. <<

    There are pros and cons to each.  I am not promoting one way over the other I just wanted to remove the idea that it was required to remove the literal form reference from the criteria string of DLookup().  Either usage (inside the string or the value concatenated to it) can be advantageous depending on your scenario.  For example, when if you want a Form dependent Query object to be used in VBA as well as the Access UI, you can wrap your parameter (the form reference) with the Eval() function ...

    SQL of a Query Object:

    SELECT * FROM someTable WHERE someField = Eval("[Forms]![someFormname]![someControl]")

    Then you can open the Query object from the Access UI, plus you can use the Query object to open a recordset in VBA, which is itself a step toward re-use and generalization.

    Also, use of the Eval() wraping and literal Form based parameters can yeild "safer" coding in that it helps reduce the risk for SQL injection.  Again, I am not promoting one technique over the other, just pointing out that there are advantages to either method.

    For me, I also work towards generalization -- and sometimes that generalization has a narrow scope.  So, in situation x, I do this, in situation y, I do that.  Where as some may say, I always do this because it always works, which (IMO) may sometimes lead to a technique that is not optimized to a given scenario -- but -- it works.


    Brent Spaulding | Access MVP
    Friday, January 27, 2012 3:21 PM
  • Dat-

     

    These actually work:

    =[Forms]![ReportForm]![lstCust].[ItemData](0)

    =[Forms]![ReportForm]![lstCust].[ItemData](1)

    =[Forms]![ReportForm]![lstCust].[ItemData](2)

     

    Clearly, though, I can't use the Index to get the result.  The user should be allowed to select an item from the ListBox and pass that (parameter) to the TextBox. 

     

    Sorry to waste so much time with this.  It seems like a simple thing, but nothing that I do actually works.  The 'InvoiceDates' TextBox works perfect and the 'CustomerName' TextBox does nothing whatsoever.  It's almost like only Values work, but no Text will work.  It doesn't make any sense.  There must be some setting; some Property that controls the behavior of the ListBox . . .

    Friday, January 27, 2012 4:58 PM
  • >> These actually work:

    =[Forms]![ReportForm]![lstCust].[ItemData](0) <<

    Did you think they wouldn't ... LOL!!!

    ----

    Is your list box, by chance, marked as a Multi Select list box? If so, and you only want the user to have only one choice, then verify that the "Multi Select" property of the List Box control is set to 'None'.

    With the Multi Select property set to None, the use of the default property (Value) should indeed return the item that is selected....

    =[Forms]![ReportForm]![lstCust]


    Brent Spaulding | Access MVP
    • Edited by datAdrenalineMVP Friday, January 27, 2012 5:11 PM
    • Marked as answer by ryguy72 Friday, January 27, 2012 5:41 PM
    Friday, January 27, 2012 5:08 PM
  • >>  ... but no Text will work ... <<

    The .Text property of a control is not valid unless the control has the focus ... plus a list box control does not have a Text property (IIRC).


    Brent Spaulding | Access MVP
    Friday, January 27, 2012 5:10 PM
  • The ListBox WAS set to Multi Select.  I changed it it to None and everything works fine now.  I think I'll keep it like this.  I know of a way to pass multiple selected items to a parameter, using VBA, but I don't think I'll ever need to do that for this scenario.

     

    Thanks so much, dat!!

     

    This was a good learning exercise!  I think in this one post, we've touched on pretty much all scenarios!  Hope this helps others who find their way here someday.

    Friday, January 27, 2012 5:40 PM