Converting query in Access to TSQL for pass through query

Please excuse my ignorance as I'm very much a novice.  I'm trying to pull records from the previous day and if I'm pulling data on a Monday, I need to pull Friday and Saturday data as well.

I realize the IIF statement is creating issues in the pass through; I just can't figure out how to write the expression in the query to accomplish this.  I appreciate the comments that have been made and will be very greatful for any additional insight.

Thanks again for any and all assistance.

I realize the IIF statement is creating issues in the pass through

..and let us guess? What issues?

btw, to write pt queries: Use SSMS (SQL Server Management Studio). You'll get better feedback here.

Please excuse my ignorance as I'm very much a novice.
There are two parts usually to a passthrough.
Usually, there's a stored procedure on SQL Server that does all the work written in SSMS (SQL Server Management Studio).
In Access, there is a passthrough query that executes the stored procedure with an optional bunch of parameters being passed in.

@Nick: This is not quite correct. You can execute any T-SQL batch in a passthrough query. You don't need to call a stored procedure.

I'm working on the pass through query and have everything working except the syntax for the date.  Just FYI

Just because I like to lather, rinse and repeat: What have you tried exactly? What is the exact error message?
What is your SQL Server version?

You don't need to call a stored procedure.
True, but really why else bother with a passthrough?
Still:

This plays on my machine and will pull data from SQL Server
Use [MyDB]
Select * from tblInsDetails where [Date] >= '26-Mar-15'  and [Date] <= '25-Apr-15 11:59 PM'

