Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    'Type mismatch' in table (2K and XP)

    I hope someone will be able to diagnose this little problem quickly. I'm sitting about 20 miles from the person who's using the DB I created, and I think she's ready to throw something at me. She's approaching a deadline, and a little problem developed.

    Try to open the table tbl_Catalog, and you'll get a "Type mismatch in expression" error. "Access cannot open this table in datasheet view." Here's what led up to this development: Two key tables in this DB are the Catalog and Donors tables. You'll see that I created a Donors form with a subform for the catalog. This seemed like a very logical arrangement to me, but the user doesn't like it. She prefers to enter data directly into the tables. Whatever....

    Today, she was adding a new item to the Catalog table when she realized she had not entered the donor's information into the Donors table. She stopped entering the donation information--actually stopped keying in the title without completing it--and (I believe) closed the Catalog table. Then she opened the Donors table and entered that information. When she went back to the Catalog table, she got the error message.

    The donor's information appears in the Donors table and, of course, in the Donors form. Oddly, when we try to view the catalog subform in DATASHEET VIEW, it opens just fine. But we still can't open the the catalog table directly. Wierd!
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'Type mismatch' in table (2K and XP)

    Open the table in design view.
    Activate the Properties window.
    Clear the Filter property.
    Close and save the table.
    Now, you should be able to open it in datasheet view.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Type mismatch' in table (2K and XP)

    Hi,
    This may not be the best answer, but it resolved the problem: I was able to view it after setting "Allow Zero Length" to "Yes" in all fields. Since the Donor Name field was empty in one of the last records, I imagine that setting the "Allow Zero Length to "Yes" on only that one field will do the trick.
    thx
    Pat

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: 'Type mismatch' in table (2K and XP)

    Hans: Thanks for that tip. It worked! Bless you!

    One more problem, however. You've seen this very same DB before, and you made some changes to overcome a "type mismatch" problem that would not let us sort the data. We can now open the DB, yes, but we still can't sort it. I've tried sorting on ItemName, Donor, Category...nothing works. Would you be so kind as to look at this problem and offer a solution. Thank you, thank you!

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

    Re: 'Type mismatch' in table (2K and XP)

    In the design view of the tblCatalog table change the display control (found in the tab Lookup) to ListBox.

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'Type mismatch' in table (2K and XP)

    The CatID field in tbl_Categories is an AutoNumber field, that is a Long Integer. The CatID field in tblCatalog is a text field. It should be Numeric (Long Integer) too. Although you see text, this is only an illusion, the first column in the combo box is hidden (column width = 0).

    If you set the field type of CatID to Numeric (Long Integer), you will be able to sort on Category, and what's more important, you will be able to join tbl_Categories and tblCatalog on CatID with referential integrity enforced.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: 'Type mismatch' in table (2K and XP)

    I see, Hans. Thanks! Now, here's a related question.

    Once we have all our data entered into the catalog, we have to export the table as a tab-delimited text file. (Someone else is going to take that information and build a printed program with Adobe Pagemaker.) We did a quick "dry run" today and exported the table into Excel. To our dismay, those fields that display plain-English text--what you called "an illusion"--appeared as the real numbers. For example, in the table the lookup field for Category displays "Collectibles," but when exported we see the number 7. I'm sure the folks who'll put together the printed program won't be amused to see that 7. We have the same problem with the Donor field. How can we fix this little problem?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'Type mismatch' in table (2K and XP)

    In fact, you've already got the answer in your database - don't export the table, but export a query based on the three tables, like qry_Catalog2003. By including the Category field from tbl_Categories and the DonorName from tbl_Donors, you ensure that the text value gets exported instead of the numerical ID. The qry_Catalog2003 query includes only one field from tblCatalog, but you can add as many as you like.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: 'Type mismatch' in table (2K and XP)

    Thanks again, Hans! I shoulda seen that one! <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

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

    Re: 'Type mismatch' in table (2K and XP)

    What I suggested in my earlier post was:
    >>In the design view of the tblCatalog table change the display control (found in the tab Lookup) to ListBox.<<

    After some more investigation I noticed all I had to do was to make a minor change (to a field name or a data type) and change it back to it's original value and save this table and it would open without the error "Type mismatch in expression" ???
    Either I have a funny version of A2000 (it's not even SR-1a) or something weird happens when you write the table and try it again.

    Any ideas Hans
    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
  •