Dlast vs Dlookup and Dmax

S

Stefan

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
 
A

Allen Browne

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.
 
K

Klatuu

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 & "'")
 
S

Stefan

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...
 
J

John Vinson

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]
 
Top