467,120 Members | 1,246 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,120 developers. It's quick & easy.

Using parameter queries in an Access REPORT--what is the extra parameter?

100+
I am trying my hand at my first ever Access report. It is based on a two parameter query (start date, end date). When I use the report wizard and click "view report" the report prompts me for the two queries. Great! However, when I close it and re-run the report, it prompts me for a third parameter: HOTSTICK_FAILURE.DATE_ENTERED. The first half is a table name; the second half is a table field name.

The query this report is based off of sums up totals between the start date and end date...so clearly I don't want to specify a "date entered" in my query...even if I DID understand where it was coming from.

What am I doing wrong, or what can I do to make this work just like my query? I did see something in the help on Microsoft that included using a form to pass values to a report query...it involved a bit of VBA code, and I don't understand all of the code so I'm reluctant to use it. Is there an easier way?

I am a new Access user, so don't assume I know anything!! :-)

Thanks in advance, Danica
Oct 14 '09 #1

✓ answered by Delerna

Hi Dancia.
Your query is selecting 6 fields and is aggregating 4 of those 6.

Since you have no aggregate fuction on two of the selected fields,
it is an absolute must that those two fields are listed
in the GROUP BY clause. The rule is
ANY selected field that is not being aggregated must appear in
the group by clause or the query will generate an error stating that the field is an invalid selection because it is not aggregated and it is not in the group by.

The 2 fields that must be in the group by clause are
....[HOTSTICK_FAILURE].[DATE_ENTERED]
....[HOTSTICK_FAILURE].[COMMODITY_DESC]


Now to the point.
In post 5 you mention that you removed
[HOTSTICK_FAILURE].[DATE_ENTERED]
from the group by clause.
I presume that it was still in the SELECT list?

If that is the case then you should have gotten something like this error
Column [HOTSTICK_FAILURE].[DATE_ENTERED] is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
You should have got that error IF
[HOTSTICK_FAILURE].[DATE_ENTERED]
is in fact an existing field.

If it is not an existing field then you would not get the above error.
Instead, your query would ask you to provide a value for it.
This is indeed what is happening!

It appears that the query is not recognising
[HOTSTICK_FAILURE].[DATE_ENTERED]
as a legitimate field and is therefore asking you for its value


The table name is exactly the same as the rest so it looks OK.
So, are you sure you have not missspelled the the field name.
are you sure that field actually exists in the table.

Sorry for the lengthy explanation

  • viewed: 3968
Share:
25 Replies
Delerna
Expert 1GB
Can you post your query code.

Here's how to get it in case you don't already know!
Instructions are for access 2003. It may be different if you are using 2007
1) Design the query
2) In the viey menu select "SQL View"
3) A window will appear with the actual SQL code for your query
4) Highlight all of the SQL code and copy it
5) Paste it into a post
Oct 15 '09 #2
Delerna
Expert 1GB
I did see something in the help on Microsoft that included using a form to pass values to a report query...it involved a bit of VBA code, and I don't understand all of the code so I'm reluctant to use it.
Don't be afraid to experiment with something new.
Make a copy of your database and try it in the copy.
That way you can't break the orriginal.
Just be careful that you dont experiment on tables containing live data.

This will ensure rapid progress for you, especially if you couple
your experiments by
first seaching for answers to questions similar to yours
secondly asking a direct question if you can't find an answer.
Oct 15 '09 #3
100+
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [From Date] DateTime,
  2.            [To Date] DateTime;
  3. SELECT     DISTINCTROW
  4.            Format$([HOTSTICK_FAILURE].[DATE_ENTERED],'yyyy-mm') AS [DATE_ENTERED By Month],
  5.            HOTSTICK_FAILURE.COMMODITY_DESC,
  6.            Sum(HOTSTICK_FAILURE.TOTAL_PASSED) AS [Sum Of TOTAL_PASSED],
  7.            Sum(HOTSTICK_FAILURE.QTY_VISUAL_FAIL) AS [Sum Of QTY_VISUAL_FAIL],
  8.            Sum(HOTSTICK_FAILURE.QTY_MECH_FAIL) AS [Sum Of QTY_MECH_FAIL],
  9.            Sum(HOTSTICK_FAILURE.QTY_ELEC_FAIL) AS [Sum Of QTY_ELEC_FAIL]
  10.  
  11. FROM       HOTSTICK_FAILURE
  12.  
  13. GROUP BY   Format$([HOTSTICK_FAILURE].[DATE_ENTERED],'yyyy-mm'),
  14.            HOTSTICK_FAILURE.COMMODITY_DESC
  15.  
  16. HAVING     (((Format$([HOTSTICK_FAILURE].[DATE_ENTERED],'yyyy-mm')) Between [FROM DATE] And [TO DATE]));
