Using DLookUp as the default value of a Forms ComboBox

[Pasadena-D via AccessMonster.com]

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

 

[Jack Leach]

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)

 

[Bob Quintal]

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

 

[Barry A&P]

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

 

[Pasadena-D via AccessMonster.com]

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?

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]

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

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