Need help writing a function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kayard@tiscali.it - 27 Sep 2006 15:41 GMT

Hi, I know very little about ACCESS VBA, therefore you are my only
possibility to get this done... I have posted also in the query groups
trying to find out a solution without coding but not having received
any answer I must conclude that coding is necessary.

Let's begin from the end.

I need to build a query (Query1) containing 2 fileds.

First field Query1.RequestedDate must be the copy of field Date in
Table tbCalendar (think of it as an input date)

Second field Query1.MostRecentAvailableDate must be a calculated field
whose value is the most recent date in field tbCatalogue.Date which is
less than the date contained in field1 of Query 1.

Basically i need to build a query that for each date in field Date in
table tbCalendar calculates the most recent date available in field
tbCatalogue.Date which is earlier than the date in tbCalendar.

I hope to have myself understood

Sorry for my english

Paolo

Italy

Reply to this Message

Alex Dybenko - 27 Sep 2006 17:46 GMT

Ciao!
as I understand you - you can do this with such query:

SELECT tbCalendar.RequestedDate, Max(tbCatalogue.CatalogDate) AS
MostRecentAvailableDate
FROM tbCalendar INNER JOIN tbCatalogue ON tbCalendar.RequestedDate >
tbCatalogue.CatalogDate
GROUP BY tbCalendar.RequestedDate;

Signature

Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

> Hi, I know very little about ACCESS VBA, therefore you are my only
> possibility to get this done... I have posted also in the query groups
[quoted text clipped - 23 lines]
>
> Italy

Reply to this Message

kayard - 27 Sep 2006 21:54 GMT

Thanks it works !!!

could you plrase help me with VB code as well ?

I need a function whose interface would be:

GetLastDate(input_date)

that would run this query:

SELECT MAX(Date) FROM (SELECT Date FROM tbCatalogue.Date WHERE
tbCatalogue.Date <= input_date)

So I can use the result of this function in the query builder as a
where clause in another query and get the price form tbCatalogue.Price

I need the compete syntax to write in VBeditor ... as I said I'm a N00b

Thanks again for your work

Paolo

Reply to this Message

Alex Dybenko - 28 Sep 2006 06:37 GMT

Hi Paolo,
you can use DMax function:

DMax("[Date]", "tbCatalogue","[Date] <= " & [input_date])

but if you plan to use it in query - then better to use joins or subquries
for better performance

Signature

Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

> Thanks it works !!!
>
[quoted text clipped - 17 lines]
>
> Paolo

Reply to this Message