9 Replies Latest reply on Oct 13, 2016 9:35 PM by beverly

    Tricky Query


      Hello Everyone.

      I have a tricky query that I am trying to perform and I just can't get my head around it. I have a library database with a dateborrowed and duedatereturned field. What I am trying to do is a query which on the date queried will return all records for the books which are currently borrowed.


      I had thought about another field which would show status whilst borrowed, which would be fine for current day queries, but I want this to be able to be queried at any given time.


      I am sorry, but I am not explaining myself very well.



        • 1. Re: Tricky Query

          You want to FIND books that have something in the dateborrowed but nothing in the datereturned.


          For a manual find

          Enter find mode

          Type "*" (no quotes) in the dateborrowed field and

          Type "=" (no quotes) in the datereturned field.

          Perform the find


          Sent from miPhone

          • 2. Re: Tricky Query

            dudatereturned sounds like a field that is trying to do too much.  Shouldn't you have two fields - one for duedate and one for returneddate.  Then all you need to do at any time to find the books which are currently on loan is do a find for records which have a value in dateborrowed and have no value in returneddate.

            • 3. Re: Tricky Query


              • 4. Re: Tricky Query
                David Moyer


                what are books?


                I think the new field needs to be DateReturned, unless you already have one.  I see DateBorrowed and DateDue (paraphrasing).  If you had the DateReturned field, then you could add the "status" field you mention, as a calculated text field, to check if DateReturned was empty (see the FM function Isempty),

                • 5. Re: Tricky Query

                  Hi Guys,

                  Thank you for the help. I already have a DueDate field. The DueDateReturned field was badly named at the time, but I have left it there. As I was trying to say, I can search for all the current books which are on loan, ie using IsEmpty and searching with *,


                  but what I want to search is for a snap shot from a previous day in the past where all 3 date fields would be filled in.


                  example would be a book borrowed on 1/1/2016 and due 1/2/2016 but was returned 31/1/2016. Now If I search for 15/1/2016 I should  be able to find that this book was borrowed.


                  Thanks for the speedy responses.

                  • 6. Re: Tricky Query
                    David Moyer

                    Does this work?

                    - enter find mode

                    - set DateBorrowed to <=YourDate

                    - set DateReturned to >=YourDate

                    - perform find

                    • 7. Re: Tricky Query

                      Hi David,

                      Yes that did the trick. I was trying to overthink it and as usual, the solution is really straight forward.


                      Cheers and thanks

                      • 8. Re: Tricky Query


                        Something I have just noticed is that if the datereturned field is empty (the book hasn't been returned) then this query won't find the book.


                        I can do this with a separate query looking for "=" in the datereturned query.


                        Does anyone know if this could be done manually as a single query something like:

                        - enter find mode

                        - set DateBorrowed to <=YourDate

                        - set DateReturned to >=YourDate or "="

                        - perform find

                        • 9. Re: Tricky Query

                          This would an AND query. Both conditions are true. If you need OR, stick a New Record/Request between the Set Field steps.


                          Although, manually isn't Set Field, you type the "<=" and whatever date directly into the field. Any symbol "=", etc. can be used in manual finds. There is even a drop-down to select them (when in Find mode).  


                          Sent from miPhone