Now, to wrinkle it (I am cheating and doing this in SSMS which has syntax support and copy-and-pasting back to the Access passthrough. I've built this in SSMS and it runs

Use MyDB
Declare @StartDate smalldatetime =(
      case WHEN
      DATEPART(WEEKDAY, GETDATE()) = 2 THEN GETDATE() - 3
      ELSE GETDATE() - 1
      END)
Declare @EndDate smalldatetime = getdate()
Select  tblInsDetails.* from tblInsDetails where [Date] >= @StartDate  and [Date] <= @EndDate;

But in Access, fuggetaboutit.
Even this doesn't play
Use TI_Data
Declare @EndDate smalldatetime = getdate()
Select  tblInsDetails.* from tblInsDetails where [Date] >= '26-Mar-15'  and [Date] <= '25-Apr-15 11:59 PM';

or even this
Use TI_Data
Declare @EndDate smalldatetime = getdate()
Select @EndDate

So even though I've got good T-SQL, what the passthrough will do seems to be limited
No Declares it seems

But this played well:
Use myDB
Select  tblInsDetails.* from tblInsDetails where [Date] >=
 (
      case WHEN
      DATEPART(WEEKDAY, GETDATE()) = 2 THEN GETDATE() - 3
      ELSE GETDATE() - 1
END )   and [Date] <= GetDate();

True, but really why else bother with a passthrough?

Performance. Mainly affected by network latency.

For your examples: They all should work, when you use SQL Server 2008+. But you forgot one detail how Jet interact with SQL Server. The driver layer evaluates the first row set. This is under most circumstances the number of rows affected. Thus you need as first statement:

SET NOCOUNT ON;

E.g.

SET NOCOUNT ON;

DECLARE @StartDate SMALLDATETIME = ( CASE WHEN DATEPART(WEEKDAY, GETDATE()) = 2 THEN GETDATE() - 3
                                          ELSE GETDATE() - 1
                                     END );
									 
DECLARE @EndDate SMALLDATETIME = GETDATE();

SELECT  @StartDate AS StartDate ,
        @EndDate AS EndDate;

Select allOpen in new window

The only reason why you shouldn't do this: This queries are ad-hoc queries. So they have a littel overhead on SQL Server (compiling, plan caching and reuse).

You asked what I was using and it's Access.  This has to be built in Access.  I am not able to use SSMS.

I am not able to use SSMS.

This is normally not correct. Cause you can connect to your database server in Access. Thus you should be able to connect to your database server also in SSMS. Just use the same credentials.

@Lwc0724
I am not able to use SSMS.
It is a free install.
In some ways complex to get right, but still free.
And really, either you or the database administrator, is going to have (and need!)access to this tool or its paid-for big brother.  It is like saying you are charged with maintaining car but the only tools you have and are permitted to use are screwdrivers.

Please excuse my ignorance as I'm very much a novice.
This has to be built in Access.
Your data is on SQL Server.
The passthrough is used to access SQL Server bypassing all of Access's normal facilities.
So really, what you are building is going to live, execute and be compliant with SQL Server, and Access is just really not the tool to build it with.

My first example was
Exec spEngRevenueByMonth '26-Mar-15' ,'25-Apr-15 11:59 PM'
This pulls data from the SQL Server using the stored procedure spEngRevenueByMonth
While technically, I could create all the T-SQL of the stored procedure in the passthrough and run it ad-hoc, here is that code, altered as @ste5an suggests might make it work just as a passthrough

SET NOCOUNT ON;
--what I ultimately need is a listing of invoiced JobIDs that are only done by engineers OR
--invoiced Jobs that have been submitted for cert all in a given time bracket OR
--invoiced jobs that are not never going to be certed that invove an engineer.
--any jobs that have the override checkbox set (ie, NeverInvoice jobs, jobs that will never cert ect.)

-- a table variable to hold the values while they get tagged with YTD or LastWeek
Declare @Engineers table (
InspectorID int ,
InspectorName nvarchar(255),
[Role] nvarchar(50))

insert into @Engineers (
InspectorID,
InspectorName,
[Role])

Select InspectorID, InspectorName,[Role] from tblInspectors where [Role] = 'Eng' and [inactive] =0
Union
Select 0 as InspectorID,'' as InspectorName,'' as [Role]	

SELECT  Distinct   dbo.tblInsDetails.JobID, tblClients_1.[Client Name] AS Owner, dbo.tblClients.[Client Name] AS Consignee, dbo.tblInsDetails.Date, dbo.tblInsDetails.WO#, 
                      dbo.tblInsDetails.EngPO, dbo.tblInsTypes.InsTypeName, dbo.tblGeneralEquipType.GeneralEquipType,  dbo.tblCertResults.GeneralEquipTypeID, dbo.tblEquipmentType.EquipmentType, 
                      dbo.tblCertResults.EquipmentTypeID, dbo.tblCertResults.Item, dbo.tblCertStatus.DateSubmitted , dbo.tblInsDetails.InvoiceNumber, dbo.tblInsDetails.InvoiceDate, dbo.tblCertResults.Capacity, 
                      dbo.tblInvoiceSummary.EngCertification, dbo.tblInvoiceSummary.EngServices, dbo.tblInvoiceSummary.SubTotal, dbo.tblInvoiceSummary.GST, 
                      dbo.tblInvoiceSummary.Total,(
case 
	when
	(dbo.tblInsDetails.[InspectorID] IN (Select InspectorID from @Engineers) And
	(dbo.tblInsDetails.[InspectorID2] IN (Select InspectorID from @Engineers)) and
	(dbo.tblInsDetails.[InspectorID3] IN (Select InspectorID from @Engineers)) and
	(dbo.tblInsDetails.[InspectorID4] IN (Select InspectorID from @Engineers)) and
	(dbo.tblInsDetails.[InspectorID5] IN (Select InspectorID from @Engineers)))  then 1
	else 0 
end) as onlyEngineers,(
case
	when tblCertStatus.DateSubmitted Is Not Null then 1
	ELSE 0
END ) as isCert,(
case when 
((dbo.tblInsDetails.[InspectorID] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) Or
(dbo.tblInsDetails.[InspectorID2] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID3] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID4] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID5] IN (Select InspectorID from @Engineers Where InspectorID <> 0))) And 
dbo.tblInsTypes.InsTypeName not like '%Eng Cert%'  And 
dbo.tblInsTypes.InsTypeName not like '%CAODC%' then 1
	else 0 
end) as InvolvesEngineers,(
case when 
((dbo.tblInsDetails.[InspectorID] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) Or
(dbo.tblInsDetails.[InspectorID2] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID3] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID4] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID5] IN (Select InspectorID from @Engineers Where InspectorID <> 0))) then 1
	else 0 
end) as JointInspection,(
case when
dbo.tblLoadtests.jobid is not null then 1
	else 0
end) as OurLoadTest, dbo.tblInsDetails.REInvoiceNumber

FROM         dbo.tblEquipmentType INNER JOIN
                      dbo.tblCertResults ON dbo.tblEquipmentType.EquipmentTypeID = dbo.tblCertResults.EquipmentTypeID INNER JOIN
                      dbo.tblGeneralEquipType ON dbo.tblCertResults.GeneralEquipTypeID = dbo.tblGeneralEquipType.GeneralEquipTypeID INNER JOIN
                      dbo.tblInsDetails ON dbo.tblCertResults.JobID = dbo.tblInsDetails.JobID INNER JOIN                      
                      dbo.tblBilling ON dbo.tblInsDetails.JobID = dbo.tblBilling.JobID INNER JOIN
                      dbo.tblInsTypes ON dbo.tblInsDetails.InsTypeID = dbo.tblInsTypes.InsTypeID INNER JOIN
                      
                      dbo.tblClients AS tblClients_1 ON dbo.tblInsDetails.OwnerID = tblClients_1.[Client ID] INNER JOIN
                      dbo.tblClients ON dbo.tblInsDetails.ConsigneeID = dbo.tblClients.[Client ID] left Join
					  dbo.tblCertStatus ON dbo.tblCertResults.ResultsID = dbo.tblCertStatus.ResultsID LEFT JOIN 
					  dbo.tblLoadTests ON dbo.tblInsDetails.JobID = dbo.tblLoadTests.JobID left Join
					  dbo.tblInvoiceSummary ON dbo.tblInsDetails.JobID = dbo.tblInvoiceSummary.JobID