I just noticed that the GROUP BY line references the third parameter box. Maybe the solution will be easy. Remove the group by?

THANKS!!!!!!!!
Oct 15 '09 #4
100+
It seems that when I remove DATE_ENTERED from the grouping when I run the report wizard, my problem goes away. I guess the case is closed...for now at least. :-) If you have further advice, feel free to leave it!
THANKS SO MUCH. You people are wonderful.
Oct 15 '09 #5
100+
OH NO. The problem is back, and this time I am NOT using grouping.
As requested earlier, here is my SQL code:
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [From Date] DateTime,
  2.            [To Date] DateTime;
  3. SELECT     DISTINCTROW
  4.            Format$([HOTSTICK_FAILURE].[DATE_ENTERED],'yyyy-mm') AS [DATE_ENTERED By Month],
  5.            HOTSTICK_FAILURE.COMMODITY_DESC,
  6.            Sum(HOTSTICK_FAILURE.TOTAL_PASSED) AS [Sum Of TOTAL_PASSED],
  7.            Sum(HOTSTICK_FAILURE.QTY_VISUAL_FAIL) AS [Sum Of QTY_VISUAL_FAIL],
  8.            Sum(HOTSTICK_FAILURE.QTY_MECH_FAIL) AS [Sum Of QTY_MECH_FAIL],
  9.            Sum(HOTSTICK_FAILURE.QTY_ELEC_FAIL) AS [Sum Of QTY_ELEC_FAIL]
  10.  
  11. FROM       HOTSTICK_FAILURE
  12.  
  13. GROUP BY   Format$([HOTSTICK_FAILURE].[DATE_ENTERED],'yyyy-mm'),
  14.            HOTSTICK_FAILURE.COMMODITY_DESC
  15.  
  16. HAVING     (((Format$([HOTSTICK_FAILURE].[DATE_ENTERED],'yyyy-mm')) Between [FROM DATE] And [TO DATE]));
Not sure why the report is asking me for the third parameter. Please help! Thanks so much, Danica
Oct 15 '09 #6
Delerna
Expert 1GB
Hi Dancia.
Your query is selecting 6 fields and is aggregating 4 of those 6.

Since you have no aggregate fuction on two of the selected fields,
it is an absolute must that those two fields are listed
in the GROUP BY clause. The rule is
ANY selected field that is not being aggregated must appear in
the group by clause or the query will generate an error stating that the field is an invalid selection because it is not aggregated and it is not in the group by.

The 2 fields that must be in the group by clause are
....[HOTSTICK_FAILURE].[DATE_ENTERED]
....[HOTSTICK_FAILURE].[COMMODITY_DESC]


Now to the point.
In post 5 you mention that you removed
[HOTSTICK_FAILURE].[DATE_ENTERED]
from the group by clause.
I presume that it was still in the SELECT list?

If that is the case then you should have gotten something like this error
Column [HOTSTICK_FAILURE].[DATE_ENTERED] is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
You should have got that error IF
[HOTSTICK_FAILURE].[DATE_ENTERED]
is in fact an existing field.

If it is not an existing field then you would not get the above error.
Instead, your query would ask you to provide a value for it.
This is indeed what is happening!

It appears that the query is not recognising
[HOTSTICK_FAILURE].[DATE_ENTERED]
as a legitimate field and is therefore asking you for its value


The table name is exactly the same as the rest so it looks OK.
So, are you sure you have not missspelled the the field name.
are you sure that field actually exists in the table.

