Need Help Converting Access Query to TSQL

[rodeomount]

I need to convert the following Access query to TSQL. The only problem is that I don't have any experience with a query like this. Any help is deeply appreciated.



(((IIf([tbl0000Tracking]![AvailableScanDate] Is Not Null,CStr(Format([tbl0000Tracking]![AvailableScanDate],"mm/dd/yyyy")),Null)) Is Not Null)
AND ((WorkDaysDiff([tbl0000Tracking]![AvailableScanDate],Now()))>0)
AND ((Left([tbl0000Shipments]![Carrier],3))="UPS") AND ((tbl0000Tracking.DelvScanDate) Is Null)
AND ((tbl0000Tracking_2.TrackingNumber) Is Null) AND ((tbl0000Tracking_1.TrackingNumber) Is Null)
AND ((tbl0000Shipments.AbandonedRptSent) Is Null));

 

[ca8msm]

Have a look at the three following functions. You should be able to use any of these functions to replace your existing IIF function:

COALESCE
CASE
ISNULL

To get the current date and time, you can use GetDate instead of your Now method.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[rodeomount]

How does "WorkDaysDiff" work? I'm assuming the following:

WorkDaysDiff(A,B)

Is equivalent to A - B

 

[ca8msm]

I think WorkDaysDiff will be a user written function rather than a built-in Access function. ave a look through the Access database to find out. If it is, you will have to write a similar function in SQL Server.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]