Results 1 to 8 of 8
  1. #1
    sherrygroves is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    23

    Prompting for user input in query


    Hi!

    I have a calculated field in a query - DaysTilDue1: IIf([Completed]=True,99999,[Report Due Date]-Date())

    I want the query to prompt for how many days due and give me the reports due for the days <= that number. For example if they want to see reports due within 45 days - they would enter 45 and get the reports due within 45 days. I can't get his to work. I tried <=[Days Til Due?]. it prompts for the number of days but the results are not what I want.

    Please help.

    Thanks!
    Sherry

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What results are you getting? What results do you want? The query works - no error?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Are the results what you want without the criteria? Here is an Example:
    <=Now()+45

    HTH

  4. #4
    sherrygroves is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    23
    Hi!

    Yes, the query runs without error. The expected results are the 8 or 10 reports with due dates 45 days or less than today. The results I'm getting are 3 or four reports with dates 30 or less and then everything over about 100 days. I have 2 other queries written, one I've coded in the <=45 days and then one with no filters at all. Both of those work fine, giving the expected results.

  5. #5
    sherrygroves is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    23
    One other thing, the number of days til due could be negative, if the report has come due but not completed.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I can't see anything wrong with the expression and if it works in other query, no reason for it not to work in this one. I would have to review database and the data being queried.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    sherrygroves is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    23
    The difference between the query that works and the one I am having problems with is that the one that's working, I'm not prompting for a number. The <=45 is coded in the query. I'm trying to give some flexibility in what output I can get.

    In the one with unexpected results, I am also prompting for a region code but that is okay. I can remove the <= and just prompt for a number and I will get only the reports due that many days from now. For example, put in 45 and get the 3 that are due 45 days from today.

  8. #8
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Maybe try adding If Nz(somedate,0)=0 to your date criteria. Or
    if isnull([cboDate])=true


    HTH
    Last edited by burrina; 08-31-2014 at 09:34 PM. Reason: Additional Code

Please reply to this thread with any new information or opinions.

Similar Threads

  1. A query based on user input in form
    By drhassan in forum Queries
    Replies: 17
    Last Post: 01-20-2014, 06:00 PM
  2. Replies: 6
    Last Post: 06-28-2013, 03:12 PM
  3. Replies: 6
    Last Post: 07-22-2010, 05:53 PM
  4. User Input Query
    By ManC in forum Queries
    Replies: 2
    Last Post: 03-04-2010, 07:09 PM
  5. Replies: 0
    Last Post: 04-26-2008, 09:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums