Relationship between two Tables. Field type mismatch. Integer and Text. ERROR

[jasabel]

I am querying a SQL Server and I need to configure a relationship between two tables BUT their fields are different in Type. Although both hold numbers, one of them is an integer an the other is stored in a text record.Obviously I get a type mismatch error.

I suspect I have to convert the text to integer first I ignore the function.

Thanks a lot

[ItsMe]

Have you tried using a Query Object in Access to create the JOIN? Why are you mentioning a function, what function did you create/use?

If you must create a 'Relationship', you will likely need a temp table on the client. Otherwise, I would expect Access to Cast the Integer or Cast the Text.

[jasabel]

Have you tried using a Query Object in Access to create the JOIN? Why are you mentioning a function, what function did you create/use?

If you must create a 'Relationship', you will likely need a temp table on the client. Otherwise, I would expect Access to Cast the Integer or Cast the Text.

Well, I have tried to make a intermediate query to change the text to a number using the function FormatNumber (field,0) and then link this query to the table with the integer field, but I get an mismatch error.

I am using the query designer all the time, its easy to it in plain SQL and passtrough it but I want to avoid this option.

regards

[June7]

FormatNumber (as does Format) function still results in a text value. Try number conversion functions: Val(), CInt(), CDbl(). Issue with them is they will error if field is Null.

[jasabel]

I have found the solution here.... http://p2p.wrox.com/sql-language/121...rt-access.html

[jasabel]

Hey June7.
You are right, I have sorted it using the Val function.
Thanks a lot

[jasabel]

Ohhh no I have found another mismatch error using text fields. The thing is that I am linking tables in access from different databases

[ItsMe]

You need to determine where your query is running. If you build a query that is not a pass-through query (no globe icon in the Navigation Pane) you can use functions intrinsic to Access. For instance, you can cast a Number type to Text type.
https://msdn.microsoft.com/en-us/lib.../gg278896.aspx

You seldom need to use theses functions because Access does a pretty good job managing implicit casting. However, when linking to other types of back ends, it is more important to be explicit with data types.