By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,360 Members | 2,961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,360 IT Pros & Developers. It's quick & easy.

ms access to sql server query help!

P: n/a
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!!

Aug 11 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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/d...displaylang=en
"ChrisElias271" <ch***********@gmail.comwrote in message
news:52**********************************@v57g2000 hse.googlegroups.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!!

Aug 11 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.