Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Aug 2003
    Location
    St. Paul, Minnesota, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Query (Access2000)

    Hi,

    I am converting a database from Access97 to Access 2000. It is linking to a Foxpro 2.6 database. The following query statement:

    "P_Lic_Sort: IIf([P_Lic]="p","D",IIf([P_Lic]="P","N","R"))"

    works in Access97 but does not work in Access2000. It appears that Access2000 does not recognize upper case vs lower case in the query. The results are all "D".

    Any suggestions? I cannot change the value on the linked Foxpro table.

    Thanks in advance for your help.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access Query (Access2000)

    I suspect this may be related to the ODBC driver for FoxPro - at least I presume you are using an ODBC driver rather than just doing a straight ISAM as you could in 97. To test your theory about upper versus lower case, try importing the table into Access rather than linking. It may be that the IIF function is also interacting badly with the ODBC driver, or that you have an index of some sort on that FoxPro field that Access is using via the ODBC driver. Another thing you could try is a simple query on the FoxPro table that returns all fields, and then do a second query based on the first and do the field calculation that way.
    Wendell

  3. #3
    New Lounger
    Join Date
    Aug 2003
    Location
    St. Paul, Minnesota, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Query (Access2000)

    Hi Wendell,

    Thank you for your response. I am indeed using the Microsoft dBase VFP Driver ODBC. I have 2 problems with this connection.

    I tried creating new table from the linked table before checking for a lower case "p" but it still fails. It treats it like the upper case "P".

    The second problem I am running into is that it shrinks the numeric size on a linked Foxpro table. Using the same ODBC connection, it linked to one table with a field C_Cred 4.1, the table image in Access2000 showed it as Field Size = Decimal, Precision = 5, and Scale = 1 and it works. Another table with the very same field name and size, Access2000 showed it as Field Size Decimal, Precision = 2, and Scale = 1. It stated the the value is too large for the definition.

    Any suggestions on either problem?

    Thanks again,

    Jim

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

    Re: Access Query (Access2000)

    Access has always been case insensitive, not just Access 2000. The way you enforce case sensitivity is with the statement Option Compare Binary at the top of the module that contains the code. Just be sure you only have code that relies on binary comparisons in that module because the Access default is Option Compare Database.

    Precision is the maximum number of digits used to represent values, and there is definitely a difference between Precision=5 and Precision = 2. Numeric scale relates to the number of decimal places.
    Charlotte

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access Query (Access2000)

    I think Charlotte has hit the nail on the head. Since you are doing this in a query rather than in code, it appears the IIF function is the cause of your problem. There must have been a bug in that function in Access 97 that allowed your expression to work, and Microsoft fixed it in 2000. Unfortunately, the other string comparison functions will presumably work in the same fashion, so you may have to resort to code and create a temporary table in order to get the calculated value you want.

    Unfortunately I'm not familiar with how FoxPro handles decimal and currency fields, so I can't help much there, though I note that several other developers have encountered similar problems. You may have to address that with an export from FoxPro, and then an import into Access - effectively another temporary table.
    Wendell

Posting Permissions

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