Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Reports & Parameter Queries (97)

    I have created a parameter query in Access97, VisitRecordQry, that prompts for a beginning & ending date; the results of which are used in a report. In this report I created the following Dcount control: =DCount("[clinic md name]","VisitRecordQry","[clinic md name]='Smith'"), but I get an #error message. I changed the domain to the name of the table containing the data, but that also gave an error as long as the query had a parameter. If I eliminate the parameters from the query I get the correct value, but of course I also get a lot of records I don't want. What am I doing wrong? Thanks for any help

    Tanya

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access Reports & Parameter Queries (97)

    I can't be sure, but I'm guessing your problem may be that of controls on the report having the same name as fields in the tables. While Access automatically uses the fieldname as the controlname, this isn't a good idea. YOu should get in the habit of changing them. For example, if it is a textbox control, merely add a "txt" prefix.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access Reports & Parameter Queries (97)

    I believe the Dcount should read like this:

    =DCount("clinic md name","VisitRecordQry","[clinic md name]='Smith'")

    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Access Reports & Parameter Queries (97)

    I think that DCount("[clinic md name]","VisitRecordQry","[clinic md name]='Smith'") in the report will try to open another copy of the query, which needs to prompt for dates, and so fails.

    Try putting the beginning and end dates on the form , rather than prompt for them. Tthat way the second copy of the query can get them also.
    Regards
    John



  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access Reports & Parameter Queries (97)

    After testing this, John is quite right.
    He quite rightly points to the parameters that the query requires will force the #error problem, the square brackets have no effect at all.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Reports & Parameter Queries (97)

    The square brackets definitely DO have an effect. You can't reference objects with spaces in their names unless you use square brackets around them.
    Charlotte

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access Reports & Parameter Queries (97)

    Just to clarify my response, in this instance the square brackets don't make any difference in the first parameter of the DCount command, it is already enclosed inside quotation marks.
    Generally, you are quite right about the square brackets, especially inn a query for example.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Reports & Parameter Queries (97)

    On a partly unrelated topic...but why don't MS actually change this in Access when it automatically creates controls. It seems like it would be a relatively simple change (famous last words)?

    ADDED: Sorry, this is a bit out of context at the bottom of the thread...I am talking about Access automatically naming control names exactly as their underlying Control Source.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Reports & Parameter Queries (97)

    It would require a standardized naming convention, and MS can't seem to decide on one for Access.
    Charlotte

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access Reports & Parameter Queries (97)

    I really don't know why, I guess it isn't on their priority list. But when someone like Ken Getz says "ALWAYS RENAME YOUR CONTROLS!", I don't know why they won't listen. The Reddick VBA naming convention includes prefixes for controls, so it's not like they would be inventing anything, just putting something in place that is already being done manually.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    Star Lounger
    Join Date
    Jun 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Reports & Parameter Queries (97)

    Thanks for your response, although I don't think that's the problem because I haven't changed the names of any of the controls on the report. All the controls have the default names assigned to them by Access (ex: Text ##, etc.). When I preview the report it does prompt me for dates, which is what I want so only records in a certain date range are displayed. The correct records show, however, the DCount generates an error message. If the parameters are removed from the query, in which case I am not prompted for dates, the dcount function gives the correct results, but of course I'm seeing many more records than I'm interested in. Is there possibly a way to set parameters within the report vs. the query the report is based on? Thanks for your help.

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Access Reports & Parameter Queries (97)

    I thin the solution is to put the dates on a form , not be prompted for them, because you need two copies of the query to run, and it seems only one of them is prompting for dates.
    Regards
    John



  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access Reports & Parameter Queries (97)

    I'm confused by what you originally said. This query that you created that is used in the DLookup, is it also used as the recordsource for the report? I ask this because you said that if you remove the parameters, you get more records than you wanted, yet the Dcount returns a correct number! I think you need to keep the parameters in the query, but use something else in the Dcount.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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