Data Macro equivalent to SELECT TOP 1 (1 Viewer)

[toast]

Hi all,

I'm trying to return the most recent value from a table, before today's date.

As this is a web database, I believe I need to use a data macro to achieve it (which I'm very new to).

So far, I have:

Look Up A Record In   tblPrices
Where Condition = [tblPrices].[dtPriceDate] <= Date()
Alias

Set Return Value
Name latestPrice
Expression = [tblPrices].[dblPrice]

The problem is that I cannot specify the order. This means that the returned value is indeed one of the prices before today's date, but not the most recent one before today's date.

Is there any way to sort the table by date before performing the lookup?

Many thanks in advance

 

[plog]

Macros are for the weak. I would solve this using a query and a sub-query. First create a sub-query to find the latest date that is before today's date in your table:

SELECT MAX(dtPriceDate) AS LatestPriceDate
FROM tblPrices
WHERE dtPriceDate< Date();

Save that query as 'sub_LatestPrice' and build another query using it and tblPrices to find the actual price that matches that date:

SELECT dblPrice
FROM tblPrices INNER JOIN sub_LatestPrice ON tblPrices.dtPriceDate = sub_LastestPrice.LatestPriceDate;

Run that and it will return the price on the latest date prior to today.

 

[toast]

Macros are for the weak. I would solve this using a query and a sub-query. First create a sub-query to find the latest date that is before today's date in your table:

SELECT MAX(dtPriceDate) AS LatestPriceDate
FROM tblPrices
WHERE dtPriceDate< Date();

Save that query as 'sub_LatestPrice' and build another query using it and tblPrices to find the actual price that matches that date:

SELECT dblPrice
FROM tblPrices INNER JOIN sub_LatestPrice ON tblPrices.dtPriceDate = sub_LastestPrice.LatestPriceDate;

Run that and it will return the price on the latest date prior to today.

Macros are for the weak.

And, unfortunately, for web databases (or so it would seem)!

Many thanks for the suggestion, but unfortunately those queries aren't web-compatible... this is why I've been bashing my head against a brick wall with web databases today.

You can't even edit SQL for web queries... :banghead:

 

[toast]

I appear to have found a solution that works.

You can create a data macro on a web query (you have to open a table first in order for Access 2010 to present you the option).

I created a very simple web query which merely sorted the table in descending date order.

I then created a data macro which performed a lookup on that webquery, and returned the value for use on the form.

A simple process, but annoyingly difficult to unearth!