When specifying query criteria, be careful to avoid the following constructs. Reformulating queries containing problematic criteria as described below will help Oracle to optimize queries for improved performance.
1. Use Between instead of arithmetical comparisons:
Example1: [Field] >= #1/1/2000# And <= #12/31/2000#
Reformulation: [Field] Between #1/1/2000# And #12/31/2000#
Example2: [Field1] > 10 And <= 20
Reformation: [Field1] Between 11 And 20
2. Do not make "not equal" (<> or !=) comparisons.
Example: [Field1] <> "Fred"
Reformulation: Not [Field1] = "Fred"
3. Assign data types to all parameters.
4. Avoid using Format( whenever possible. For improved DBMS performance, use the front-end application to format data.
5. Use explicit data type conversions rather than relying on MS-Access implicit conversions. For example, "Qtr " & DatePart("q",Date()), though valid in MS-Access, will result in a SQL Server data conversion error. Use "Qtr" & CStr(DatePart("q",Date())) to avoid conversion errors.
6. Use functionally equivalent DatePart( statements instead of Format(. Ex. DatePart( "q", Date) instead of Format( Date, "q").
7. When queries use more than one table with like named fields, specify aliases for fields with the same underlying names. For example, in the Northwind sample database, the Invoices query refers to CompanyName for both the Customers and Shippers tables. This results in a Duplicate Columns error when converting the query to SQL Server.