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

440 views
Skip to first unread message

excelCPA

unread,
Nov 12, 2009, 11:12:23 AM11/12/09
to
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.

Jerry Whittle

unread,
Nov 12, 2009, 12:14:04 PM11/12/09
to
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" wrote:

> .
>

excelCPA

unread,
Nov 12, 2009, 12:26:08 PM11/12/09
to
On Nov 12, 12:14 pm, Jerry Whittle
> > .- Hide quoted text -
>
> - Show quoted text -

That is a typo. the SQL is ([Table1] INNER JOIN Table2 ON
[Table1].Policy_Number = val(Table2.PolicyNum))

Jerry Whittle

unread,
Nov 12, 2009, 2:12:03 PM11/12/09
to
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.


"excelCPA" wrote:

> .
>

John Spencer

unread,
Nov 13, 2009, 7:47:19 AM11/13/09
to
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

Reply all
Reply to author
Forward
0 new messages