SAGE ODBC Data Type help!

[neil.hampshire]

Hi,
Here goes....!

I'm Just wondering if it is possible to change that data type within the 'SALES_ORDER' table or any others for that matter!

The field i would like to change is: 'ORDER_NUMBER' (found in the SALES_ORDER table) from a 'Number' data type to a 'Text' data type


If this is possible i can link the 'Reference' field within the 'STOCK_TRAN' table (which is a 'Text' data type ) to 'ORDER_NUMBER' so i can show transaction history for a certain part within an Access Form for our sales guys!

Phew that was a mouthfull!! if i need to explain some more, do not hesitate to ask!

Thanks in advance!

Neil

[neil.hampshire]

update: i forgot to mention that we use Sage Line 50 v11

[brucedenney]

You can not change data types.

If you want to link tables they need to be the same type. So I guess you could link the Stock transactions to Sales orders. I guess you would filter by the Customer so you could see all products on prior orders.

Of course you could do that using the Sales order and SOP Item tables anyway.

If you are working in Access then ODBC is slow. I suggest caching the tables in Access. As the reference on a product transaction could be an Invoice or a SOP you could get wrong links. I would pull all the invoice items on invoices not made from SOPS and all the SOP items and build a cached "products sold table" in Access and use it for the lookup.

If you link the tables to Access or Excel you can use a function to change the datatype and so link the tables externally, however, then your report would have to be done in Excel or Access...