Type mismatch in JOIN expression. (Error 3615) Join data stored asText and Number

  • Thread starter Thread starter excelCPA
  • Start date Start date
E

excelCPA

I am attempting to have a query from two separate linked tables. Both
fields I am trying to join are all numbers, however Table1 has this
stored as Number, the other has this stored as Text. I can't change
the properties of the linked tables since I don't own them.


I tried converting the tables stored as text in the join as follows
with no luck:
([Table1] INNER JOIN Tables2 ON [Table1].Policy_Number = val
(Table2.PolicyNum))

Any ideas on how to join these tables in the query? Thanks.
 
Unless it's just a typo below, you have Tables2 and Table2 in your SQL. That
little s could make all the difference.
 
Unless it's just a typo below, you have Tables2 and Table2 in your SQL. That
little s could make all the difference.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.



excelCPA said:
I am attempting to have a query from two separate linked tables.  Both
fields I am trying to join are all numbers, however Table1 has this
stored as Number, the other has this stored as Text.  I can't change
the properties of the linked tables since I don't own them.
Click to expand...
I tried converting the tables stored as text in the join as follows
with no luck:
([Table1] INNER JOIN Tables2 ON [Table1].Policy_Number = val
(Table2.PolicyNum))
Click to expand...
Any ideas on how to join these tables in the query?  Thanks.
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

That is a typo. the SQL is ([Table1] INNER JOIN Table2 ON
[Table1].Policy_Number = val(Table2.PolicyNum))
 
Typo? CPA? :-) My son is a CPA.

Try something a little different with the SQL and how the fields are joined.

FROM [Table1], [Table2]
WHERE [Table1].Policy_Number = Val(Table2.PolicyNum)

One second! What kind of tables are they linked to? It's possible that you
can't run a function like VAL against it if not Access.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


excelCPA said:
Unless it's just a typo below, you have Tables2 and Table2 in your SQL. That
little s could make all the difference.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.



excelCPA said:
I am attempting to have a query from two separate linked tables. Both
fields I am trying to join are all numbers, however Table1 has this
stored as Number, the other has this stored as Text. I can't change
the properties of the linked tables since I don't own them.
Click to expand...
I tried converting the tables stored as text in the join as follows
with no luck:
([Table1] INNER JOIN Tables2 ON [Table1].Policy_Number = val
(Table2.PolicyNum))
Click to expand...
Any ideas on how to join these tables in the query? Thanks.
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

That is a typo. the SQL is ([Table1] INNER JOIN Table2 ON
[Table1].Policy_Number = val(Table2.PolicyNum))
.
Click to expand...
 
IF Table2.PolicyNum is ever NULL Val(Table2.PolicyNum) is going to generate an
error. That will give you an error when you attempt to run the query. SO you
might try
Val(Table2.PolicyNum,"0")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top