DLookup in Set Value

[Liz C]

Hi!

I'm trying to set the value of a field in a form to a number I'm looking up
in a table where it's between certain dates on the form.

Here's the expression in my macro (set value action, to set the value of the
Fuel textbox on my form):

=DLookUp("[FAC_PCT]","RInjFPct","[EFF_END_DT]<=Forms!DateF!ProdEnd and
[EFF_START_]>=Forms!DateF!ProdBeg")

I have 12/1/2005 as the ProdBeg date on the form and 12/31/2005 as the
ProdEnd date on the form. In the RInjFPct table, there is a record where
EFF_END_DT is 4/1/2006 and EFF_START_ is 4/1/2005 and the FAC_PCT is 1.04, so
it should pick this up. I also tried doing the DLookup directly in a text
box on the form and it still gives me a blank. If I type in a text box the
actual dates, rather than referring to the form, it works. Also, If I just
ask for one criteria based on the form, it works, but when I put the "and"
part in, it doesn't.

Thank you in advance for your help!

 

[Steve Schapel]

Liz,

I don't quite understand.
4/1/2006 is not <= 12/31/2005, so the record will not be returned as
it doesn't meet the criteria.

 

[Liz C]

No - the form has 12/1/2005 prodbeg and 12/31/2005 prodend. The record I
want it to return from the table has 4/1/2005 begin and 4/1/2006 end, so it
should pull it.

Liz,

I don't quite understand.
4/1/2006 is not <= 12/31/2005, so the record will not be returned as
it doesn't meet the criteria.

--
Steve Schapel, Microsoft Access MVP

Hi!

I'm trying to set the value of a field in a form to a number I'm looking up
in a table where it's between certain dates on the form.

Here's the expression in my macro (set value action, to set the value of the
Fuel textbox on my form):

=DLookUp("[FAC_PCT]","RInjFPct","[EFF_END_DT]<=Forms!DateF!ProdEnd and
[EFF_START_]>=Forms!DateF!ProdBeg")

I have 12/1/2005 as the ProdBeg date on the form and 12/31/2005 as the
ProdEnd date on the form. In the RInjFPct table, there is a record where
EFF_END_DT is 4/1/2006 and EFF_START_ is 4/1/2005 and the FAC_PCT is 1.04, so
it should pick this up. I also tried doing the DLookup directly in a text
box on the form and it still gives me a blank. If I type in a text box the
actual dates, rather than referring to the form, it works. Also, If I just
ask for one criteria based on the form, it works, but when I put the "and"
part in, it doesn't.

Thank you in advance for your help!

 

[Ken Snell \(MVP\)]

The DLookup may think the date value is actually a string. Try casting the
value from the form's control explicitly as a date:

=DLookUp("[FAC_PCT]","RInjFPct","[EFF_END_DT]<=#" &
CDate(Format(Forms!DateF!ProdEnd,"mm\/dd\/yyyy")) & "# and [EFF_START_]>=#"
& CDate(Format(Forms!DateF!ProdBeg,"mm\/dd\/yyyy")) & "#")

--

Ken Snell
<MS ACCESS MVP>

No - the form has 12/1/2005 prodbeg and 12/31/2005 prodend. The record I
want it to return from the table has 4/1/2005 begin and 4/1/2006 end, so
it
should pull it.

Liz,

I don't quite understand.
4/1/2006 is not <= 12/31/2005, so the record will not be returned as
it doesn't meet the criteria.

--
Steve Schapel, Microsoft Access MVP

Hi!

I'm trying to set the value of a field in a form to a number I'm
looking up
in a table where it's between certain dates on the form.

Here's the expression in my macro (set value action, to set the value
of the
Fuel textbox on my form):

=DLookUp("[FAC_PCT]","RInjFPct","[EFF_END_DT]<=Forms!DateF!ProdEnd and
[EFF_START_]>=Forms!DateF!ProdBeg")

I have 12/1/2005 as the ProdBeg date on the form and 12/31/2005 as the
ProdEnd date on the form. In the RInjFPct table, there is a record
where
EFF_END_DT is 4/1/2006 and EFF_START_ is 4/1/2005 and the FAC_PCT is
1.04, so
it should pick this up. I also tried doing the DLookup directly in a
text
box on the form and it still gives me a blank. If I type in a text box
the
actual dates, rather than referring to the form, it works. Also, If I
just
ask for one criteria based on the form, it works, but when I put the
"and"
part in, it doesn't.

Thank you in advance for your help!

 

[Steve Schapel]

Liz,

Sorry to contradict, but this is simply not correct. I can't quite
figure out what you want. But your criteria includes this:
[EFF_END_DT]<=Forms!DateF!ProdEnd
[EFF_END_DT] = 4/1/2006
Forms!DateF!ProdEnd = 12/31/2005
4/1/2006 is not <= 12/31/2005
Therefore [EFF_END_DT] is not <=Forms!DateF!ProdEnd
Therefore it should not "pull it", as you so delicately express it.

Perhaps if you could explain *why* you think this record should be
returned by the DLookup, someone may be able to help you with the
necessary adjustment.