converting access queries to SQL queries

[SpiderFlight]

I have a project which includes converting access queries to SQL queries. Is there software what will do the conversion? Below is an access where clause I need to convert to SQL.

WHERE
(((weekofyr([TblEShareData].[CHECKIN_DT_TM])) Not Between #1/1/2004# And #12/31/2004#)
AND ((TblEShareData.E_ENCNTR_TYPE_DISP) Is Not Null)
AND ((IIf([TblEShareData]![TRIAGE_START] Is Null,10000,DateDiff("n",[TblEShareData].[CHECKIN_DT_TM],[TRIAGE_START])))>=-10

Or (IIf([TblEShareData]![TRIAGE_START] Is Null,10000,DateDiff("n",[TblEShareData].[CHECKIN_DT_TM],[TRIAGE_START]))) Is Null)
AND ((IIf([TblEShareData]![BED_ASSIGN] Is Null,10000,DateDiff("n",[TblEShareData].[CHECKIN_DT_TM],[BED_ASSIGN])))>=-10

Or (IIf([TblEShareData]![BED_ASSIGN] Is Null,10000,DateDiff("n",[TblEShareData].[CHECKIN_DT_TM],[BED_ASSIGN]))) Is Null)
AND ((IIf([TblEShareData]![TRIAGE_CMPLT] Is Null,10000,DateDiff("n",[TblEshareData].[CHECKIN_DT_TM],[TRIAGE_CMPLT])))>=-10

Or (IIf([TblEShareData]![TRIAGE_CMPLT] Is Null,10000,DateDiff("n",[TblEshareData].[CHECKIN_DT_TM],[TRIAGE_CMPLT]))) Is Null)
AND ((TblEShareData.BED_ASSIGN) Is Not Null))

OR (((weekofyr([TblEShareData].[CHECKIN_DT_TM])) Not Between #1/1/2004# And #12/31/2004#)
AND ((TblEShareData.E_ENCNTR_TYPE_DISP) Is Not Null)
AND ((IIf([TblEShareData]![TRIAGE_START] Is Null,10000,DateDiff("n",[TblEShareData].[CHECKIN_DT_TM],[TRIAGE_START])))>=-10

Or (IIf([TblEShareData]![TRIAGE_START] Is Null,10000,DateDiff("n",[TblEShareData].[CHECKIN_DT_TM],[TRIAGE_START]))) Is Null)
AND ((IIf([TblEShareData]![BED_ASSIGN] Is Null,10000,DateDiff("n",[TblEShareData].[CHECKIN_DT_TM],[BED_ASSIGN])))>=-10

Or (IIf([TblEShareData]![BED_ASSIGN] Is Null,10000,DateDiff("n",[TblEShareData].[CHECKIN_DT_TM],[BED_ASSIGN]))) Is Null)
AND ((IIf([TblEShareData]![TRIAGE_CMPLT] Is Null,10000,DateDiff("n",[TblEshareData].[CHECKIN_DT_TM],[TRIAGE_CMPLT])))>=-10

Or (IIf([TblEShareData]![TRIAGE_CMPLT] Is Null,10000,DateDiff("n",[TblEshareData].[CHECKIN_DT_TM],[TRIAGE_CMPLT]))) Is Null)
AND ((TblEShareData.TRIAGE_START) Is Not Null))

OR (((weekofyr([TblEShareData].[CHECKIN_DT_TM])) Not Between #1/1/2004# And #12/31/2004#)
AND ((TblEShareData.E_ENCNTR_TYPE_DISP) Is Not Null)
AND ((IIf([TblEShareData]![TRIAGE_START] Is Null,10000,DateDiff("n",[TblEShareData].[CHECKIN_DT_TM],[TRIAGE_START])))>=-10

Or (IIf([TblEShareData]![TRIAGE_START] Is Null,10000,DateDiff("n",[TblEShareData].[CHECKIN_DT_TM],[TRIAGE_START]))) Is Null)
AND ((IIf([TblEShareData]![BED_ASSIGN] Is Null,10000,DateDiff("n",[TblEShareData].[CHECKIN_DT_TM],[BED_ASSIGN])))>=-10

Or (IIf([TblEShareData]![BED_ASSIGN] Is Null,10000,DateDiff("n",[TblEShareData].[CHECKIN_DT_TM],[BED_ASSIGN]))) Is Null)
AND ((IIf([TblEShareData]![TRIAGE_CMPLT] Is Null,10000,DateDiff("n",[TblEshareData].[CHECKIN_DT_TM],[TRIAGE_CMPLT])))>=-10

Or (IIf([TblEShareData]![TRIAGE_CMPLT] Is Null,10000,DateDiff("n",[TblEshareData].[CHECKIN_DT_TM],[TRIAGE_CMPLT]))) Is Null)
AND ((TblEShareData.TRIAGE_CMPLT) Is Not Null));

Thanks for the assistance.

 

[JamesLean]

Couple of starters:

- Delimit date values with single quotes rather than #s.
- Don't use ! to qualify table names, just use dots.
- SQL Server does not have IIf function, you need to use a CASE expression:

SELECT CASE WHEN x = y THEN expr1 ELSE expr2 END

--James

 

[cheerio]

I am not aware of any automated tools. I would add to the previous post:

- Access Nz function has to be changed to IsNull (although I did not see any use in your example)

- Replace double quotes with single quotes (the need for this depends on Server settings but it will do no harm)

- Datediff syntax is slightly different (Access wants the period flag in quotes and SQL Server does not use the quotes)

- Access tends to put in far more brackets than are really needed

- Generally functions will be different eg Access Now() function equates to SQL GETDATE()

- Generally it is not necessary to put field names in square brackets unless those names are reserved words although square brackets will do no harm

- finally if you are selecting from just one table you do not need to qualify every field with the table name