Convert snippet of SQL to Access query.

  • Thread starter Thread starter Lurch
  • Start date Start date
L

Lurch

Hi All;

Using Access XP on Windows 2000 Pro..I am trying to create a query
which is based upon several linked tables. I cannot change any format,
field, etc of these tables as they are from different departments
where I work. I have an sql file that our IT dept created for
me...however I need to deploy this to my staff who are comfortable in
Access (my IT guy isn't familiar with Access)...ok so here goes....I
have a table where a field contains data such as "+1 +2 +3.2 +0". This
is not an expression. It is just the way the data is collected from
another department. I need to create a join to another table that may
store the data as "1, 2, 3.2, 0". This is collected from another
department...as I said I cannot change the formatting. The following
sql was used in order to create the join in SQL Server. I need to
translate it into something Access would understand. I know going to
sql server would be the easiest way to go but it isn't possible....

.....left join tblTable_1 on (table_1.Field1 = table_2.Field2 collate
AQL_Latin1_General_CP1_CI_AS and patindex('%+' + convert
(nvarchar,table_3.field_with_commas) + '%',
table_1.field_with_plus_signs) >0)

I hope that this makes sense. Is there any way to work around this in
Access?

Thank you all VERY much!!!
 
Please ignore my aweful spelling. I typed the original post
quickly...here is the sql...

left join tblTable_1 on (tbltable_1.Field1 = tbltable_2.Field2 collate
SQL_Latin1_General_CP1_CI_AS and patindex('%+' + convert
(nvarchar,tbltable_3.field_with_commas) + '%',
tbltable_1.field_with_plus_signs) >0)


I hope that this makes sense....
 
Back
Top