Data Type Issue

[The Rook]

I currently have a database the has linked ODBC Tables. Two of the fields
from different tables that I am trying to join have differnt data types, 1
has text the other has number.

When I link join the fields and run the query I get the messgae 'typer
mismatch in expression' if I try to amend the data type through the design
aspect of tables I get the message 'Can not save propoties of linked tables'

Is there a way around this?

 

[Kevin B]

You need to open the database file that the linked tables reside in and make
your table structure changes there.

 

[The Rook]

Unfortunately I am unable to do that as it is read-only

 

[Ken Sheridan]

Call the VAL function in the join expression in your query to return the text
values as numbers e.g.

FROM Table1 INNER JOIN Table2
ON Table1.SomeColumn = VAL(Table2.SomeColumn)

Ken Sheridan
Stafford, England

 

[The Rook]

Thanks for your reply, but when it comes to SQL I am clueless.

I have pasted the SQL for the query below, if you wouldn't mine helping me out

I am wanting the data type of o_STKDETAIL.BatchWorks_Order to be TEXT:


SELECT dbo_STKDETAIL.Part_Number, dbo_INVDET.Qty, dbo_INVDET.Unit_Price
FROM dbo_STKDETAIL INNER JOIN dbo_INVDET ON dbo_STKDETAIL.BatchWorks_Order =
dbo_INVDET.Works_Order_No
WHERE (((dbo_STKDETAIL.Location)="FG") AND ((dbo_STKDETAIL.Ex_Job_No)="DEL"))


Thanks in advance

Regards

 

[KARL DEWEY]

Try this --
SELECT dbo_STKDETAIL.Part_Number, dbo_INVDET.Qty, dbo_INVDET.Unit_Price
FROM dbo_STKDETAIL INNER JOIN dbo_INVDET ON
Val(dbo_STKDETAIL.BatchWorks_Order) = dbo_INVDET.Works_Order_No
WHERE (((dbo_STKDETAIL.Location)="FG") AND ((dbo_STKDETAIL.Ex_Job_No ="DEL"))

You will not be able to view in design view after this.

Another way is to use a query with the VAL function instead of the table
directly.