How would this access code work as a pass through query?

[texaschili]

I have an If statement and also I need to adjust these dates but when I convert the query to a pass thru it doesn't understand the code.

IIf(tblCustomersBilling!RecordID Is Null,"",CLng(tblCustomersBilling!RecordID)) AS CBRID, IIf(Right(Left(tblCustomers!DateRx,2),1) Like "/",Left(tblCustomers!DateRx,2),Left(tblCustomers!DateRx,2)+"/")+Right(tblCustomers!DateRx,4) AS MnthYrRXFl1

[pbaldy]

Pass through to what? SQL Server wouldn't understand it, as it has no IIf() function. The usual replacement for that in a pass through query is a CASE statement. I don't think it will recognize the CLng() function either; you'd use CAST or CONVERT for that.

[texaschili]

Pass through to what? SQL Server wouldn't understand it, as it has no IIf() function. The usual replacement for that in a pass through query is a CASE statement. I don't think it will recognize the CLng() function either; you'd use CAST or CONVERT for that.

Ok. Would this work:

  CASE tblCustomersBilling.RecordID WHEN IS NULL
              THEN CAST tblCustomersBilling.RecordID AS CBRID

[pbaldy]

The syntax for the CAST:

http://msdn.microsoft.com/en-us/libr...sql.80%29.aspx

and CASE:

http://msdn.microsoft.com/en-us/libr...SQL.80%29.aspx

The "searched" syntax is typically used to replace the IIf():

CASE WHEN tblCustomersBilling.RecordID IS NULL THEN...

Note that in Access you can use the Nz() function to replace Null values; the SQL Server equivalent is the ISNULL() function.