I recently learned of TVF's and it seemed like it was exactly what I was looking for. I did my first attempt at one and it is not going well.
Here is my code:
USE [RMC_Tracker] GO /****** Object: UserDefinedFunction [dbo].[TFV_Dashboard] 9:08:08 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Script Date: 4/13/2020 -------------------------------------------- Author: John Clark Create date: 4/12/2020 Description: Returns a recordset for the RMC Tracker ALTER FUNCTION [dbo].[TVF_Dashboad] ( -- Add the parameters for the function here @CompDate datetime = NULL ) RETURNS TABLE AS RETURN ( -- Add the SELECT statement with parameter references here SELECT dbo.ACTIVE.RECORD_ID, dbo.ACTIVE.CMD_ID, dbo.ACTIVE.EQUIPMENT, dbo.ACTIVE.EquipDes, dbo.ACTIVE.CASREP,dbo.ACTIVE.[UPDATE], dbo.ACTIVE.TYAST, dbo.ACTIVE.STATUS,dbo.ACTIVE.JCN, dbo.COMMANDS.Trigrpah FROM dbo.ACTIVE INNER JOIN dbo.COMMANDS ON dbo.ACTIVE.CMD_ID = dbo.COMMANDS.CMD ID WHERE (dbo.ACTIVE. RECCLS = @CompDate) )
I call this Function from within MS Access via a Pass Through Query:
SELECT * FROM dbo.TVF_DashBoard(default)
It returns an empty set. What I am trying to do is create a simple Function that allows me to return a table that can be toggle based on whether a field (RECCLS) is either Null or Not Null.
Any assistance would be appreciated.