changing data type in linked tables (2002 – XP)

Topic

New Reply

I have a Palm Pilot that creates an Access ODBC link. This link has all the data fields as Text in the data type. When I try to link a table in my inventory database to this Palm created table none of my queries work correctly as to math functions. For example in one query I subtract field2 from field3 and then in the criteria the expression >=field7 so that the query only returns data that needs to be reordered.

Now my query seems to return anything that is not at zero sum. In another way, negative numbers are apparently viewed by Access as greater than field7 as well as positive numbers.

I have tried changing the data type in the linked table and Access tells me that this cannot be changed. Is there a solution to this problem?

TIA

Bret

Reply | Quote

Replies

[quite]I have a Palm Pilot that creates an Access ODBC link. [/quote] Could you expand on that a bit, please? Access doesn’t run on the Palm, so what application is creating the table you’re linking to and is that table still on the Palm or are you linking to a PC version of it synced to the Palm? Linked tables can’t be modified from Access, although data can be changed if Access knows what kind of table it is and the necessary indexes, etc., are in place. You can’t change the datatype of even a linked Access table from the front end.

Reply | Quote

Thanks for replying Charlotte. The application on the Palm is called Tracer Plus. It creates a database that is then exported to the PC. I have used the software for about a year and they came out with an new feature that creates an OBDC file to link directly to Access. Since I upgraded that software I have found that their OBDC file sets up a table but all the data has the data type of Text. I have tried to change things on their end without success.

Today I was trying to use their table to get the data from and then use append delete or something to create a different table that I could change the data types to the proper settings.

What is happening (I am assuming this) is that when I run a query the data type being text doesn’t allow for a comparsion of less than, greater than or equal to, as a number datatype would. The query is now returning items that have a negative amount for reorder.

Does this make it as clear as mud.

TIA
Bret

Reply | Quote

Would using the function Val help, this converts numerics in text fields to numerics.

Pat

Reply | Quote