Results 1 to 4 of 4
  1. #1
    cheese9799 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2011
    Posts
    11

    Using user inputs as variables in a query/report?

    I have a query which takes a few fields and sorts them. The intention is that the user inputs how far back it would like the database to display results from, and then that value is used. For example, the user inputs '4', and the database displays the results from the last 4 months, but not before then.



    I have it set up so it works with a number inputted into the code, such as 3 months or 4 months, but I can't get it to work with a user-inputted number.

    The current idea was to use a OnOpen event on the report, setting a variable called NoOfMonths with the inputted value. However, it doesn't seem that this value can then be used in the code, because it doesn't recognise it when I try to use it in the query...

    How would I get around this?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The most common method is to have a form the user inputs the criteria on, and either have a query use the form for its criteria or using a technique like this:

    BaldyWeb wherecondition

    You can use a variable in the open event, but I'd set the filter with it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    cheese9799 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2011
    Posts
    11
    Quote Originally Posted by pbaldy View Post
    The most common method is to have a form the user inputs the criteria on, and either have a query use the form for its criteria or using a technique like this:

    BaldyWeb wherecondition

    You can use a variable in the open event, but I'd set the filter with it.
    Hmm... Not really getting that. I've made a form (not linked to any table) with a text box that the user is to enter the number into. How do I then link this to the report?

    I tried doing an AfterUpdate event with DoCmd.OpenReport but the report doesn't even open. Same with OpenQuery. Doubt it'd work anyway...

    Basically, I don't know how to a) make a query use a form's value and b) make sure that the form has to be opened before the report.

    I need the user to click a button, so the form pops up. They enter their value into the box, then the report pops up.

    EDIT:

    Ok, here's what I have:

    Option Compare Database

    Private Sub txtWeeksBack_AfterUpdate()
    DoCmd.OpenReport "rptHighestPayingRecentJobs", , , "curPrice > Me.txtWeeksBack"
    End Sub

    txtWeeksBack is the text box on my form. The form isn't assigned to any particular tables and there are no fields - just the text box. This is just a test code - it should open my rptHighestPayingRecentJobs report, showing every field where curPrice is a larger value than whatever was entered in the Weeks Back text box. If I can get this to work, I can change it for my actual solution. However, the report doesn't open - in fact, it tries printing!! I have no idea why it's doing this. It does 2 things: a) it says something about printing, and b) asks me for the value of Me.txtWeeksBack, so obviously isn't getting that from the control.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    There are two basic options, like I said. The first is to have the query criteria use the form:

    Forms!FormName.TextboxName

    The second is the link I gave, which uses the wherecondition argument of OpenReport. As to have the form open, I typically have the button the user clicks to get their report on that form, so then you're sure it's open.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 9
    Last Post: 02-17-2011, 03:33 PM
  2. Append Query using variables
    By hawg1 in forum Queries
    Replies: 2
    Last Post: 06-09-2010, 08:59 AM
  3. Replies: 1
    Last Post: 11-30-2009, 05:11 PM
  4. Multiple inputs one result
    By ee12csvt in forum Queries
    Replies: 0
    Last Post: 09-11-2009, 03:19 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