Syntax Variations

[stanl]

As this forum has set idle a bit, I would like to suggest a continuing discussion of SQL syntax variations between db's, providers etc...

For example, a common gotcha is the use of * as substitution in Access queries, which must be converted to % when issuing those queries via ADO.

Having recently worked with Access and SQL Server 2005 I have found

Access: 'Select ... WHERE [var]="value"'
SQLServer: "Select ... WHERE [var]='value'"'

Access: Select ... WHERE len(trim([var]))=value
SQLServer: Select ... WHERE len([var])=value

Access: Select .... WHERE ucase([var])=VALUE
SQLServer: Select ... WHERE [var]=VALUE 'or value or Value

trivial examples, but nice to know when upsizing code.

Stan

[XLGibbs]

The Access wizard provides a unique slant on syntax variations. Essentially it is the same conceptually.

SQL "can" be set to use " instead of ' but it is not the norm.

Most SQL engines including Access will automatically convert text to UPPER for the comparison.

SQL also can require the use of the RTRIM and LTRIM functions, depending on whether or not the proper field type for text fields was used, and if trailing /leading spaces were inadvertantdly included in the datasets.

SQL is basically SQL in all cases. Big thing is that Access supports IIF (Immediate IF), but SQL Server uses the CASE WHEN ELSE END construct.