Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116

    Unanswered: Field problem after converting from97 to 2000

    Does any one know if there is an issue with Access 97 database created field lengths not converting in Access 2000?

    I have a field that I created in Access 97:
    Description: IIf(Left([Invoice History Detail]![HDCUNO],3)="001" And Left([Invoice History Detail]![HDSYCD],1)="1" Or (Left([Invoice History Detail]![HDCUNO],3)="002" And Left([HDSYCD],1)="7"),"RECYCLE","TRASH") & " " & [Invoice History Detail]![HDCTQT] & "-" & [Invoice History Detail]![HDCTSZ] & [Invoice History Detail]![HDVLCD] & " " & IIf([Invoice History Detail]![HDPUDY] Is Null Or Len([Invoice History Detail]![HDPUDY])-CharCount("-",[Invoice History Detail]![HDPUDY])=0,"ON CALL",Len([Invoice History Detail]![HDPUDY])-CharCount("-",[Invoice History Detail]![HDPUDY])) & "/W-" & Left([Invoice History Detail]![HDINVN],6)

    But in Access 2000 it breaks. When I try to edit the field only part of it is shown. How do I fix? I'm thinking that it has too many characters so I will have to break it into two or more fields then create another query to add the two fields together. Does anyone have any ideas?

    Thanks . . . texasalynn

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    If you feel that the Immediate if statement behind the control is too long (I am not sure of the limitattions but I do not think that this is the issue) try puttting the logic behind the oncurrent event of the form itself and repaint the control as you scroll through th erecords

    or try dimming some of the names in a function and call the function to fill in the data for the control.

  3. #3
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    I'm sorry you have totally lost me. What? I'm not a guru so I need simple explanations. Go into query - add a field, etc. etc.

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    ok if the IIF statement is in a query the maximum length of the sql statement can be 2056 characters so there is probably not a limitation issue.

    when you say "breaks" what error message do you get?

    I thought that the IIf statment ws behind a field on a form and using the same logic you could correct data using an if/then statement behind the form.

    when in access 2000 press alt F11 and from the Tools menu in the VBA window select "references"
    There will be several references that are checked. Do any say "missing"?

    How did you convert to Access? Did you use the conversion utility? Sometimes better results are gained when you create a blank database in 2000 and import all of the tables/queries/forms etc.

    I aplologize about not being more clear - it's just that after reading a complex iif statement I jumped a few steps.

  5. #5
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    And just as an afterthought, my observed opinion is that Access 97 and Access 2002 are a lot less buggy than Access 2000.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  6. #6
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    OK - someone has said that their is a limit on the field size of 512 characters. Mine has more like 600+. So that is the problem.

    Thanks for the help.
    texasalynn

Posting Permissions

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