Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2009
    Posts
    4

    Exclamation Display Query Parameter In Report With No Data


    I have a daily report which needs to be run and saved even if there is are no records returned from the query. Originally I formatted the header to display the report name plus the user entered paramater in the underlaying query however if the query returns no records it will not pass the paramater to the report. How can I force the query to pass the paramater entered? I know most people don't require a report with no records but in this case even no records is important to archive.
    Thank you.

  2. #2
    Join Date
    Feb 2009
    Posts
    16
    If it is just a report that you are running couldn't you just put the results into a recordset (or maybe a temporary table) and use something like

    Me.Yourtextbox.DefaultValue = "No Records Returned."
    If rs.BOF=rs.EOF then
    Me.Yourtextbox.Visible = True
    End If

    I don't recall the exact sytax, but you should get the idea. I don't think you could do this directly on the query results, thus the recordset or table suggestion. If you used a temporary table you would need to use DFirst and DLast. Obviously, the OnOpen Event should be set to Me.Yourtextbox.Visible = False

  3. #3
    Join Date
    Jan 2009
    Posts
    4
    Thank you for the reply. Unfortunately that is a bit beyond my skills. To clarify do you mean create a temporary table and base the report on the table? If so how would I force the parameters entered in the query into the temp table so the report would pick it up?

  4. #4
    Join Date
    Feb 2009
    Posts
    16
    Okay, even easier...

    Why don't you use the DLast function and test for null. If Null then your control would display that there are no records via the control source.

    If your control was a list box for example it might look like this.

    If Not IsNull(DLast(Me.lstDisplayQryResults)) Then
    Me!lstDisplayQryResults.RowSourceType = "Value List"
    Me!lstDisplayQryResults.RowSource = " No Records to Display" 'I don't know how column you have but you look up the value list syntax and add commas as needed.
    End If
    This piece of code should probably go in the OnOpen event for your form. In the OnClose event you would have to reset your list box.

    Me!lstDisplayQryResults.RowSourceType = "Table/Query"
    As always I recommend checking my syntax as it is really perfect.

  5. #5
    Join Date
    Jan 2009
    Posts
    4
    Thank you. The only problem is not just to show there are no records but to carry through the paramater entered. No records is as important as records returned. For example there was a text box which contained the text and the query parameter to run the report so it would display "Check In for [date entered]" or "Check In 02/09/09"

    The problem is when the query returns no records, it does not pass the parameter entered to the report so the title just contains an error. I need to title to contain the paramater the user entered regardless of whether the query returns any records. Is there a way to do this without extensive code?

    Thanks in advance.

  6. #6
    Join Date
    Feb 2009
    Posts
    16
    As always I recommend checking my syntax as it is really perfect.
    This is funny, my grammar can be pretty poor as well. This should have said "rarely perfect".

    The problem is when the query returns no records, it does not pass the parameter entered to the report so the title just contains an error. I need to title to contain the paramater the user entered regardless of whether the query returns any records. Is there a way to do this without extensive code?
    I am not clear on this. I assume that the date entered field is being pulled directly from the forms text box where the date is entered. Your query shouldn't have anything to do with this????

    If you wanted to put this in the code I provided change:
    Code:
     
    Me!lstDisplayQryResults.RowSource = " No Records to Display"
    To....
    Code:
    Me!lstDisplayQryResults.RowSource = " No Records to Display for " & Me.YourDateTextBoxName

  7. #7
    Join Date
    Jan 2009
    Posts
    4
    I am using a simple report based on a select query which prompts the user for the parameter prior to each run. It is the paramater the user inputs that I need to display on the report even when the report contains no matching records. Are you suggesting using a text box to initiate the query rather than allowing the native prompt? Thank you

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

Similar Threads

  1. Replies: 0
    Last Post: 08-07-2008, 07:02 PM
  2. How to display data from another record
    By rodrigo in forum Access
    Replies: 1
    Last Post: 07-24-2006, 07:29 PM
  3. Replies: 4
    Last Post: 01-05-2006, 02:36 AM
  4. Add combo box to parameter query
    By louisa14 in forum Queries
    Replies: 1
    Last Post: 12-10-2005, 08:38 AM
  5. DISPLAY INPUT PARAMETERS ON THE REPORT
    By fadone in forum Forms
    Replies: 1
    Last Post: 12-09-2005, 10:26 PM

Tags for this Thread

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