Join Tables with differing Dates

[sberti]

I'm using data from 2 external sources. The data in both tables is Monthly data.



Table 1 uses the 1st day of the month as the Date
Table 2 uses the last day of the month as the Date

In each table, I have concatenated the Dates for both like this:
Month: DatePart("yyyy",[TableDate]) & "-" & DatePart("m",[TableDate])
An example of the result is: 2012-11 (yyyy-mm)

I can join the tables on these fields. So that works, ok.

BUT I have a Third table that has data entered manually.
In it, I have a field called YearMonth formatted as Text.
When I enter 2012-11 in this field and try to join this field to either of the other tables, it doesn't work. It does not pull the data and for some reason the concatenated field displays the dash (-) only, no numbers.

Can anyone help me join this Third table to either of the others?

[orange]

You could try to make a new field in that 3rd table (untested)

TheDatetoUse data type Date/Time

fill it with
Dateserial(Left(YearMonth,4), Mid(YearMonth,instr(YearMonth,"-"),1)

Then use your DatePart approach to get the year and month for the join

Just trying to help.

[sberti]

Thanks for your suggestions.