ODBC Calculated Date Problem
I'm using BarTender label software to create labels from data in our Packaging database. I thought I had everything working OK, but noticed a date discrepancy on 1 of my automated labels that has to do with an expiration date. This expiration date is a calculated date based on a related file called "Master Product" that contains a detail list of all products including a field called "month_life" which is the shelf life of the product in months. The calculation works fine in my Packaging database and it looks something like this:
Exp_Date = Date (Month(Bottling_Date)+Master Product::month_life;Day(Bottling_Date);Year(Bottling_Date))
It would have values like this:
Bottling_Date = 1/1/2009
month_life = 24
Exp_Date = 1/1/2011 based on the calculation
While this works fine in FileMaker and the correct date is displayed, it is unstored and calculated on the fly and cannot be stored due to the external reference to Master Products.
For some reason when I pull this data via ODBC into a label layout, it loses the additional time added to the date, so my Exp_Date would be 1/1/2009 on the label (ARGH!)
At this point the only semi-solution I see is to make a shelf_life field in the Packaging database that is a lookup, but then that lookup field won't get updated if it changes in the Master Product file without manual intervention in the Packaging database to re-lookup that field.
Any suggestions? I'm open to ideas : )