Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2013

    Unanswered: DLookUp Formatting

    Good Morning,

    Do not know if this is possible and I am at a loss. Trying to use DLookUp in a Select Distinct SQL expression. Here is what I have.

    Dim strSQL As String

    strSQL=SELECT DISTINCT [tblServices].[Services] FROM tblServices WHERE DLookUp ("SelectStartDate","tblSelectStartDate","Collect_S tart="[Forms]![tblSelectStartDate]![SelectStartDate])

    I am trying to filter in the table tblServices.Collect_Start the user's selection which is located in tblSelectStartDate.SelectStartDate

    To put it another way, tblServices.Collect_Start should display (filter) the records that contain the user's selection which is located in tblSelectStartDate.SelectStartDate

    I think it is a matter of formatting to put "" in the correct place in the statement before and after the = sign. I have searched the DLookUp formatting but this seems to be a special case.

    Thanks for any help.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    why use a select distinct AND a dlookup?
    dlookup returns the first row that matches the criteria

    usually I'd expect to use either a selectg OR dlookup but bot a dlookup inside a select.
    I don't know in detail how dlookup works (Im expecting it to effectively be a select statement under the covers just that is packaged as a macro for people scared of SQL

    fist off you date criteria should be delinited by the hash symbol
    CollectS tart= #"[Forms]![tblSelectStartDate]![SelectStartDate] & "#"
    dates should be wuoted as either ISO (YYY/MM/DD) or US ("MM/DD/YYYY") format. if the value is coming from a control that 'knows' its bound to a date control then you should be fine

    as to your current design I dont' really understand wht you are trying to do beyond
    you want distinct services from table tblservices
    where ?
    the dlookup will return the first selectstartdate from tblSelectStartDate whose collect_Start = a value from your form.
    but you dont' actually do anything with the dlookup bit
    say the selectstartdate for collectstart of 05 Ddecember 2013 is 01 December 2013
    effectively what you have written is
    strSQL=SELECT DISTINCT [tblServices].[Services] FROM tblServices WHERE #2013/12/01#"[/code]

    what I don't know is what you expect to do with that

    Im guessing you mean to supply another column in tblservices that euqlas that vlaue

    strSQL=SELECT DISTINCT [tblServices].[Services] FROM tblServices WHERE mydatecolumn = " & DLookUp ("SelectStartDate","tblSelectStartDate","Collect_Start= #"[Forms]![tblSelectStartDate]![SelectStartDate] & "#")

    the whole statement can be rewritten to eliminate the dlookup altogether using a join
    strSQL=SELECT DISTINCT [tblServices].[Services] FROM tblServices
    join tblselectstartdate on tblservices.mydatecolumn = tblselectstartdate
    WHERE tblSelectStartDate.Collect_Start= #" & [Forms]![tblSelectStartDate]![SelectStartDate] & "#"
    I'd rather be riding on the Tiger 800 or the Norton

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