Linking Tables with Dates

[scottmarkle]

Hi everyone,

I'm a relative n00b to access so please be gentle

I'm trying to link two tables together. In order to get aa unique field
from each table I need to concatenate three fields. Two of the fields are
text, the third is a date (i.e. Field 1 - C111; Field 2- H666, Field 3 -
1/6/2008, Created Unique Field - C111H6661/29/2008)
The problem I am running into is that one table is a text file generated
from SAP R3 and the date field displays the zeros in the months and days
(01/06/2008) and the other table is is a BW extract that the date appears
without zeros (1/6/2008). So when I concatenate they don't match because of
the zeros.
I have both of the tables linked so unfortunately I can't change the formats
in the tables themselves. Does anyone know how I can change the tables so
that the date formats are the same and my concatenate would work? I hope I
made this clear enough, but let me know if you need further details.

Thanks,
Scott

 

[wphx]

Are you able to import the text file as a table? As part of the import
wizard you will be able to identify the date column as a date-data type -
this will convert the data into the data format ans should be compatible
with the date data in your other table. You can then use several joins in
your query to connect the dates and the other gear.

one tings that might be interesting to try, is to create two separate
queries - one for each table. Inside the query create your unique key (you
can use the format() function to create a common date format. Then create a
new query and see if you can join the generated fields in the previous
queries?


inside query:

Newformat: field1 & field2 & format(field3,"yyyymmdd")

???