• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

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

0
snyperj
Asked:
snyperj
  • 4
  • 2
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now