Dlookup usage question (Last date)

[Veli Izzet]

Hi all,

How do I modify the following Dlookup, so I get the latest(by date)
"BirimFiyat"?

DLookup("BirimFiyat";"AlimQ";"MalzemeID="&[MalzemeID])

The date field is "AlimTarihi" and "AlimQ" is sorted by "AlimTarihi".
(BTW is this sorting necessary in this situation?)

Thanks for answers

 

[tina]

well, i can think of a couple ways to get the return value you're after, but
not in a DLookup run directly on the data table. do you have to use a
DLookup? and if you do, does it have to run on the table - or can you run it
on a query?

 

[Veli Izzet]

Hi Tina,

Actually AlimQ is a query, so I can use a query. I want to get the last
acquistion price of an item and put it on a form/subform.

well, i can think of a couple ways to get the return value you're after, but
not in a DLookup run directly on the data table. do you have to use a
DLookup? and if you do, does it have to run on the table - or can you run it
on a query?

Hi all,

How do I modify the following Dlookup, so I get the latest(by date)
"BirimFiyat"?

DLookup("BirimFiyat";"AlimQ";"MalzemeID="&[MalzemeID])

The date field is "AlimTarihi" and "AlimQ" is sorted by "AlimTarihi".
(BTW is this sorting necessary in this situation?)

Thanks for answers

 

[tina]

well, if you're not using the query AlimQ anywhere else, i'd set the
MalzemeID criteria directly in the query, and then turn the query into a
Totals query - with the BirimFiyat field set to GroupBy and the AlimTarihi
field set to Max.

if you *are* using the query AlimQ elsewhere, and don't want to make changes
to it, then i would create another query based on query AlimQ. then apply
the criteria, etc, etc, as directed above.

once you've done either of the above, you should wind up with a query that
returns one record only. then you don't need criteria in the DLookup, just

DLookup("BirimFiyat";"MyQueryName")

there are code-based solutions you can use instead, since you're using the
return value in a form, but there's no need to go that route if this
solution suits you.

hth

Hi Tina,

Actually AlimQ is a query, so I can use a query. I want to get the last
acquistion price of an item and put it on a form/subform.

well, i can think of a couple ways to get the return value you're after, but
not in a DLookup run directly on the data table. do you have to use a
DLookup? and if you do, does it have to run on the table - or can you run it
on a query?

Hi all,

How do I modify the following Dlookup, so I get the latest(by date)
"BirimFiyat"?

DLookup("BirimFiyat";"AlimQ";"MalzemeID="&[MalzemeID])

The date field is "AlimTarihi" and "AlimQ" is sorted by "AlimTarihi".
(BTW is this sorting necessary in this situation?)

Thanks for answers

 

[Veli Izzet]

Well, actually after your mail, I went the same way and created a totals
query, but this time I set the AlimTarihi to last (I sorted the query
on alimtarihi) .

I need to use cirteria though because there are many products (malzeme)
and I want the last price for each of them. Anyhow the problem is solved.
Thanks

well, if you're not using the query AlimQ anywhere else, i'd set the
MalzemeID criteria directly in the query, and then turn the query into a
Totals query - with the BirimFiyat field set to GroupBy and the AlimTarihi
field set to Max.

if you *are* using the query AlimQ elsewhere, and don't want to make changes
to it, then i would create another query based on query AlimQ. then apply
the criteria, etc, etc, as directed above.

once you've done either of the above, you should wind up with a query that
returns one record only. then you don't need criteria in the DLookup, just

DLookup("BirimFiyat";"MyQueryName")

there are code-based solutions you can use instead, since you're using the
return value in a form, but there's no need to go that route if this
solution suits you.

hth

Hi Tina,

Actually AlimQ is a query, so I can use a query. I want to get the last
acquistion price of an item and put it on a form/subform.



but

run it

on a query?




Hi all,

How do I modify the following Dlookup, so I get the latest(by date)
"BirimFiyat"?

DLookup("BirimFiyat";"AlimQ";"MalzemeID="&[MalzemeID])

The date field is "AlimTarihi" and "AlimQ" is sorted by "AlimTarihi".
(BTW is this sorting necessary in this situation?)

Thanks for answers

 

[tina]

you're welcome

Well, actually after your mail, I went the same way and created a totals
query, but this time I set the AlimTarihi to last (I sorted the query
on alimtarihi) .

I need to use cirteria though because there are many products (malzeme)
and I want the last price for each of them. Anyhow the problem is solved.
Thanks

well, if you're not using the query AlimQ anywhere else, i'd set the
MalzemeID criteria directly in the query, and then turn the query into a
Totals query - with the BirimFiyat field set to GroupBy and the AlimTarihi
field set to Max.

if you *are* using the query AlimQ elsewhere, and don't want to make changes
to it, then i would create another query based on query AlimQ. then apply
the criteria, etc, etc, as directed above.

once you've done either of the above, you should wind up with a query that
returns one record only. then you don't need criteria in the DLookup, just

DLookup("BirimFiyat";"MyQueryName")

there are code-based solutions you can use instead, since you're using the
return value in a form, but there's no need to go that route if this
solution suits you.

hth

Hi Tina,

Actually AlimQ is a query, so I can use a query. I want to get the last
acquistion price of an item and put it on a form/subform.



tina wrote:

well, i can think of a couple ways to get the return value you're

after,

but

not in a DLookup run directly on the data table. do you have to use a
DLookup? and if you do, does it have to run on the table - or can you

run it

on a query?




Hi all,

How do I modify the following Dlookup, so I get the latest(by date)
"BirimFiyat"?

DLookup("BirimFiyat";"AlimQ";"MalzemeID="&[MalzemeID])

The date field is "AlimTarihi" and "AlimQ" is sorted by "AlimTarihi".
(BTW is this sorting necessary in this situation?)

Thanks for answers