comparing dates resolved

[pame1la]

i have a table that contains dates and rates, i want to create a query which returns the rate corresponding to todays date or the closest date in the past

(i know how to say select where date = a certain date but how do i do the above???)

any ideas????

[Arachnid13]

loop through the date values and then compare them to today's date using

ChosenDate = ?
TodaysDate = Date
Days = datediff("d",TodaysDate,ChosenDate)

then the the Days variable will tell you how many days there are between the dates (0 would be today) so in your app you could find the date with the lowest days value and use that

[pame1la]

okay so i am right in thinkin i would have a sql query which will call all the dates and rates from the table, then compare dates with todays date storing the difference in an array. Then go through the array, and find the smallest diff...then get the corresponding date and therefore rate?

isn't there any similar way of doin it, i mean is there no way of writing a select statement that selects rate where dif between todays date and dte is smallest????

[BIOSTALL]

Could you not sort your table into ascending order by date using SORT BY name of date column ASC. Then.. get todays date, search for it in the table then surely the next record (.movenext) will be the closest to todays date?



BIOSTALL

[Arachnid13]

unfortunately not, the only way to select the smallest value from an array or any collection of values is by looping through all of them

[pame1la]

Could you not sort your table into ascending order by date using SORT BY name of date column ASC. Then.. get todays date, search for it in the table then surely the next record (.movenext) will be the closest to todays date?



BIOSTALL

yeah i think so, il try and let u know

[Arachnid13]

@Biostall:

using a sort would work, however i was under the impression she was doing this entirely from vb, can you sort the columns from there?

[BIOSTALL]

In an sql query you can. And i meant ORDER BY, not SORT BY, sorry

BIOSTALL

[BruceG]

Hold the phone, folks. You can do this with a query like the following:

strSQL = "SELECT RateField FROM TheTable " _
          & " WHERE DateField = (SELECT MAX(DateField) FROM TheTable " _
          & " WHERE DateField <= #" & Format$(Date, "m/d/yyyy") & "#"

[pame1la]

Thanks.....