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

[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.

 

[Jerry Whittle]

Unless it's just a typo below, you have Tables2 and Table2 in your SQL. That
little s could make all the difference.

 

[excelCPA]

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.

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.
.- 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]

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.

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.

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.
.- Hide quoted text -

- Show quoted text -

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

 

[John Spencer]

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