I have a Firebird database where I used a Stored Procedure named SP_SALES to generate quite a complex sales report. I now want to consolidate the sales reports from a few DB's into one report using MS Access. In Access I created a normal pass though query acting as a linked table and will do this for each database from where I can easily consolidate the reports. My issue now is that I have FROM DATE and TO DATE parameters in my stored procedure. So my pass through query in Access looks like this:
SELECT * FROM SP_SALES ('2019/01/01' , '2019/12/31')
The user should be able to define the FROM DATE and the TO DATE when pulling my consolidated report. So I have created a userform where this can be populated. The userform is named frm_CONSOLIDATED_SALES and the date boxes are named txt_FROM_DATE and txt_TO-DATE respectively. So I changed my Access query to look as follows:
SELECT * FROM SP_SALES ([Forms]![frm_CONSOLIDATED_SALES][txt_FROM_DATE] , [Forms][frm_CONSOLIDATED_SALES][txt_TO_DATE])
I am however getting a TOKEN UNKNOWN error when running the query. I am trying to stay clear from doing this in VBA as I am not sure how to do a pass through query in VBA. However, if I need to go that route any assistance will be greatly appreciated.
How should Firebird know anything about your database when receiving a pass-through query? It has no idea what [Forms]![frm_CONSOLIDATED_SALES][txt_FROM_DATE] is about other than invalid syntax.
So, before passing the query, adjust its SQL property to:
SELECT * FROM SP_SALES ('2019/01/01' , '2019/12/31')
using something like:
YourQuery.SQL = "SELECT * FROM SP_SALES ('" & Format([txt_From_Date], "yyyy\/mm\/dd") & "' , '" & Format([txt_To_Date], "yyyy\/mm\/dd") & "')"