DLookup

  • Thread starter Thread starter Jen
  • Start date Start date
J

Jen

Hi There -
I am trying to do a DLookup in a query. As part of the
criteria, I want it to find records with a date between a
start and end date that is entered on a form.
Here is what I have
LbsDamage: Nz((DLookUp
("SumOfLbsDamage","qry_SumDmg","[InvTrans_id]=" &
[InvTrans_Id] & "and[Dmg_Date]between" & [Forms]!
[frm_inv_dates]![inv_start] And [Forms]![frm_inv_dates]!
[inv_end])),0)

I have played with different ways to do this and cannot
figure it out. I could put the date requirement in
the "qry_SumDmg", but I want to be able to use the same
query for other purposes without the date filter.

Any help is appreciated.
Thanks!
Jenni
 
Hi There -
I am trying to do a DLookup in a query. As part of the
criteria, I want it to find records with a date between a
start and end date that is entered on a form.
Here is what I have
LbsDamage: Nz((DLookUp
("SumOfLbsDamage","qry_SumDmg","[InvTrans_id]=" &
[InvTrans_Id] & "and[Dmg_Date]between" & [Forms]!
[frm_inv_dates]![inv_start] And [Forms]![frm_inv_dates]!
[inv_end])),0)

I have played with different ways to do this and cannot
figure it out. I could put the date requirement in
the "qry_SumDmg", but I want to be able to use the same
query for other purposes without the date filter.

Any help is appreciated.
Thanks!
Jenni
Click to expand...
You need to use a space between the various parts of the code, i.e. &
"and[Dmg_Date]between" must be & "and [Dmg_Date] between" etc.
Then you must also wrap the date criteria within #'s.

Try it like this:

LbsDamage:Nz(DLookUp("SumOfLbsDamage","qry_SumDmg","[InvTrans_id]=" &
[InvTrans_Id] & " and [Dmg_Date] between #" & [Forms]!
[frm_inv_dates]![inv_start] & "# And #" & [Forms]![frm_inv_dates]!
[inv_end] & "#"),0)

The above also assumes [InvTrans_id] is a Number datatype.

I haven't checked it, but I think I have it all O.K.
 
Back
Top