Combo Box Default Value based on a query

[Pasadena-D via AccessMonster.com]

I have a ComboBox in my form, which is called "Pay_Period_Ending_Filter". I'd
like the default value of "Pay_Period_Ending_Filter" 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" query, and "Pay_Period_Ending"
field, and use this as the default value for "Pay_Period_Ending_Filter" when
I 1st open the form.

I've tried this in Access, but it gives me an error:
[Pay_Period_Ending_Filter].[Value]=DLookUp("Pay_Period_Ending",
"qryPay_Period_Ending","Pay_Period_Ending >= " & Date())

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

 

[XPS350]

I have a ComboBox in my form, which is called "Pay_Period_Ending_Filter". I'd
like the default value of "Pay_Period_Ending_Filter" 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" query, and "Pay_Period_Ending"
field, and use this as the default value for "Pay_Period_Ending_Filter" when
I 1st open the form.

I've tried this in Access, but it gives me an error:
[Pay_Period_Ending_Filter].[Value]=DLookUp("Pay_Period_Ending",
"qryPay_Period_Ending","Pay_Period_Ending >= " & Date())

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

First of all you need to place delimiters around dates:
"Pay_Period_Ending >=#" & Date() & "#"

Secondly I think DLookup might not give you the proper result. It
returns a value >= date. It might not be the closest to teh current
date. Maybe you better use Dmin instead.

Groeten,

Peter
http://access.xps350.com

 

[Douglas J. Steele]

On 2 mei, 20:55, "Pasadena-D via AccessMonster.com" <u56499@uwe>

First of all you need to place delimiters around dates:
"Pay_Period_Ending >=#" & Date() & "#"

Actually, since you cannot be certain of how the user's Short Date format
has been defined, you're better off using

"Pay_Period_Ending >=" & Format(Date(), "\#yyyy\-mm\-dd\#")

 

[Pasadena-D via AccessMonster.com]

Peter and Douglas,

I have tried both of your recommendations (listed below), and I still get a
#ERROR. Can either of you think of anything else?

[Pay_Period_Ending_Filter].[Value]=DLookUp("Pay_Period_Ending",
"qryPay_Period_Ending","Pay_Period_Ending >=#" & Date() & "#")


[Pay_Period_Ending_Filter].[Value]=DLookUp("Pay_Period_Ending",
"qryPay_Period_Ending","Pay_Period_Ending >=" & Format(Date(),"\#yyyy\-mm\-dd\
#"))

 

[Mike Painter]

Peter and Douglas,

I have tried both of your recommendations (listed below), and I still
get a #ERROR. Can either of you think of anything else?

[Pay_Period_Ending_Filter].[Value]=DLookUp("Pay_Period_Ending",
"qryPay_Period_Ending","Pay_Period_Ending >=#" & Date() & "#")


[Pay_Period_Ending_Filter].[Value]=DLookUp("Pay_Period_Ending",
"qryPay_Period_Ending","Pay_Period_Ending >=" &
Format(Date(),"\#yyyy\-mm\-dd\ #"))

What is the error?
Where are you using this statement? I'm guessing that it should be
Me.[pay_period...

As mentioned be aware that your dLookup will return the first value >= the
date but that might not be the closest value

 

[Larry Linson]

For "closest to current" date, you need DMax, not DLookup, and _definitely_
not DMin.

Larry Linson
Microsoft Office Access MVP

 

[XPS350]

For "closest to current" date, you need DMax, not DLookup, and _definitely_
not DMin.

 Larry Linson
 Microsoft Office Access MVP

You "definitely" need DMin if you are looking for "closest to current
and greater than", which OP seems to be looking for
("Pay_Period_Ending >= " & Date()).


Groeten,

Peter
http://access.xps350.com

 

[Pasadena-D via AccessMonster.com]

Thanks for all your feedback. After trying all of your suggestions I thought
"let me try to reference the table instead of the query" and it worked. Not
sure why, but it did. Thanks!