Pass dates and numerical values to sql with pass through query from Access

Have a pass through query called PTEarnings that works fine as it is:
{CALL "pwhcprod01"."dbo"."cp_Earnings2012Results";1(14, {ts '2013-08-11 00:00:00'}, {ts '2013-08-17 00:00:00'})}

Would like to use a form for the user to modify the 3 parameters. The 14 is the 1st parameter and is numeric.  The 2nd and 3rd are the date range for the query.  Once the parameters are input by the user they would click a box on the form to run the query.  I would then like to save the results in a table they name in the same form.

So
Input branch number  14
Input Beginning date  2013-08-11
Input Ending date  2013-08-17
Type new table name MyNewTable

Have tried but can't quite get the concatenation for the parameters correct. Would prefer not to have to type the 00:00:00 portion of the date as long as the results are the same as if they did.

Help would be appreciated.
LBarrettAsked:
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.

chaauCommented:
I think you need to provide user-friendly controls, i.e. a TextBox for the INT value (with a spinner is the values are not that big) and two Date Pickers. When a user selects the values you need to construct the pass-through statement, like this:
strSQL = "{CALL "pwhcprod01"."dbo"."cp_Earnings2012Results";1("
strSQL = strSQL & CStr(TextBox1.Value) & ", { '"
strSQL = strSQL & Format(DateFrom, "yyyy-mm-dd") & " 00:00:00'}, {ts '"
strSQL = strSQL & Format(DateTo, "yyyy-mm-dd") & " 00:00:00'})}"

Open in new window

Here, DateFrom and DateTo are the date values from the date picker controls
0
LBarrettAuthor Commented:
strSQL = "{CALL "pwhcprod01"."dbo"."cp_Earnings2012Results";1("
strSQL = strSQL & CStr(Forms![RunPayroll]![Branch].Value) & ", { '"
strSQL = strSQL & Format(Forms![RunPayroll]![BegDate].Value, "yyyy-mm-dd") & " 00:00:00'}, {ts '"
strSQL = strSQL & Format(Forms![RunPayroll]![EndDate].Value, "yyyy-mm-dd") & " 00:00:00'})}"

I put this string in the sql view of the the query called PassThrough and called it from the form.

Got error of  SQL Server incorrect syntax near '='(#102)

I put this string in the sql view of the the query called PassThrough and called it from the form.
0
chaauCommented:
Of course, my mistake. Change the first line to this:
strSQL = "{CALL ""pwhcprod01"".""dbo"".""cp_Earnings2012Results"";1("

you need to escape the quotes with double quotes
0
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

LBarrettAuthor Commented:
Tried and got the same error.
0
chaauCommented:
Can you debug the program and show what is in strSQL right before you pass it into SQL Server
0
LBarrettAuthor Commented:
Sorry I don't know how to debug sql in a passthrough query???  Can you enlighten?
0
chaauCommented:
Somehow I knew you tell this. After your last line:
strSQL = strSQL & Format(Forms![RunPayroll]![EndDate].Value, "yyyy-mm-dd") & " 00:00:00'})}"

Can you please add this line:
MsgBox(strSQL)

Then, please take a screenshot of the message box and upload it here
0
LBarrettAuthor Commented:
Same error I was getting:

ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server] Incorrect syntax near '='.(#102)
0
chaauCommented:
In you code, please click on the panel to the left of the MsgBox(strSQL), as per the screenshot below. The red dot should appear.
a chunk of codeThen press F5 (or click on Run button at the top).
Then select your values and wait until the program breaks. The VB window should be displayed. Move the mouse cursor over strSQL on the highlighted line and copy the value of strSQL at the time of execution. Paste it here. It is very important to know what is the value of strSQL before you pass it on to SQL Server.

How do you pass it to SQL Server anyway. Can you also post the code of the whole function?
0
LBarrettAuthor Commented:
you asked how do I pass the code - as I said in the 2nd post it is in the query itself - parameter query that looks at the form for the parameters.  The code is as shown in in the post above Posted on 2013-09-25 at 15:50:52ID: 39522976.  Right after that is when you suggested I double the quote marks.  Does this help?
0
chaauCommented:
OK, I give up.
All I asked was this: show me the value of strSQL, as it is constructed by VB after your code executes.
The value should be similar to this one:
{CALL "pwhcprod01"."dbo"."cp_Earnings2012Results";1(14, {ts '2013-08-11 00:00:00'}, {ts '2013-08-17 00:00:00'})}

Open in new window

I just think there could a problem with dates or Int value conversions
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
LBarrettAuthor Commented:
They have changed the format of this project so I will close the question.  Thank you for your time.
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.