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
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
WHERE (((qryBookTransactionsLast.TransactionType)='On Shelf' Or (qryBookTransactionsLast.TransactionType)='Returne d'));
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?
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.
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
And use this query in query "qryBookTransactionsLast" in stead to use table "tluTransactionType" directly
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.
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?