Sorry for the lengthy explanation
Oct 15 '09 #7
100+
Delerna,
I am about to leave work for the weekend...so I cannot fully investigate your instructions for a few days. However, I have verified that my field *does* exist and is *not* misspelled. It is clear you are an expert...I will fully investigate your answer Monday. I am just beginning so I'm a little slow anyway. Thanks SO MUCH for your reply! Danica
Oct 15 '09 #8
Delerna
Expert 1GB
PS
What I am about to say is a matter of choice and is suggestion only.
I , and many other developers, use Hungarian Notation when naming database objects. This is a 3 letter prefix on all object names.
there are many standard prefixes.

tbl for tables eg tblHOTSTICK_FAILURE
qry for queries qryHOTSTICK_FAILUREByDateEntered
frm for forms
rpt for reports
btn for buttons
txt for text boxes
opt for option boxes.
the list goes on and on....google Hungarian Notation

What is the point.
As your databases get more and more complicated you will find a name of an object and you wont remember whether it is a table or a query or a report or a form or a button.
This means you will have to search in everything when trying to find HOTSTICK_FAILURE for example.

However, if you called it tblHOTSTICK_FAILURE then you will instantly know it is a table and you will find it in the table definitions


Just a tip....its up to you
Oct 15 '09 #9
100+
I actually think it's a good idea. I can already see this may cause me some problems. Is it too late to do this now? I have been reluctant to rename things (especially field names) because of code that is built around it. I am just starting on reports, if that tells you where I am. (Tables, forms, queries, all done).
Oct 15 '09 #10
NeoPa
Expert Mod 16PB
@Delerna
He may call himself Delerna, but he's clearly learned this pretty well already.

I read that you have double-checked this name, but I wonder if perhaps the '_' character may be incorrect. Sometimes spaces are displayed as underscores when they are formatted as underlined, for instance. Just a thought.

I also need to remind you that the CODE tags are not optional here. It is very much harder to work with any sort of code displayed in a standard web layout.

PS. Tags are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [ CODE ] tags has a hash (#) on it. You can choose which editor to use in your Profile Options (Look near the bottom of the page).
Oct 15 '09 #11
Delerna
Expert 1GB
Maybe more for future projects.
However, If you wanted to take the time to rename in you current project...this should be relatively easy.

Again, this is Access 2003
In the Tools/Options menu, select the general tab.
Halfway down the right side of that tab is some
Name Auto correct options.

If you tick all three of those options then if you rename a table
all queries, forms, reports etc that reference that table will automatically
be renamed for you.
As well as that, a table will be created listing everything that was changed.


If you want to do that, I suggest you do it on a copy of your database
....just in case ;)

I also suggest you rename one thing at a time and then check the log table to see what it did.
Maybe you might then delete those log records before renaming the next thing. Just so the log table dosn't get too big and confusing to read.
Oct 15 '09 #12
100+
Delerna,
I have studied your reply. I ran the report wizard to re-do my report, this time grouping by the two fields you suggested:
....[HOTSTICK_FAILURE].[DATE_ENTERED]
....[HOTSTICK_FAILURE].[COMMODITY_DESC]
However, when I run the report, the same problem is still happening. What is also wierd is no matter what value I put in the third, unwanted parameter [HOTSTICK_FAILURE].[DATE_ENTERED], the same results come to my report.

Also worth mentioning...when I use the wizard and it is done, it lets me open the report from the wizard. When I do this, I get just the 2 parameters I seek. However, when I close it and re-open it from my list of reports, that's when the third parameter enters the picture. Any other ideas?
Oct 19 '09 #13
Delerna
Expert 1GB
Are you able to attach the database or is its data private and sensitive.
Maybe a cut down version of the database with all objects required to emulate the problem.

Please ensure the database you attach is having the same issue.


Dancia, from here it is very difficult to help you unless I can get my hands on the database and trace through the issue.

Another way would be for me to try and guide you through the steps I would take to find out what is causing the problem, but we could be in for a lot of back and forth posting.

First thing to do would be to narrow down where the problem is occuring.
Difficult without seeing the whole structure of your database
but from what you have mentioned I see
A Table .............. A Query ............... A Report
.........................+ 2 params
We know you get the problem when you run the report a second time
We have been assuming that the problem is in the query...but is it?

What happens if you run the query 2...3...4 times?
If no problem doing that, the the problem is actually in the report and
this means we can forget checking out the query.

