Avatar of snyperj
snyperj
Flag for United States of America asked on

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

Microsoft Access

Avatar of undefined
Last Comment
GRayL

8/22/2022 - Mon
GRayL

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.
GRayL

Change line 10 to:

sSQL = sSQL & "WHERE(Date_Entered BETWEEN DATEADD(year, -" & vrange & ",#" &  GETDATE() & "#) AND #" &  _ GETDATE() & "#) AND (Customer_No = '" & vCust & "')"
snyperj

ASKER
I do want it to resolve in the back end.  I only want to bring the records across that meet the date criteria.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
GRayL

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
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.
ask a question
snyperj

ASKER
Thanks... works great.  My original code also had an unnecessary & _ at the end of each line causing problems.
GRayL

Thanks, glad to help