deleting a spacebar

[damcloodt]

Hello,

I have to link two tables with each other. In table 1 I have a column with codes (EP678686) that I want to link with a column in table2, however in table 2 the codes include spacebars (EP 678686). In order to be able to linke the two tables with each other, I first want to delete the spacebar between EP and 678686 in table 2. Does anyone know how to do this, by using a "make table query"??

Thanks a lot!

 

[MSAccessRookie]

Hello,

I have to link two tables with each other. In table 1 I have a column with codes (EP678686) that I want to link with a column in table2, however in table 2 the codes include spacebars (EP 678686). In order to be able to linke the two tables with each other, I first want to delete the spacebar between EP and 678686 in table 2. Does anyone know how to do this, by using a "make table query"??

Thanks a lot!

If the Columns to link are EXACTLY the same except for a SINGLE space, then the following might do what you want:

Table1.ColumnToLink=Replace(Table2.ColumnToLink, " ", "")

Of Course, if Table2.ColumnToLink can have more than one space, this might not work the way that you want.

 

[damcloodt]

Thanks, but where should I put the code? I normally work with query in design view?

 

[damcloodt]

If the Columns to link are EXACTLY the same except for a SINGLE space, then the following might do what you want:

Table1.ColumnToLink=Replace(Table2.ColumnToLink, " ", "")

Of Course, if Table2.ColumnToLink can have more than one space, this might not work the way that you want.

Thanks, but where should I put the code? I normally work with query in design view?

 

[MSAccessRookie]

Thanks, but where should I put the code? I normally work with query in design view?

In design view, this amounts to linking Table1 to Table2, and then choosing Table1.ColumnToLink, and setting its criteria to =Replace(Table2.ColumnToLink, " ", "")

 

[damcloodt]

This does not work, I get an error. should , be ; maybe? I tried this, but still another error: data type mismatch in criteria expression?? Anyone who can help me?

 

[damcloodt]

Maybe it is easier for me to just delete the spacebar (3rd digit)? Is there a query that can delete a spacebar? (so that EP 678686 becomes EP678686)?

Thanks a lot!!

 

[DCrake]

Create a new query and bring in table 2

In your first column enter

T2:Replace([YourFieldName]," ","")

Then bring down the additional fields in your table into the query. Then save the query as say QryT2NoSpaces

Next create another new query and bring in Table 1 and the query QryT2NoSpaces and create a join between the table and the query using the field in table 1 to T2 in your query.

This should now work.

David