Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2011
    Posts
    13

    Unanswered: Adding Query Criteria in Report Header

    I have a query that has this criteria for a "CourseDescription" field:

    Like [Enter Name of Course with * in front and after]

    I would like to have the Name of Course that is entered with the * in front and after in the Report Header.

    So if someone is searching for all training that has the word *fork* (say for the word forklift), I want the word FORK to populate a text box in the report header.

    Thanks for your help!

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Change the report header to a text box (instead of a label) so it will be programmable. Also, add a hidden text box anywhere in the report header section. This can be either bound (if it gets its data from the report's recordsource), or unbound (if it gets the data from VBA). You might call the hidden textbox "txtTWord."

    In the report header text box, the Control Source is (for example)
    Code:
    ="All Training Course Names That Contain the Word *" & Me.txtTWord & "*"
    Don't forget the "=" in front.

    The reason I use this system is so I don't skewer the text because of an extra-short or extra-long text value in a text box.

  3. #3
    Join Date
    Jul 2011
    Posts
    13
    I'm not sure I understand.... I copied the code you sent me - it is not returning the word that is entered into the query between the * asterisks.

    I tried changing the Me.TxtTWord to my field name that the query is pulling on: CourseDescription.TxtTWord.... still nothing - I'm getting the #Name?error. Since the query is combining a few different tables I thought maybe I needed the Table Name too: [CourseRequirements].[CourseDescription].txtTWord. Using either of my changes automactically puts in the brackets around txtTWord.

    This is in the text box that is not hidden. Also, why do I need to have a hidden text box?

    Attached is a screen print of the query in case you need information on it.

    Thanks!
    Attached Files Attached Files

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Two things leap out at me from the query that you uploaded. One is that there are no joins at all for the tables CourseRequirements or Employee. There should at least be one Inner Join each to TrainingRecords.

    The second thing is that your criteria line should read
    Code:
    Like "*" & [Enter Course Name] & "*"
    This is because the text between the brackets is considered like a variable. If you provide the "*" (in order to satisfy the 'Like' keyword) on both sides, you don't need it when you run the query. That's why I hard-coded the two "*" in the header text box.

    The purpose for the hidden textbox is so you don't have two text boxes with the same information. If it's hidden, it'll only appear in the header.

    Sam

  5. #5
    Join Date
    Jul 2011
    Posts
    13
    I corrected the criteria line per your suggestion. Thank you, the really helps for this query and all future queries....

    I'm still have difficulties with the other code - still getting the same error message and i get a "pop-up" asking for the "Me" of the code. What am I doing wrong?!?!?

    I've attached another screen shot of what the text box code looks like.

    Thanks!
    Attached Files Attached Files

  6. #6
    Join Date
    Jul 2011
    Posts
    13
    I've attached another .pdf with notes about the table joins.....

    Thanks!
    Attached Files Attached Files

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by cin324 View Post
    I've attached another .pdf with notes about the table joins.....

    Thanks!
    It is here, but it wasn't in the screen shot I opened yesterday. It's fine.

    Sam

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by cin324 View Post
    I'm still have difficulties with the other code - still getting the same error message and i get a "pop-up" asking for the "Me" of the code. What am I doing wrong?!?!?

    I've attached another screen shot of what the text box code looks like.
    The text box that's labeled "Text38" should be named "txtTWord". Not the label, the text box. Since you made it unbound, that means it's getting its value from VBA in the Report Header section of the report.

    Unfortunately, I have to run now, and I won't be available until Sunday at the earliest, and more likely Monday afternoon. Please try to understand where I'm heading, and don't hesitate to look at the Help file. It's really helpful. Also, maybe someone else on the forum would like to lend a hand.

    Sam

  9. #9
    Join Date
    Jul 2011
    Posts
    13
    TextBox 38 is named txtTWord although it doesn't show that on the .pdf. I'm not sure why the label for that text box is showing text38. This is the hidden text box.

    If the hidden text box should be "bound" then I have no clue how to change that.....

    The other text box i did not name - left it default. once again i have copied your code and it is still prompting me for the "Me" in code.

    Apparently I'm still missing something in your instructions.

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    What section of the report did you copy the code to? It should be copied to the report header section.

    I have a different question, though. Does the query give you correct results when you run it by itself (in other words, without the report formatting)?

    SL

  11. #11
    Join Date
    Jul 2011
    Posts
    13
    Yes, I get the proper results when I run the query.

    I pasted your code into the Text Box that I created in the Report Header.

    See attached for the property sheets for both text boxes in the report header...... (I've added a "Between" text box to the header in the past, why is this more difficult?)

    Thanks
    Attached Files Attached Files

  12. #12
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    My mistake. Remove the "Me." from the text box. It refers automatically to the hidden text box called txtTWord.

    My appologies,

    Sam

  13. #13
    Join Date
    Jul 2011
    Posts
    13
    Thanks for your help!!!

  14. #14
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Hold on! Did it work???

    SL

  15. #15
    Join Date
    Jul 2011
    Posts
    13
    No it didn't.... I actually put the same code in the Control Source of the Text Box in the Report Header as the code in the query:

    ="*" & [Enter Part of Course Name] & "*"

    It populates the text box with the word between the "*"

    I then created a label with this caption: "All Courses that contain the word:"

    So the report header looks like this:
    All Courses that contain the word: "fork"

    Thanks

Posting Permissions

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