Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    changing data type in linked tables (2002 - XP)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: changing data type in linked tables (2002 - XP)

    [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.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changing data type in linked tables (2002 - XP)

    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

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: changing data type in linked tables (2002 - XP)

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

    Pat

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •