Type mismatch on a join

[philfer]

Hi,

I am importing a text file into a table.

The table has one field as the text file is a series of strings.

I then use Left and Mid on the string to split it up into the fields it includes in the string.

I then (in a query) try to join one of these fields (maturity date) to a field in another table but it wont let me run the query as it says "join - type mismatch".

When I split the string I cast it as a date with CDate(Mid......

And in the other table the field is formatted as a date.

Does anyone knwo why I am still getting this error?

I have formatted them both as short date to try to ensure they are the same.


I then tried to the same thing with the "Amount" field. I used the Value function and tried to cast it as a double and as an int separately. I also used the properties sheet to set the format type and decimal places.

In the other table it was already a number field and I set it to the same format and decimal places. But it still wont let me join it even though the number look the same to me

Any help would be appreciated as its driving me up the wall!!!!!

Cheers
Phil

[philfer]

I meant Val not Value!

[OBP]

The date field I can understand, as it is possible to format a field to look like a date, but the data may not be stored as a real date, which is stored as a number.
Without seeing the data it is hard to say what is wrong.

[philfer]

The original data is one long string so I am guessing when I split it its still a series of strings.

I then try to cast it using CDate/ CDbl hoping this will change the datatype.

Its still not working.........wish I could send the data but its work stuff and they are paranoid about letting stuff go out the office (as you would expect)

[OBP]

The Data Type is controlled by the data type in the Table, you can't force Access to make a string a Query, but when you put data in a Date field Access recognises lot's of different formats. So try changing the Date field to a Date Type, if you get any errors you will then know that Access couldn't recognise it as a Date.

[philfer]

Thanks for that OBP.

I am importing the file into a table with one field called "Data" of type text.

I then use a query to split it by using the mid function as a field expression.

In the query design view if I hit F4 and pull the format combo down it is blank.

When I use CDate the date formats appear.

Do you think, given your previous post, that it would be better to use a make table query and have the field as a Date type in the destination table.

Thanks for all your help
Phil

[Kicker]

I agree with OBP. When you say


When I use CDate the date formats appear.

it tells me that you are "displaying" the date format but the data itself is still text. If you are using a Query, use something like

Expr1: CDate(mid(....))

That will actually put a date value into the field and that field can be used to relate to another date field and for date calculations.

Hope this helped.

[OBP]

Phil, it might help if you post some dummy data in the table and show us the query so that we can see what is happening.