troubleshooting Question

Please help with code to modify query def

Avatar of snyperj
snyperjFlag for United States of America asked on
Microsoft Access
6 Comments1 Solution342 ViewsLast Modified:
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.QueryDef
Dim sSQL As String
Dim vrange As Integer ' nbr of previous years to show
Dim vCust As String   ' customer nbr to show

Set 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
ASKER CERTIFIED SOLUTION
GRayL

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros