IIf ..then...case...switch - I'm not sure

[dchristo]

I am creating a pass thru query in SQL

SELECT
dbo.flat_master1.id,dbo.flat_master1.filercvd,dbo.flat_master1.claim_type,dbo.flat_master2.saledate,dbo.master2.deliverdate,,dbo.master2.state,IIf([claim_type]
in('bike'),dbo.master2.saledate +60,IIf([Claim_type] Not in
('bike'),dbo.mistat2.deliverdate + 60)) AS Projected Date
From (dbo.flat_master1 inner join dbo.master2 on dbo.flat_master1.muniq_id =
dbo.master2.uniq_id)

I cannot get the iif statement to work.

Any help would be appreciated.

 

[Stefan Hoffmann]

hi,

I am creating a pass thru query in SQL

SELECT
dbo.flat_master1.id,dbo.flat_master1.filercvd,dbo.flat_master1.claim_type,dbo.flat_master2.saledate,dbo.master2.deliverdate,,dbo.master2.state,IIf([claim_type]
in('bike'),dbo.master2.saledate +60,IIf([Claim_type] Not in
('bike'),dbo.mistat2.deliverdate + 60)) AS Projected Date
From (dbo.flat_master1 inner join dbo.master2 on dbo.flat_master1.muniq_id =
dbo.master2.uniq_id)

I cannot get the iif statement to work.

You need to rewrite the IIF(), if the destination SQL dialect does not
support it.

If it's T-SQL you may use

CASE [Claim_type] = 'bike'
THEN 0
ELSE [deliverdate] + 60
END AS [Projected Date]


mfG
--> stefan <--

 

[Jerry Whittle]

I see four things:

1. After dbo.master2.deliverdate there seems to be two commas ,, .

2. I'm not sure that IIf's will work in a pass-thru.

3. "ondbo.mistat2.deliverdate" That looks like a typo as there is an extra
period and the table name doesn't match anythiing.

4. Looks like your second IIf is missing an argument. It doesn't what to do
if it isn't in 'bike'.
Actually the statement below is simpler and should do the job.

SELECT
dbo.flat_master1.id,
dbo.flat_master1.filercvd,
dbo.flat_master1.claim_type,
dbo.flat_master2.saledate,
dbo.master2.deliverdate,
dbo.master2.state,
IIf([claim_type] = 'bike', dbo.master2.saledate +60,
dbo.mistat2.deliverdate + 60) AS Projected Date
From (dbo.flat_master1 inner join dbo.master2
on dbo.flat_master1.muniq_id = dbo.master2.uniq_id) ;

Another problem just might be with the single quotes. Some databases work
differently with single or double quotes.

 

[Jerry Whittle]

Looks like I made a typo. Try this:

SELECT
dbo.flat_master1.id,
dbo.flat_master1.filercvd,
dbo.flat_master1.claim_type,
dbo.flat_master2.saledate,
dbo.master2.deliverdate,
dbo.master2.state,
IIf([claim_type] = 'bike', dbo.master2.saledate +60, dbo.master2.deliverdate
+ 60) AS Projected Date
From (dbo.flat_master1 inner join dbo.master2
on dbo.flat_master1.muniq_id = dbo.master2.uniq_id) ;

 

[dchristo]

Thank you for replying. I keep getting incorrect syntax near '='. (#170)

Looks like I made a typo. Try this:

SELECT
dbo.flat_master1.id,
dbo.flat_master1.filercvd,
dbo.flat_master1.claim_type,
dbo.flat_master2.saledate,
dbo.master2.deliverdate,
dbo.master2.state,
IIf([claim_type] = 'bike', dbo.master2.saledate +60, dbo.master2.deliverdate
+ 60) AS Projected Date
From (dbo.flat_master1 inner join dbo.master2
on dbo.flat_master1.muniq_id = dbo.master2.uniq_id) ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am creating a pass thru query in SQL

SELECT
dbo.flat_master1.id,dbo.flat_master1.filercvd,dbo.flat_master1.claim_type,dbo.flat_master2.saledate,dbo.master2.deliverdate,,dbo.master2.state,IIf([claim_type]
in('bike'),dbo.master2.saledate +60,IIf([Claim_type] Not in
('bike'),dbo.mistat2.deliverdate + 60)) AS Projected Date
From (dbo.flat_master1 inner join dbo.master2 on dbo.flat_master1.muniq_id =
dbo.master2.uniq_id)

I cannot get the iif statement to work.

Any help would be appreciated.

 

[John W. Vinson]

Thank you for replying. I keep getting incorrect syntax near '='. (#170)

That's because IIF() is a builtin Access query engine function which is
unknown and unavailable to T/SQL (or to PL-SQL or to DB2 or the other database
engines to which you might be issuing passthrough queries... you haven't
said).

 

[dchristo]

The SQL Pass-Through query in Access

That's because IIF() is a builtin Access query engine function which is
unknown and unavailable to T/SQL (or to PL-SQL or to DB2 or the other database
engines to which you might be issuing passthrough queries... you haven't
said).

 

[Daryl S]

Try changing your AS from Projected Date to Projected_Date.

--
Daryl S

Thank you for replying. I keep getting incorrect syntax near '='. (#170)

Looks like I made a typo. Try this:

SELECT
dbo.flat_master1.id,
dbo.flat_master1.filercvd,
dbo.flat_master1.claim_type,
dbo.flat_master2.saledate,
dbo.master2.deliverdate,
dbo.master2.state,
IIf([claim_type] = 'bike', dbo.master2.saledate +60, dbo.master2.deliverdate
+ 60) AS Projected Date
From (dbo.flat_master1 inner join dbo.master2
on dbo.flat_master1.muniq_id = dbo.master2.uniq_id) ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am creating a pass thru query in SQL

SELECT
dbo.flat_master1.id,dbo.flat_master1.filercvd,dbo.flat_master1.claim_type,dbo.flat_master2.saledate,dbo.master2.deliverdate,,dbo.master2.state,IIf([claim_type]
in('bike'),dbo.master2.saledate +60,IIf([Claim_type] Not in
('bike'),dbo.mistat2.deliverdate + 60)) AS Projected Date
From (dbo.flat_master1 inner join dbo.master2 on dbo.flat_master1.muniq_id =
dbo.master2.uniq_id)

I cannot get the iif statement to work.

Any help would be appreciated.

 

[John W. Vinson]

The SQL Pass-Through query in Access

Yes, but it is being *EXECUTED* by SQL/Server or some other program. That's
what a pass through means - it is "passed through" Access to some *OTHER*
server program which in turn executes it. Access does not parse it or
understand it or evaluate it at all - it passes it through, verbatim, to the
server.

If the server sees something (such as IIF) which it doesn't recognize, it will
generate an error and return that error to your program.