Using DLookUp as the default value of a Forms ComboBox
in [Forms]
|
Prev: REquery Subform
Next: Windows 7
From: Bob Quintal on 5 Mar 2010 18:03 "Pasadena-D via AccessMonster.com" <u56499(a)uwe> wrote in news:a48581436ccd5(a)uwe: > Bob, > > If I use: > [Pay_Period_Ending_Filter].[Value]=DLookUp("Pay_Period_Ending", > "qryPay_Period_Ending","Pay_Period_Ending >= " & Date()), then I > get an #ERROR when the form opens. If you named your combobox Pay_Period_Ending_Filter, lose the [] around it and from around its value property. With the [], Access thinks you are referring to a field, not a control. > > If I use: > =DLookUp("Pay_Period_Ending","qryPay_Period_Ending","Pay_Period_End > ing >= " & Date()), then it defaults to the 1st date in the query. > How is the query sorted? Make sure that it is sorted on pay_period_ending. > Any work arounds? see above Q > > Bob Quintal wrote: >>> I have a ComboBox in my form, which we will call "ComboBox123" >>> for now. I'd like the default value of "ComboBox123" to be a >>> date >>[quoted text clipped - 8 lines] >>> and it returns the closest value from column 1 of that data >>> range. I have no clue how to do this in DLookUp. Please help!!! >> >>The closest date may be before or after the current date. >>With either VLookup in Excel or DLookup in Access you will need to >>pick one. >> >>Assuming you pick the next future date, and assuming the field >>with the date to lookup in the query is named Pay_Period (you >>didn't tell us what it is) the syntax for DLookup would be >>ComboBox123.value = Dlookup("Pay_Period", "qryPay_Period_Ending", >>"Pay_Period >= " & date()) >>(all that should be on 1 line). >> > -- Bob Quintal PA is y I've altered my email address. |