I am (trying) to use the code below to modify a pass through query hitting a SQL server back end table, but something is not right as I keep getting a run time error 13. mismatch when I try to modify the query def.
Note: vrange is a combo box selection and the values can be 1, 2, 3, or 5
vCust is an alphnumeric customer nbr.
This is how the query should look, this runs fine and pulls data:
SELECT Company_Code, Order_No, Order_Index, Title, Customer_No, Date_Entered, Customer_PO_Number, Order_Status, Order_Type, Location_Code, Customer_PO_Date, Date_Last_Modified, Audit_Id
FROM OP_ORDER_HEADER
WHERE (Date_Entered BETWEEN DATEADD(year, - 1, GETDATE()) AND GETDATE()) AND (Customer_No = 'UNI052')
ORDER BY Date_Entered
...but I can't seem to arrive at the above by using the below... Thanks..
Dim qdf As DAO.QueryDefDim sSQL As StringDim vrange As Integer ' nbr of previous years to showDim vCust As String ' customer nbr to showSet qdf = CurrentDb.QueryDefs("PT_OP_ORDERHEADER_bkmcth2")sSQL = "SELECT Company_Code, Order_No, Order_Index, Title, Customer_No, Date_Entered, Customer_PO_Number," & vbCrLf & _sSQL = sSQL & "Order_Status, Order_Type, Location_Code, Customer_PO_Date , Date_Last_Modified, Audit_Id" & vbCrLf & _sSQL = sSQL & "From OP_ORDER_HEADER " & vbCrLf & _sSQL = sSQL & "WHERE(Date_Entered BETWEEN DATEADD(year, -" & vrange & ", GETDATE()) AND GETDATE()) AND (Customer_No = '" & vCust & "')"qdf.SQL = sSQL
The way you have it written, GETDATE() will be resolved in the SQL Server backend. If you intend to resolve in Access, then your coding has to reflect that - by including it like you did vrange and vcust.
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.