Calling a SQL function from MS Access

I wathced the video on the EE webstie on how to call a SQL Scalar and Table function from MS Access.  I thought I got the steps down.  However, I am running into a bit of problem trying to apply what I saw to calling a table function that requires two dates to return output to a PassThru query.

First here is my SQL Table Function.  It works fine when running it from a SQL stored procedure.

USE [livedb]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_getHRTurnovers]    Script Date: 07/05/2015 11:48:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




Create FUNCTION [dbo].[fn_getHRTurnovers]
    (
     @BDate datetime
    ,@EDate datetime
    )
    
    
RETURNS TABLE
AS
RETURN

    /* Prints out all the applicable config parameters and their respective values for the requested report in one XML line */
    (SELECT     TOP (100) PERCENT EmployeeID, EffDt, NewDeptCode, OldPos, MAX(OldPosTitle) AS OldPosTitle, NewPos, MAX(NewPosTitle) AS NewPosTitle, Name, 
                      RIGHT(NewJobCode, 4) AS JobCode, CASE WHEN NewPos = OldPos THEN 0 ELSE 1 END AS TrnsfrChk
FROM         dbo.vw_TransferData
GROUP BY EmployeeID, EffDt, NewDeptCode, OldPos, NewPos, Name, NewJobCode
HAVING      (EmployeeID IS NOT NULL) AND (EffDt > @BDate) and (EffDt < @EDate)
ORDER BY EmployeeID)

    
       

Open in new window


Next, here is my Access Pass Thru query:

It is call "qpass"

Select * from dbo.fn_getHRTurnovers(@BDate,@EDate);

Finally here is the VBA within Access to call the function:

Option Compare Database

Sub ExecuteSQLFunction()
    Dim qdef As DAO.QueryDef
    Set qdef = CurrentDb.QueryDefs("qpass")
    Dim qBDate As Date
    Dim qEDate As Date
    Dim strDate1 As String
    Dim strDate2 As String
    strDate1 = InputBox("Please enter a Beginning and Ending Date", "Enter Beginning Date", qBDate)
    strDate2 = InputBox("Please enter a Beginning and Ending Date", "Enter Beginning Date", qEDate)
    qry.SQL = "Select * from dbo.fn_getHRTurnovers  (strDate1,strDate2)"
    qdef.Close
    Set rst = CurrentDb.OpenRecordset("Select * from qpass", dbOpenDynaset)
    
    
    
    
End Sub

Open in new window


When I try to run this, I get Time variables, not Date.  

I have attached the expected outout when I enter 12/31/2014 and Today as the date parameters.

Can someone help with the proper syntax?

Thanks
new-employees.xlsx
GPSPOWAsked:
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.

ste5anSenior DeveloperCommented:
Yup, you need to insert T-SQL date literals. The most common is 'yyyymmdd'. So I would use a helper function:

Public Function SqlDate(ADate As Date) As String

  SqlDate = Format(Now, "'yyyymmdd'")

End Function

Open in new window


and

qry.SQL = "SELECT * FROM dbo.fn_getHRTurnovers  (" & SqlDate(strDate1) & " , " & SqlDate(strDate2) & ");"

Open in new window

0
GPSPOWAuthor Commented:
I tried the solution.  I am getting the following error:

ByRef argument type mismatch.

Here is what I input into VBA:

Option Compare Database

Sub ExecuteSQLFunction()
    Dim qdef As DAO.QueryDef
    Set qdef = CurrentDb.QueryDefs("qpass")
    Dim strDate1 As String
    Dim strDate2 As String
    strDate1 = InputBox("Please enter a Beginning and Ending Date", "Enter Beginning Date", SqlDate(strDate1))
    strDate2 = InputBox("Please enter a Beginning and Ending Date", "Enter Ending Date", SqlDate(strDate2))
    qry.SQL = "Select * from dbo.fn_getHRTurnovers  (" & SqlDate(strDate1) & ", " & SqlDate(strDate2) & ");"
   
    qdef.Close
    Set rst = CurrentDb.OpenRecordset("Select * from qpass", dbOpenDynaset)
   
   

What could be wrong?

Thanks

Glen
   
   
End Sub
Public Function SqlDate(ADate As Date) As String
    SqlDate = Format(Now, "'yyyymmdd'")
End Function
0
ste5anSenior DeveloperCommented:
Don't touch the date input lines..
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.

GPSPOWAuthor Commented:
i re-wrote the function and vba per your suggestions.

Please see attached and let me know why I am still getting a ByRef argument error.

thank you

Glen
SQL-Access-Function.txt
0
ste5anSenior DeveloperCommented:
It's a type mismatch:

Option Compare Database
Option Explicit

Public Sub ExecuteSQLFunction()

    Dim qdef As DAO.QueryDef
    
    Dim qBDate As Date
    Dim qEdate As Date
    
    qBDate = InputBox("Please enter a Beginning and Ending Date", "Enter Beginning Date", qBDate)
    qEdate = InputBox("Please enter a Beginning and Ending Date", "Enter Beginning Date", qEdate)

    Set qdef = CurrentDb.QueryDefs("qpass")
    qdef.SQL = "Select * from dbo.fn_getHRTurnovers (" & SqlDate(qBDate) & " , " & SqlDate(qEdate) & ");"
    qdef.Close
    
End Sub

Public Function SqlDate(ADate As Date) As String
    SqlDate = Format(Now, "'yyyymmdd'")
End Function

Open in new window


btw, please use Option Explicit in all your code modules.  See Tools\Options in the VBA IDE.

Untitled.png
0
GPSPOWAuthor Commented:
I now have the qPass query working.  However, the SqlDate function is setting the qBDate and qEDate to today's date, not what is input, so the output is null.

I am inputting 2014-12-31 and 2015-06-30 as my qBDate and qEDates.

Also, when the input screen displays, the format is a time not a date.

Thanks

Glen
0
ste5anSenior DeveloperCommented:
Decouple your function from the user interaction:

ublic Sub ExecuteSQLFunction(ABeginDate As Date, AEndDate As Date)

    Dim qdef As DAO.QueryDef
    
    Set qdef = CurrentDb.QueryDefs("qpass")
    qdef.SQL = "Select * from dbo.fn_getHRTurnovers (" & SqlDate(ABeginDate) & " , " & SqlDate(AEndDate) & ");"
    qdef.Close
    
End Sub

Open in new window

0
GPSPOWAuthor Commented:
I have been tinkering with this for a few days and have a qorking pass thru query to a SQL procedure, instead of a function.

Exec  dbo.sp_getHRTurnovers_View_Test '2015-01-01','2015-03-31'

What i want to do is replace the hardcoded dates for two [TempVars] - "DateFrom" and "DateTo"

Here is what I tried but the syntax is not right:

Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Set db = CurrentDb
   
    Set qdf = db.QueryDefs("qpass_transfers")
    qdf.SQL="Exec "dbo.sp_getHRTransfers_View_Test' " &[TempVars]![DateFrom]&","&[TempVars]![DateTo]&'" "

I get messed up on the apostrophes and qutoes.

Can someone help me out?

Thanks

Glen
0
ste5anSenior DeveloperCommented:
Yup, use the SqlDate function. You still need to provide T-SQL date literals. Just use Debug.Print or  MsgBox qdf.SQL to output your created string and compare it to the string in SSMS.

btw, there are some issues with spacing and quotation marks:

qdf.SQL = "EXECUTE dbo.sp_getHRTransfers_View_Test " & SqlDate([TempVars]![DateFrom]) & ", " & SqlDate([TempVars]![DateTo]) & ";"

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
GPSPOWAuthor Commented:
thanks
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.