0

Expr1: DLookUp("[price]","[query1]","[converted]<=" & [converted] & " And [item]='" & [item] & "'") when converted is a date mm/dd/yyyy converted with CDbl(because i thought that I was wrong writing Expr with date criteria) But again it return wrong

       item     date          converted        price
1   aa  21/10/2022    CDbl([date])  5
2   aa  23/10/2022              7
3   ab  18/10/2022              4
4   ac  21/10/2022              8
5   ab  21/10/2022              9
6   aa  30/10/2022              9


item    date           convertet    price
aa  26/10/2022  CDbl([date])    7   What I expect
aa  26/10/2022              9   What it return
**it return price from row 6 instead of row 2**
June7
  • 19,874
  • 8
  • 24
  • 34
fredi
  • 1

1 Answers1

0

Don't use CDbl. Use the proper syntax for selecting a date and the original true date value:

Expr1: DLookUp("[price]","[query1]","[DateField] <= #" & Format([TrueDateValueField], "yyyy\/mm\/dd") & "# And [item]='" & [item] & "'")
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • I think the wrong return comes because there are gaps between the dates in query 1 – fredi Oct 27 '22 at 13:08
  • No. `DLookup` will just find "a" price at some date earlier then the specified. That will be 1 or 2. – Gustav Oct 27 '22 at 14:09