Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Question Unanswered: Input Box for query

    I am fairly new to Access. I am using Access 2007 and am trying to make it run a query by popping up an input box and then running a query that "contains" the string that is input. In other words, if I have a field called TITLE and I run the query and I put Microsoft in the input box, it will query the TITLE field and return all records with Microsoft in it, regardless of what else the field contains (MS Access, MS Word, etc.).

    I hope I explained this well enough...

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    You're going to have to write some VBA to prompt for the input and then incorporate that into a SQL string. I'd suggest going through Martin Green's six lectures on putting VBA and SQL together. That should get you pretty far. BTW, how are you planning on using the results?
    Me.Geek = True

  3. #3
    Join Date
    Apr 2009
    Posts
    3
    Thanks, I will check out the course. Unfortunately, I'm not a programmer, so it will be a stretch for me.

    I was hoping to send that to a report or maybe on a form.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Since you said you were "fairly new to Access" I gave you Martin Green's site, he makes things very approachable no matter how new you are to Access in my opinion.

    Once you get how to build the SQL statement in Access with the InputBox response, you'll want to look at applying it to the RecordSource property of either a form or a report. Though alternatively, if you just need to use the inputbox response to specify the WHERE clause of your SQL, you may want to look into just applying a Filter to your Form/Report (where you take the input and just build the WHERE clause instead of the whole string). You can use this filter string that you build as an argument when you open the form/report.

    Hope that helps, let us know how you get along. Cheers!
    Me.Geek = True

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The crude method for prompting the user for a value in a query is to put:

    [Please enter a title]

    In the criteria for the field you want to prompt for. In your case it sounds like the Title field.

    Access will evaluate the query when you run it and determine it doesn't have a value for the variable [Please enter a title] and will prompt the user for it.

    The reason I say this is the crude method is because you cannot validate what has been entered by the user. If the user presses enter without entering a title then Access will return no records. The biggest problem is if you are expecting a number and the user enters text. You will get an error message 'Type Mismatch' when you run it that way.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I got the job of refactoring a database with a sh*tload of those crude type parameters in it. One report has 6 parameters to be filled in one after the other. There are 50 reports with varying parameters and they want these reports updated. Testing them is as annoying as hell. Fill in 6 parameters, notice this line is not straight, go to design, straighten the line, preview, fill in six #&$(*@ fields again, and notice that the line is now straight, but that heading is not bold......

    Use custom dialog boxes wherever possible!!

    /rant
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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