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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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

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
ste5anSenior DeveloperCommented:
Don't touch the date input lines..
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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

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

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