ms access to sql server query help!

[ChrisElias271]

Hi All,

I'm desperately trying to use an old access query and translate it to
be used in SQL enterprise manager.


The dataset i'm trying to update is too large for access (28 million
rows).


IIf([TableName]![FIELDNAME] Like "??0*",Left ([TableName]![FIELDNAME],
2) & Right([TableName]![FIELDNAME],4),IIf([TableName]![FIELDNAME]
Like
"?0*",Left([TableName]![FIELDNAME],1) & Right([TableName]!
[FIELDNAME],
4),[TableName]![FIELDNAME]))


This works to remove padding zeros in the middle of a postcode field.


eg


SR04 PQJ would be SR4 PQJ
or
S03 4PY would be S3 4PY.


It does this by finding all postcodes that begin with either ??0 or ?
0
and then recompiles them using concatenation.


This works fine in access but i can't translate it to be read in SQL
server. I'm aware of the differences in wildcard chars and the CONCAT
function but cannot seem to get it right.


Could someone please help!!

[bcap]

Re: ms access to sql server query help!

In SQL Server:

1. There is no such function as Iif, you need to use the CASE function.

2. The string delimiter is a single quote mark, not a double quote mark.

3. "*" is not a wildcard character, you need to use the "%" character.

4. The "!" character will not be recognised, use a full stop character.

5. The "&" operator does not exist, the nearest equivalent is the "+"
operator.

SQL Server 2000 Books Online can be downloaded for free:

http://www.microsoft.com/downloads/details.aspx?FamilyId=A6F79CB1-A420-445F-8A4B-BD77A7DA194B&displaylang=en

"ChrisElias 271" <chriselias271@ gmail.comwrote in message
news:520334b4-b3d8-4d72-b0b0-e3f28c4e4d57@v5 7g2000hse.googl egroups.com...

Hi All,
>
I'm desperately trying to use an old access query and translate it to
be used in SQL enterprise manager.
>
>
The dataset i'm trying to update is too large for access (28 million
rows).
>
>
IIf([TableName]![FIELDNAME] Like "??0*",Left ([TableName]![FIELDNAME],
2) & Right([TableName]![FIELDNAME],4),IIf([TableName]![FIELDNAME]
Like
"?0*",Left([TableName]![FIELDNAME],1) & Right([TableName]!
[FIELDNAME],
4),[TableName]![FIELDNAME]))
>
>
This works to remove padding zeros in the middle of a postcode field.
>
>
eg
>
>
SR04 PQJ would be SR4 PQJ
or
S03 4PY would be S3 4PY.
>
>
It does this by finding all postcodes that begin with either ??0 or ?
0
and then recompiles them using concatenation.
>
>
This works fine in access but i can't translate it to be read in SQL
server. I'm aware of the differences in wildcard chars and the CONCAT
function but cannot seem to get it right.
>
>
Could someone please help!!
>