where dbo.tblInsDetails.JobID in(

select dbo.tblInsDetails.JobID
--,InspectorID,InspectorID2,InspectorID3,InspectorID4,InspectorID5
from dbo.tblInsDetails
where dbo.tblInsDetails.InvoiceDate Between cast(@StartDate as datetime) And cast(@EndDate as datetime)  and (
(dbo.tblInsDetails.[InspectorID]  IN (Select InspectorID from @Engineers Where InspectorID <> 0)) and
(dbo.tblInsDetails.[InspectorID2] IN (Select InspectorID from @Engineers)) and
(dbo.tblInsDetails.[InspectorID3] IN (Select InspectorID from @Engineers)) and
(dbo.tblInsDetails.[InspectorID4] IN (Select InspectorID from @Engineers)) and
(dbo.tblInsDetails.[InspectorID5] IN (Select InspectorID from @Engineers)))

--I also need Jobs that will require that are invoiced and have been submitted for cert in that same period.
--but they can't be ONLY engineers

Union All

SELECT tblInsDetails.JobID
--, tblInsDetails.[WO#], tblInsDetails.InvoiceDate, tblCertStatus.DateSubmitted
FROM (tblCertStatus INNER JOIN tblCertResults ON tblCertStatus.ResultsID = tblCertResults.ResultsID) INNER JOIN tblInsDetails ON tblCertResults.JobID = tblInsDetails.JobID
WHERE (((tblCertStatus.DateSubmitted) Between cast(@StartDate as datetime) And cast(@EndDate as datetime)) AND ((tblCertStatus.DateSubmitted) Is Not Null))

union all

--need non-cert jobs that involve an engineer
select dbo.tblInsDetails.JobID
--,InspectorID,InspectorID2,InspectorID3,InspectorID4,InspectorID5
from dbo.tblInsDetails inner join dbo.tblInstypes on dbo.tblInsDetails.InsTypeID = dbo.tblInsTypes.InsTypeID
where dbo.tblInsDetails.InvoiceDate Between cast(@StartDate as datetime) And cast(@EndDate as datetime) and (
(dbo.tblInsDetails.[InspectorID] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) Or
(dbo.tblInsDetails.[InspectorID2] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID3] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID4] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID5] IN (Select InspectorID from @Engineers Where InspectorID <> 0))) And 
dbo.tblInsTypes.InsTypeName not like '%Eng Cert%'  And 
dbo.tblInsTypes.InsTypeName not like '%CAODC%'

union all

select dbo.tblInsDetails.JobID from dbo.tblInsDetails where dbo.tblInsDetails.IncludeInEngRevenue = 1

)
and not dbo.tblInsDetails.InvoiceNumber is null
and not dbo.tblInsDetails.JobID in (select dbo.tblInsDetails.JobID from dbo.tblInsDetails inner join dbo.tblEngRevenuePaid on dbo.tblInsDetails.JobID = dbo.tblEngRevenuePaid.JobID
where dbo.tblEngRevenuePaid.StartDate < cast(@StartDate as datetime) 
)
Order by dbo.tblInsDetails.InvoiceDate
END
GO

Select allOpen in new window

But you'd just NEVER do that as a passthrough.
It's akin to writing HTML in notepad.
You can do it, but there are IDEs designed and built to make the task much, much easier.
SSMS is the tool for writing and saving T-SQL to be used in conjunction with SQL Server.
Access then uses the resulting code.

We have posted working samples of how your case structure needs to be if you are using it in a passthrough.
You have posted no code samples at all.
Can we see what you are attempting?

The passthrough is also VERY particular about the little things
This works on my machine

SET NOCOUNT ON;
DECLARE @StartDate SMALLDATETIME = ( CASE WHEN DATEPART(WEEKDAY, GETDATE()) = 2 THEN GETDATE() - 3
                                          ELSE GETDATE() - 3
                                     END );									 
DECLARE @EndDate SMALLDATETIME = GETDATE();
Select  tblInsDetails.* from tblInsDetails where [Date] >= @StartDate  and [Date] <= @EndDate;

Select allOpen in new window

Forget a single semi-colon and it just returns no records.