Dlast vs Dlookup and Dmax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to find the last record and price for a stock in another table. During
this time I have found that Dlast is the function I want. But it is not
giving me the result I want and I know why now.

It’s an easy thing with dlast. When the user clicks a combobox of the stock
the last price executed should be default. Like this:

Private Sub Stock_Change()
Dim dblLastStockValue As Double
dblLastStockValue = DLast("[Price]", " tblExecuted", "[StockNo]=" &
Me.Stock)
Price.Value = dblLastStockValue
End Sub

I have been struggling around with Dlookup and Dmax without any luck and
would appreciate some guidance in the right direction. Maybe Dlookup with
Dmax is’t appropriate here?

I have two tables with stocks. One table with unexecuted orders and one with
executed orders. The structure of the tables is almost the same.

tblExecuted
OrderNo Stock Price
1 AAA 50
2 BBB 70
3 AAA 55
4 AAA 65
5 BBB 75
6 CCC 62

On the form of the tblUnexecuted Orders, the user selects the AAA stock, I
want the last price of AAA to be 65 in the textbox, and BBB would generate 75.

Any suggestions without using Dlast?

TIA

Stefan
 
None of these functions can provide what you need.

DLookup() returns the first match - not what you want.
DMax() returns the highest value - not what you want.
DLast() returns the last value, but that's not useful because you cannot
specify a sort order, so "last" is meaningless.

This page contains a replacement for DLookup():
http://allenbrowne.com/ser-42.html

It provides a 4th argument to specify how the records should be sorted, and
therefore which of the matching records gets returned. You can then code:
dblLastStockValue = ELookup("[Price]", " tblExecuted", _
"[StockNo]=" & Me.Stock, "OrderNo DESC")

As a bonus, it's nearly twice as fast as DLookup(), and it distinguishes
nulls and zero-length-strings correctly--something Microsoft got wrong with
their domain aggregate functions.
 
It is interesting you have no date or time the order was executed. I am
assuming that OrderNo is a sequential number in which the orders placed. If
that is true, then this should work:

=DMAX("[Price]","tblExecuted","[Stock] = '" & Me.Stock & "'")
 
Dear Allen
I have seen your solution on the web before and I don’t know why I didn’t
use it before. It would have saved me a lot of hours.

It works excellent!!!

A big thanks for your wonderful solution. IT IS GREAT…

Stefan

Allen Browne said:
None of these functions can provide what you need.

DLookup() returns the first match - not what you want.
DMax() returns the highest value - not what you want.
DLast() returns the last value, but that's not useful because you cannot
specify a sort order, so "last" is meaningless.

This page contains a replacement for DLookup():
http://allenbrowne.com/ser-42.html

It provides a 4th argument to specify how the records should be sorted, and
therefore which of the matching records gets returned. You can then code:
dblLastStockValue = ELookup("[Price]", " tblExecuted", _
"[StockNo]=" & Me.Stock, "OrderNo DESC")

As a bonus, it's nearly twice as fast as DLookup(), and it distinguishes
nulls and zero-length-strings correctly--something Microsoft got wrong with
their domain aggregate functions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Stefan said:
I want to find the last record and price for a stock in another table.
During
this time I have found that Dlast is the function I want. But it is not
giving me the result I want and I know why now.

It's an easy thing with dlast. When the user clicks a combobox of the
stock
the last price executed should be default. Like this:

Private Sub Stock_Change()
Dim dblLastStockValue As Double
dblLastStockValue = DLast("[Price]", " tblExecuted", "[StockNo]=" &
Me.Stock)
Price.Value = dblLastStockValue
End Sub

I have been struggling around with Dlookup and Dmax without any luck and
would appreciate some guidance in the right direction. Maybe Dlookup with
Dmax is't appropriate here?

I have two tables with stocks. One table with unexecuted orders and one
with
executed orders. The structure of the tables is almost the same.

tblExecuted
OrderNo Stock Price
1 AAA 50
2 BBB 70
3 AAA 55
4 AAA 65
5 BBB 75
6 CCC 62

On the form of the tblUnexecuted Orders, the user selects the AAA stock, I
want the last price of AAA to be 65 in the textbox, and BBB would generate
75.

Any suggestions without using Dlast?

TIA

Stefan
Click to expand...
Click to expand...
 
It is interesting you have no date or time the order was executed. I am
assuming that OrderNo is a sequential number in which the orders placed. If
that is true, then this should work:

=DMAX("[Price]","tblExecuted","[Stock] = '" & Me.Stock & "'")
Click to expand...

Klatuu, won't this return the maximum price for this stock, regardless
of WHEN the trade was executed?

John W. Vinson[MVP]
 
Back
Top