Altering fields in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a question that I really have no idea how to resolve. I am working
on a database that I have read-only access to. The problem is is that I dont
think it was designed very well. There is a specific field that should
connect 2 tables. Unfortunately, in one of the tables this field contains a
dash(-) in it while the field in the other table does not. I cannot find any
other way to link these 2 tables. So my question is: How can I alter the
first value so that I can use it to link up to the value in the second table?
 
Can you give some examples of the values you want to use for linking?
Is the dash always in the same position in the data? For instance, the
third position?

Are you comfortable writing SQL statements in the SQL window or can you only
use the query grid?
 
The data looks like 000-000000 in one table and 0000000000 in the other
table. Also, some earlier data does not seem to have the dashes in either so
I would basically need to check the 4th position and see if it is a dash.
Then I would need to remove it if there is one. I can use SQL Statements but
Im fairly new at it.
 
Normally you would join doing something like the following.

SELECT *
FROM Table1 INNER JOIN Table2
ON Table1.TheField = Table2.TheField

In your case you need to do something along the lines of

SELECT *
FROM Table1 INNER JOIN Table2
ON Table1.TheField =
IIF(Mid(Table2.TheField,4,1)="-",Left(Table2.TheField,3) &
Mid(Table2.TheField,5),Table2.TheField)

One problem - your sample numbers, don't match up in the number of digits.
000-000000 (9 numeric characters)
0000000000 (10 numeric charactes)
Was that a typo? If not, then what goes into position 4?
 
Back
Top