If the problem does appear when running the query then our assumption was correct and there is no need to check out the report

can you post a screenshot of the table in design mode if you can't attach the database.
Try and show all field names.
Oct 19 '09 #14
100+
Delerna,
Thanks again for your informative reply. I just realized I could post the database. So it's here now. I do not believe the problem lies within the query. I can run the query successfully as much as I want to. (I am also inexperienced so take my word for what it is worth.) The HOTSTICK_FAILURE_BY_DATE is the query and report name you probably want to look at. Also, I have another double parameter query working correctly that contains one summed field and I compared the SQL codes for both the one that won't work and the one that did and I didn't see anything different except the additional fields listed in my non-working query. I am really trying to learn here and I have learned a lot helping you help me. The similar query/report that *is* working is named NEW_HOTSTICKS_BY_DATE. I have tried to be clear with my naming so hopefully you can follow it. Both the working and non-working queries were created the same way...through the report wizard. Once again, thanks for your help and *patience*. :-)
PS. Good values for parameters are FROM DATE "1/1/09" TO DATE "10/10/09".
Attached Files
File Type: zip SHOPS1019.zip (149.4 KB, 85 views)
Oct 20 '09 #15
Delerna
Expert 1GB
Hi again Dancia
Well I tried running both the query and the report multiple times and they both ran every time with no problem ????

Perhaps if you spell out exactly what you do, step by step, when you get the error.
Oct 20 '09 #16
Delerna
Expert 1GB
Nope, I can't for the life of me make it do it.

It works perfectly.....everytime??????
Oct 20 '09 #17
100+
LOL. Just my luck.

Open database.
All Access objects are open (left hand side).
In Report section, open rptHOTSTICK_FAILURE_BY_DATE
Program immediately prompts for first parameter. Enter “1/1/09”
Program immediately prompts for second parameter. Enter “10/10/09”
Program immediately prompts for third, unwanted parameter [HOTSTICK_FAILURE.DATE_ENTERED]. You can just click “ok”…it doesn’t matter if you enter anything or not.
One final thing I just thought about. I am using Access 2007, but my (large) company set it up where it saves it in Access 2003 format, so people who haven’t upgraded can still use files.

If this is not the way I should make the report run, let me know. In the meantime, I will try it on another computer in both 03 and 07 formats.

Thanks again!
Oct 20 '09 #18
100+
I have confirmed that when I open this using Access 2003, it does work perfectly, every time. When I open it in Access 2007, it does not. I have tried Access 2007 on two different computers with no luck. ???
Oct 20 '09 #19
NeoPa
Expert Mod 16PB
Ah. An Access 2007 problem. I don't know much about that myself but I know a number of people have found Interactive: Access 2003 to Access 2007 command reference guide helpful.
Oct 20 '09 #20
Delerna
Expert 1GB
sorry, Ive said it before.
I hate access 2007
One more reason for me to continue doing so
Oct 20 '09 #21
Delerna
Expert 1GB
Dancia
I posted here so I don't discourage 2007 experts answering your other call for help.

I just rememered that I have acces 2007 installed on my wifes computer.
I will take your database home with me tonight and see what I can discover and post back tomorrow.

If I find an answer I will post it in the other thread.

Providing I haven't forgotten by the time I get home :)
Oct 21 '09 #22
NeoPa
Expert Mod 16PB
That other thread being parameter problem in Access 2007 reports?. It's probably best to use that other one now for answering the question. If answers were posted in here we'd suffer from one of the problems of double-posting. I didn't treat the new thread as a double-post as I saw the sense in separating it out, but this thread still shouldn't be used for answering the same question as the other. Does that make sense to you both?
Oct 22 '09 #23
Delerna
Expert 1GB
I posted here so I don't discourage 2007 experts answering your other call for help.
If I find an answer I will post it in the other thread.
.................................................. .................................................. ...............
Oct 22 '09 #24
NeoPa
Expert Mod 16PB
You're absolutely right Delerna. I missed that somehow. Nevertheless, I wanted something in here visible so that all others that might browse through would realise too ;)
Oct 22 '09 #25
100+
Yes, this makes sense to me too. And continued thanks to you both.
Oct 22 '09 #26

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by tlyczko | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.