Data Type Issue

  • Thread starter Thread starter The Rook
  • Start date Start date
T

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?
 
You need to open the database file that the linked tables reside in and make
your table structure changes there.
 
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
 
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
 
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.
 
Back
Top