Passing a MS Access Form date into an Oracle SQL

Question

I'm using MS Access to pull some data from an Oracle server via a pass-through query. The user is presented with a form in which they can input some variables (such as a date range). I would like the Oracle SQL to be able to pick up the two date fields from the form.

The current SQL (which doesn't work) is as follows:

SELECT a.I_LOAN_NUM, a.I_LOAN_SUB_ALLOC, c.N_EXCLV, e.I_GSL_SPNSR, e.N_GSL_SPNSR,  b.D_CAL, b.C_LOAN_STAT, g.N_CNTRY

FROM SLD_LOAN_MSTR a
JOIN SLD_LOAN_CDL b on b.I_LOAN_ID = a.I_LOAN_ID
JOIN SLD_EXCLV c on c.I_EXCLV_ID = b.I_EXCLV_ID
JOIN SLD_AC d on d.I_AC_ID = b.I_AC_ID
JOIN SLD_CUST e on e.I_CUST_ID = d.I_CUST_ID
JOIN SLD_DPT_CNTRY f on f.I_DPT_ID = b.I_DPT_ID
JOIN SLD_CNTRY g on g.I_CNTRY_ID = f.I_CNTRY_ID

WHERE (b.C_LOAN_STAT = 'SETTLED' and b.D_CAL between [Forms]![Cost Allocation Form]![Start_Date] and [Forms]![Cost Allocation Form]![End_Date])
ORDER BY b.D_CAL

The above SQL works if I replace the form references with hard coded dates, so I know the SQL is generally good. Example:

WHERE (b.C_LOAN_STAT = 'SETTLED'  and b.D_CAL between '01JAN2019' and '01FEB2019')

The error message being generated by the SQL states "ODBC--call failed. [Oracle][ODBC][Ora]ORA-00936: missing expression (#936)"

Both of the date fields in the Form are using the Short Date format.

I'm not sure if this makes any difference or not, but the Form has multiple tabs. From what I've seen from other examples, the Form reference doesn't need to take the tab labels into account.

Thanks

Answer 1

Pass-through queries are executed at the server. In your case the Oracle server doesn't recognize the [Forms]![Cost Allocation Form]![Start_Date] and [Forms]![Cost Allocation Form]![End_Date] attributes.

You could use VBA to dynamically update the query definition of the query to include the form control values. Then execute the query.

Dim strSQL As String
Dim qdf As QueryDef

strSQL = "SELECT a.I_LOAN_NUM, a.I_LOAN_SUB_ALLOC, c.N_EXCLV, e.I_GSL_SPNSR, e.N_GSL_SPNSR,  b.D_CAL, b.C_LOAN_STAT, g.N_CNTRY " & _
        "FROM SLD_LOAN_MSTR a " & _
        "JOIN SLD_LOAN_CDL b on b.I_LOAN_ID = a.I_LOAN_ID " & _
        "JOIN SLD_EXCLV c on c.I_EXCLV_ID = b.I_EXCLV_ID " & _
        "JOIN SLD_AC d on d.I_AC_ID = b.I_AC_ID " & _
        "JOIN SLD_CUST e on e.I_CUST_ID = d.I_CUST_ID " & _
        "JOIN SLD_DPT_CNTRY f on f.I_DPT_ID = b.I_DPT_ID " & _
        "JOIN SLD_CNTRY g on g.I_CNTRY_ID = f.I_CNTRY_ID " & _
        "WHERE (b.C_LOAN_STAT = 'SETTLED' and b.D_CAL between '" & _
        [Forms]![Cost Allocation Form]![Start_Date] & "' and '" & [Forms]![Cost Allocation Form]![End_Date] & _
        "' ORDER BY b.D_CAL"

Set qdf = CurrentDb.QueryDefs("PassThroughQueryName")
qdf.SQL = strSQL

Also since your using dates, I would suggest you format your dates to the ISO format yyyy-mm-dd

in this case formatting the controls like

Format([Forms]![Cost Allocation Form]![Start_Date], "yyyy-mm-dd") & "' and '" & Format([Forms]![Cost Allocation Form]![End_Date], "yyyy-mm-dd")

Another way would be to just use VBA ADO to access the Oracle Server and retrieve the data. You would still need to build up your SQL string as mentioned here.