Please help with code to modify query def

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

Open in new window

snyperjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GRayLCommented:
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.
0
GRayLCommented:
Change line 10 to:

sSQL = sSQL & "WHERE(Date_Entered BETWEEN DATEADD(year, -" & vrange & ",#" &  GETDATE() & "#) AND #" &  _ GETDATE() & "#) AND (Customer_No = '" & vCust & "')"
0
snyperjAuthor Commented:
I do want it to resolve in the back end.  I only want to bring the records across that meet the date criteria.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GRayLCommented:
I always seem to get into trouble if I do not dimension a separate dataabase - in this case db.  My texts tell me to always refresh the collection after having made the change to one of them.  If you resolve GETDATE() in the server, leave line 10 or your original code alone  but make the other changes I have shown below.
Dim qdfs as CAO.QueryDefs, qdf As DAO.QueryDef, db as DAO.Database
Dim sSQL As String
Dim vrange As Integer ' nbr of previous years to show
Dim vCust As String   ' customer nbr to show
Set db = CurrentDB
Set qdf = db.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
db.qdfs.refresh

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
snyperjAuthor Commented:
Thanks... works great.  My original code also had an unnecessary & _ at the end of each line causing problems.
0
GRayLCommented:
Thanks, glad to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.