Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Dothan, AL
    Posts
    4

    Unanswered: Select Query in a Report?

    I am trying to do a query in a report, and I can't get it working. This is the query I have now, and I am trying to use it in a textbox in a report.

    SELECT Count(VigNo)
    FROM FCD
    WHERE (VigNo Like "01*");

    The query works when trying a straing SQL query on it's own, but does not work when I put it into a text box in a report. I am putting this in the Control Source field of the Textbox:

    =SELECT Count(VigNo) FROM FCD WHERE (VigNo Like "01*");

    I get an error in Access that says "Check the Query's syntax and enclose the subquery in parentheses." So I revised the query to look like this:

    =(SELECT Count(VigNo) FROM FCD WHERE (VigNo Like "01*")

    Then when I go to view the report, the text box displays '#Name?'

    Does anyone have an idea on how to get around this or fix the error? All I want is a count of records that start with 01 in the field VigNo.

    Is this even possible to do in access?

  2. #2
    Join Date
    Feb 2004
    Location
    Houston, Texas
    Posts
    45
    I've done something similar using the Format event on the Detail. Would this technique work for you?

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.txtCustCount.Value = DCount("CustomerNumber", "tblCustomer", "CustomerNumber like '100*'")
    End Sub

    ...rtk

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Select Query in a Report?

    Originally posted by WYSIWYG
    I am trying to do a query in a report, and I can't get it working. This is the query I have now, and I am trying to use it in a textbox in a report.

    SELECT Count(VigNo)
    FROM FCD
    WHERE (VigNo Like "01*");

    The query works when trying a straing SQL query on it's own, but does not work when I put it into a text box in a report. I am putting this in the Control Source field of the Textbox:

    =SELECT Count(VigNo) FROM FCD WHERE (VigNo Like "01*");

    I get an error in Access that says "Check the Query's syntax and enclose the subquery in parentheses." So I revised the query to look like this:

    =(SELECT Count(VigNo) FROM FCD WHERE (VigNo Like "01*")

    Then when I go to view the report, the text box displays '#Name?'

    Does anyone have an idea on how to get around this or fix the error? All I want is a count of records that start with 01 in the field VigNo.

    Is this even possible to do in access?
    You need to make the report's controlsource that query and then bind the textbox to the column returned in the query.

Posting Permissions

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