Stored Procedure with Parameters from Pass-Thru Query in MS Access
I am taking a stab at creating a stored procedure in SQL that is used with a Pass-Thru query from my front-end Access DB. I have multiple parameters that I am looking to pass to the stored procedure but I am getting errors about the data types, etc. I don't think I have those set properly and need a little help figuring them out. Below is the stored procedure. The first 2 parameters are dates (11/1/2016). The next 2 are text fields. And the final 2 are numbers (200.00) . I set them all to Varchar(50) thinking that would cover all the data types but it errors out on the date ones to start. Can someone help me set these to the correct data types?
On the MS Access side I am using the following code on my form to pass the parameters from controls on my form. Then it will populate my listbox with the results. I also think the syntax of this is incorrect too because when I leave some of the controls blank it add ' ' in place of the null value so the '' don't line up with each blank parameter in the pass-thru query. Can someone help me set the SQL up to line up with the parameters if there are actual values and if there isn't any values for some of them then how do I pass those to the pass-thru query without passing just ' ' marks?
ALTER PROCEDURE [dbo].[upMySprocHistory]
(
@FieldDateFrom Varchar(50) = NULL,
@FieldDateTo Varchar(50) = NULL,
@FieldCustNo Varchar(50) = NULL,
@FieldGroupCode Varchar(50) = NULL,
@FieldQtyMin Varchar(50) = NULL,
@FieldQtyMax Varchar(50) = NULL
)
AS
SELECT *
FROM viewHistoricalData v
WHERE ((@FieldDateFrom Is Null) Or (v.DateCreated)>= @FieldDateFrom) AND
((@FieldDateTo Is Null) Or (v.DateCreated)<= @FieldDateTo) AND
((@FieldCustNo Is Null) Or (v.CustNo)= @FieldCustNo) AND
((@FieldGroupCode Is Null) Or (v.GroupCode)= @FieldGroupCode) AND
((@FieldQtyMin Is Null) Or (v.Quantity)>= @FieldQtyMin) AND
((@FieldQtyMax Is Null) Or (v.Quantity)<= @FieldQtyMax)On the MS Access side I am using the following code on my form to pass the parameters from controls on my form. Then it will populate my listbox with the results. I also think the syntax of this is incorrect too because when I leave some of the controls blank it add ' ' in place of the null value so the '' don't line up with each blank parameter in the pass-thru query. Can someone help me set the SQL up to line up with the parameters if there are actual values and if there isn't any values for some of them then how do I pass those to the pass-thru query without passing just ' ' marks?
strQry = "qryPTupMySprocHistory"
strSQL = "Execute upMySprocHistory"
strOldSQL = ChangeSQL(strQry, strSQL & "'" & _
Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateFrom] & "'" & _
Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateTo] & "'" & _
Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboCustomer] & "'" & _
Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboGroupCode] & "'" & _
Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMin] & "'" & _
Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMax] & "'") 'etc. for more parametersSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So it would look like this?
strOldSQL = ChangeSQL(strQry, strSQL & "'" & _
IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateFrom], "NULL") & "'" & _
IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateTo], "NULL") & "'" & _
IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboCustomer], "NULL") & "'" & _
IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboGroupCode], "NULL") & "'" & _
IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMin], "NULL") & "'" & _
IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMax], "NULL") & "'") 'etc. for more parametersASKER
@Kyle,
So I would leave the SQL code in my VBA form the same and just remove the word Null from my SP?
So I would leave the SQL code in my VBA form the same and just remove the word Null from my SP?
ASKER
I change the sql in my SP to '' instead of using Null. Now when I run my SQL from access I get this build in my pass-thru query when only selecting the first 3 parameters. It doesn't look right to me. Then when I try to run it I get an error that says "Incorrect syntax near '11'. (#102) Unclosed quotation mark after the character string '100987'''
Execute upMySprocHistory'11/1/2014'11/1/2016'100987''''SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I changed the SQL in my SP to show '' instead of using Null. Do I still need to use the IsNull in my VBA code or just add the commas?
If I just add the commas I still get an error with the syntax of the PT query. There are 3 other parameters that I left blank and it creates the PT query like this:
How do I not pass through the parameters that should just be null?
If I just add the commas I still get an error with the syntax of the PT query. There are 3 other parameters that I left blank and it creates the PT query like this:
Execute upMySprocHistory '11/1/2016', 11/1/2016', 100987', ', ', 'How do I not pass through the parameters that should just be null?
ASKER
I am still getting another error saying "Converting data type varchar to numeric."
You have to pass them all, just start them as blank.
The query should look like:
Execute upMySprocHistory '11/1/2016', '11/1/2016', '100987', '','', ''
The query should look like:
Execute upMySprocHistory '11/1/2016', '11/1/2016', '100987', '','', ''
ASKER
Just to test it out I put that in the query manually and I get that error message about converting data type varchar to numeric. Which leads me to the first part of my post about the datatype I have in my SP. Is that correct? If so then why am I getting this error?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Make sure that when you get that string that there's a comma between each parameter. What I find easiest is to build the string in VBA and then call the pass through query from VBA. That way you can test the string manually if needed.
kelvin