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
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