This past weekend we moved all our Sesame files, programs and data, to a new partition on the server, separating it from the operating system files. So far everything has been working well after a few tweaks of shortcuts and etc.

However, one of our reports is crashing the Sesame program with a "Divided by zero" error. I've figured out that what's causing it is a hard-coded @Xlookup() command in the programming of the report which retrieves the total of the previous year's billing in the same month from a database that lives outside our main application  (for example, running it this month should retrieve January 2011's totals). This is the command:

MonthTotal = @ToMoney(@XLookup("C:\Sesame\Miara\MonthTotals.db", @Year(@Date) - 1, "Year", @Month$(@Date))) 

As you can see, this report, which runs in our Invoice database, looks into an external database called MonthTotals, which used to live on the C: drive. It now lives on the F: drive, but rather than just prolong the problem by hard-coding F:, I would like to merge the MonthTotals database into our main application to live alongside Invoice and the others.

That's easy enough. What I need help with is transforming the @XLookup() command into an XLU() command. It's been a few years since I coded XLookups, and I'm having trouble with this.

Any help would be appreciated. Thanks.

Hi Erika, thank you for your help.  The problem is not with the values.  The MonthTotals database is full of valid values, and the report with this command has worked for us for years.  The problem is that since the database has moved from C: to F:, the report cannot find the database with its current coding, and so no xlookup value is returned.  So when it plugs it into the portion of the report which should calculate a percentage of this month's billing compared to last year's billing during the same month, zero is getting plugged into the denominator.  The error is not a surprise at all, because I coded it in a way that doesn't allow for data migration.  I messed up when I originally coded it that way some five years ago or so.

I have tried hard-coding the path to the new location, and it works just fine.  But I want to eliminate the hard-coding completely and so have merged MonthTotals.db into my main application.  It is now no longer an external lookup.  I know the new command I have to write begins with "XLU(@FN" but from that point on I'm getting lost.

Hopefully that clarifies what I'm trying to do.  I'll be experimenting tomorrow to see if I can figure out the rest of the syntax and what goes where, but some hand-holding would be most appreciated.  Thanks!

Thank you Carl, I'll give it a try.

Carl, I forgot to mention your code worked great. Thank you for your help.