Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2011
    Posts
    83

    Unanswered: Between date query

    Hi All,
    I open a form which is linked to a query. The query calculates stock sold between two dates. In the DESPATCHDATE field of the query, I have added the criteria Between [Start Date] and [End Date].

    The form then opens, asks for my start and end dates and displays the info for the period that I want.

    What I want to do is to display the Start and End dates on the form to remind the user what period they are looking at.

    Is there a way of recording the START and END dates that I have typed, saving them as a variable and then showing them on the form?

    Any ideas?

    Thanks
    Simon

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Yes.

    Create a couple of unbound text boxes on the form. In the form's OnOpen event, use InputBox to prompt for the start and end dates, and then put these values into the relevant text boxes on the form. Finally, amend the query so that instead of prompting for the dates (again), it uses the unbound text boxes.

    You might need to force the form to requery after it opens.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jan 2011
    Posts
    83
    Thanks a lot.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're welcome!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Jan 2011
    Posts
    83
    Ok, I have got as far as this and struggling again:

    "Finally, amend the query so that instead of prompting for the dates (again), it uses the unbound text boxes"

    How do I do this?

    My original query does not ask for Start and End dates as the InputBox in the OnOpen event does this. If I requery the original qery, it does not ask for any criteria.

    Thanks
    Simon

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    In the DESPATCHDATE field of the query, I have added the criteria Between [Start Date] and [End Date].
    Replace "[Start Date]" with "Forms![$NameOfYourForm].[$NameOfStartDateTextBox]" and "[End Date]" with "Forms![$NameOfYourForm].[$NameOfEndDateTextBox]". This will cause the query to look at these text boxes for the date range values.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Jan 2011
    Posts
    83
    Done that. When the form opens for the first time, it queries the query BEFORE we get to the InputBox command and so errors at the query stage becuase the txtboxes are empty.

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Ah.

    There's a way around this. If you check Access' help for "Parameter queries", there's something about occasionally wanting to return all records with a parameter query. Using a form of that should solve this problem.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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