Access Reports & Parameter Queries (97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

I believe the Dcount should read like this:

=DCount(“clinic md name”,”VisitRecordQry”,”[clinic md name]=’Smith'”)

HTH
Pat

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote