Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2006
    Posts
    213

    Unanswered: Type Mismatch error

    What normally gives this error?

    I have a library database, when i run a query i get this error. Anyone know why?

    Ive attached my small database if anyone wants to have alook.

    Thanks, Marley.
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    for those of us who don't have that particular version of access, or might be unwilling to go to the trouble of opening it and digging around looking for an error, perhaps you could copy and paste here the sql of the query giving the error, and identify the datatypes of all the columns involved in the query

    you help us, we help you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2006
    Posts
    213
    Ah ok, sorry mate.

    Heres my tables.

    table1-tblBooks
    +BookID (p)
    +BookTitle

    table2 -tblBookTransactions
    +TransactionID
    +BookID
    +ReaderID
    +TransactionTypeID
    +TransactionDate

    table3 -tluTransactionType
    +TransactionTypeID
    +TransactionType

    table4 -tblReader
    +ReaderID
    etc..

    Here are the querys

    Query1 - BookTransactionsLast

    SELECT tblBookTransactions.TransactionID, tblBookTransactions.BookID, tblBookTransactions.ReaderID, tluTransactionType.TransactionType, tblBookTransactions.TransactionDate
    FROM tluTransactionType INNER JOIN tblBookTransactions ON tluTransactionType.TransactionTypeID=tblBookTransa ctions.TransactionTypeID
    WHERE (((tblBookTransactions.TransactionDate) In (SELECT Max(tblBookTransactions.TransactionDate) AS LastTransactionDate FROM tluTransactionType INNER JOIN tblBookTransactions ON tluTransactionType.TransactionTypeID=tblBookTransa ctions.TransactionTypeID GROUP BY tblBookTransactions.BookID));

    Query2 - BooksOnShelf&Returned

    SELECT qryBookTransactionsLast.TransactionID, qryBookTransactionsLast.BookID, qryBookTransactionsLast.ReaderID, qryBookTransactionsLast.TransactionType
    FROM qryBookTransactionsLast
    WHERE (((qryBookTransactionsLast.TransactionType)='On Shelf' Or (qryBookTransactionsLast.TransactionType)='Returne d'));

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    still don't know which datatypes the columns are

    does query 1 run by itself?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2006
    Posts
    213
    Right i got confused myself so ive decided to do it this way, but again im a little stuck.

    I now have 5 tables: tblTitles, tblBooks, tblReaders, tblReturns and tblBookTransactions.

    They appear as follows:

    tblTitles
    TitleID (PK Autonumber)
    ISBN
    Title

    tblBooks
    BookID (PK Autonumber)
    ISBN (FK)

    tblBookTransactions
    TransactionID (PK Autonumber)
    BookID (FK)
    TransactionDate
    TransactionType
    Expiration
    ReturnDate


    I have made a form bound to the tblTitles and added a subform bound to tblTransactions which i believe should allow me to see transactions for that title. Is this correct so far?

    I want to make it so if all the books of that title have Loan records with a null Return Date, then they are not in stock. When a Reservation becomes a loan the Type is changed to Loan. How would i make it so when a customer returns a book would it automatically make the value null?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Marley

    Rudy is probably asleep or taking time off or drunk or something.

    I think the key fields are:
    tluTransactionType.TransactionTypeID
    tblBookTransactions.TransactionTypeID
    tluTransactionType.TransactionType

    What are the data types for these three fields? (BTW - autonumber isn't a data type)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    oK - Noticed the attached db Look at tblBookTransactions.TransactionTypeID data type...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2006
    Posts
    213
    Yeah i set it to autonumber. Should i change this?

    Thats an old version now. Ive attached the new one.
    Attached Files Attached Files
    Last edited by marleyuk; 08-12-06 at 19:05. Reason: attached new database

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The db you attached makes no sense at all.

    tblBookTransactions.TransactionTypeID should not be autobumber. Only PKs should be auto numbers - never foreign keys. Remember - autonumber is not a data type.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2006
    Posts
    213
    haha no sense thats pretty common for me. I have changed tblBookTransactions.TransactionTypeID back to text for now. How would you recommend i turn what i have into something that can output, a query showing whether a book can be reserved and a query that shows when books are overdue?

    Because i only loan books for a 2 week period i think there should be away of writing an sql statement that works out what date the book should be returned and then says the book is overdue if the date has passed. It would be good if i could say how many days overdue it is.

    Thanks,
    Marley.

  11. #11
    Join Date
    Aug 2006
    Location
    Thailand
    Posts
    12
    Pradon in my English.
    You Can't join field in Autonumber type (field "TransactionTypeID" in table "tluTransactionType") with field in text type (field "TransactionTypeID" in table "tblBookTransactions")
    Why didn't to create a query that change Autonumber to Text with "CStr" function and use it instead to use table "tluTransactionType"?
    The query I use in Your attact db is:
    "SELECT CStr(tluTransactionType.TransactionTypeID) AS TransactionTypeID, tluTransactionType.TransactionType
    FROM tluTransactionType;"
    ...
    And use this query in query "qryBookTransactionsLast" in stead to use table "tluTransactionType" directly
    Last edited by polngpole; 08-13-06 at 00:59.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The db made no sense as it was missing an entire table - and a key one at that.

    If you use autonumbers than the primary key data type will be Long. All foreign keys linked to this must also be Longs.

    Also - if you have been able to set up the foreign key as a different data type then you musn't have any relationships set up. I would advise you get your primary and foreign key data types matching and set up relationships - the queries would then be simple.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2006
    Posts
    213
    Could you possibly help me? Im lost to what table it is that im missing.

    Ive now changed all the primary keys and foreign keys to long.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by marleyuk
    Could you possibly help me? Im lost to what table it is that im missing.
    The second database you posted (sdm database.zip) is missing the tluTrans... table. Double click all the queries and they all say the same "Cannot find table....".
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2006
    Posts
    213
    Ok, ive added the TransactionType table. The reason i didnt have it before is because I had TransactionID and TransactionType in the BookTransactions table. Now ive deleted those and added the TransactionType table and added TransactionID to the Book Transactions table. Is this correct?

Posting Permissions

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