Data Type Mismatch

Question

I used the expression below to pull an area code from a tabled phone number:

AreaCode: (Mid([dbo_accounts!HomePhone],(Len([dbo_accounts!HomePhone])-8)-1,3))

The above works, however, then I attempt a join the field with a US area code table, it tells me there's a data type mismatch. I tried formatting the above expression to output a number to match the US area code table, but it still doesn't like the join.

How can I get around this?

Answer 1

I took this issue a different direction. Since I can control 1 of 2 joined tables in the query, I changed the local table (field) to a short-text data type. Issue resolved.

Answer 2

Hi,

It's a short text field in the DB.

Answer 3

AreaCode: (Mid([dbo_accounts!HomePhone],(Len([dbo_accounts!HomePhone])-8)-1,3))

The above works, however, then I attempt a join the field with a US area code table, it tells me there's a data type mismatch.

Hi shubox56,

What is the type of the area code in the code table: Text, Integer, Long, ...

You could use the functions CInt or CLng to convert Text to Integer or Long.

"Numbers" like the area code, telephone-numbers, zip-codes, assurance-numbers, etc., that are not used for any calculation but only for identifying, I always define them as Text/String.

In that case I would download the table, and convert the area code to Text.

Imb.

Answer 4

Hi Ken,

I tried Val earlier without luck. Data type mismatch.

Answer 5

Try:

AreaCode: Val(Mid([dbo_accounts!HomePhone],(Len([dbo_accounts!HomePhone])-8)-1,3))