Need help writing a function

[kayard]

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

 

[Alex Dybenko]

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;

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

 

[kayard]

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

 

[Alex Dybenko]

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


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