Using DLookUp as the default value of a Forms ComboBox
in [Forms]
|
Prev: REquery Subform
Next: Windows 7
From: Pasadena-D via AccessMonster.com on 2 Mar 2010 23:20 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 based on a DLookUp to a query. I want it to take the current date and find the closest value listed in the "qryPay_Period_Ending", and use this as the default value for "ComboBox123" when I 1st open the form. I can do this in Excel as follows, but can't get DLookUp to do it: =VLOOKUP(NOW(),qryPay_Period_Ending,1,TRUE) In Excel I VLOOKUP Now() in the data range "qryPay_Period_Ending", 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!!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1
From: Jack Leach dymondjack at hot mail dot on 3 Mar 2010 05:50 I'm not exactly sure how to get DLookup to accept the correct criteria, but maybe you could write a little public function of your own to handle it... Public Function ClosestDate(sField As String, sTable As String) As Variant Dim Ret As Variant Dim rs As DAO.Recordset Set rs = CurrentDb.Openrecordset( _ "SELECT " & sField & " FROM " & sTable & " " & _ "WHERE #" & sField & "# < #" & Now() & "# " _ "ORDER BY " & sField) If rs.Recordcount <> 0 Then rs.MoveFirst Ret = rs(0) End If rs.Close Set rs = Nothing Closest Date = Ret End Function I haven't tested that where clause but I think it's correct. Basically you're just returning an ascending sorted list of records that have a date value of less then Now. Include proper error handling, etc. hth -- Jack Leach www.tristatemachine.com "I haven''t failed, I''ve found ten thousand ways that don''t work." -Thomas Edison (1847-1931) "Pasadena-D via AccessMonster.com" 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 based on a DLookUp to a > query. I want it to take the current date and find the closest value listed > in the "qryPay_Period_Ending", and use this as the default value for > "ComboBox123" when I 1st open the form. > > I can do this in Excel as follows, but can't get DLookUp to do it: > =VLOOKUP(NOW(),qryPay_Period_Ending,1,TRUE) > In Excel I VLOOKUP Now() in the data range "qryPay_Period_Ending", 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!!! > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1 > > . >
From: Bob Quintal on 3 Mar 2010 18:22 "Pasadena-D via AccessMonster.com" <u56499(a)uwe> wrote in news:a470321f6b9ad(a)uwe: > 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 > based on a DLookUp to a query. I want it to take the current date > and find the closest value listed in the "qryPay_Period_Ending", > and use this as the default value for "ComboBox123" when I 1st > open the form. > > I can do this in Excel as follows, but can't get DLookUp to do it: > =VLOOKUP(NOW(),qryPay_Period_Ending,1,TRUE) > In Excel I VLOOKUP Now() in the data range "qryPay_Period_Ending", > 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.
From: Barry A&P on 4 Mar 2010 14:05 I am very inexperienced at this stuff but here is my two cents... would it be possible to do something with query to get DateDiff where you might have to use an Iff statement to set all negative date diff values to positive values and then use a dlookup with DMin on the date diff maybe that would get you the closest date wether it is past or future..?? Barry "Bob Quintal" wrote: > "Pasadena-D via AccessMonster.com" <u56499(a)uwe> wrote in > news:a470321f6b9ad(a)uwe: > > > 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 > > based on a DLookUp to a query. I want it to take the current date > > and find the closest value listed in the "qryPay_Period_Ending", > > and use this as the default value for "ComboBox123" when I 1st > > open the form. > > > > I can do this in Excel as follows, but can't get DLookUp to do it: > > =VLOOKUP(NOW(),qryPay_Period_Ending,1,TRUE) > > In Excel I VLOOKUP Now() in the data range "qryPay_Period_Ending", > > 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. > . >
From: Pasadena-D via AccessMonster.com on 4 Mar 2010 16:01
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 I use: =DLookUp("Pay_Period_Ending","qryPay_Period_Ending","Pay_Period_Ending >= " & Date()), then it defaults to the 1st date in the query. Any work arounds? 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). > -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1 |