Insert Missing records from previous record

[bob]

Hello,

I have a problem which is baffling me. I have found very
tedious ways of resolving them, but was wondering if there
was an eaiser way.

I have a table with Julian dates and exchange rates, but
there are gaps in the dates. I am trying to populate the
gaps with the rates from the previous day. Any ideas?

eg.
table
Date (julian) Rate Currency
104005 1.35 CAD
104010 1.37 CAD

I would like to poulate the dates 104006-104009 with the
rate from 104005?
how do I do this

 

[Allen Browne]

If it is just a matter of retrieving the Rate that applies on a date, you
could look up the value with something like this:
ELookup("Rate", "Table1", "[Date] <= 104007" , "Date DESC")
using the extended lookup function from this link:
http://members.iinet.net.au/~allenbrowne/ser-42.html
It works like DLookup(), but allows you to specify the sort order.

If you actually want to populate your lookup table with a record for every
date, you would need to do that with a recordset and VBA loop. It will be
somewhat messy, so probably what you have already done.