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
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
25  4402 
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
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.
-
PARAMETERS [From Date] DateTime,
-
[To Date] DateTime;
-
SELECT DISTINCTROW
-
Format$([HOTSTICK_FAILURE].[DATE_ENTERED],'yyyy-mm') AS [DATE_ENTERED By Month],
-
HOTSTICK_FAILURE.COMMODITY_DESC,
-
Sum(HOTSTICK_FAILURE.TOTAL_PASSED) AS [Sum Of TOTAL_PASSED],
-
Sum(HOTSTICK_FAILURE.QTY_VISUAL_FAIL) AS [Sum Of QTY_VISUAL_FAIL],
-
Sum(HOTSTICK_FAILURE.QTY_MECH_FAIL) AS [Sum Of QTY_MECH_FAIL],
-
Sum(HOTSTICK_FAILURE.QTY_ELEC_FAIL) AS [Sum Of QTY_ELEC_FAIL]
-
-
FROM HOTSTICK_FAILURE
-
-
GROUP BY Format$([HOTSTICK_FAILURE].[DATE_ENTERED],'yyyy-mm'),
-
HOTSTICK_FAILURE.COMMODITY_DESC
-
-
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!!!!!!!!
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.
OH NO. The problem is back, and this time I am NOT using grouping.
As requested earlier, here is my SQL code: -
PARAMETERS [From Date] DateTime,
-
[To Date] DateTime;
-
SELECT DISTINCTROW
-
Format$([HOTSTICK_FAILURE].[DATE_ENTERED],'yyyy-mm') AS [DATE_ENTERED By Month],
-
HOTSTICK_FAILURE.COMMODITY_DESC,
-
Sum(HOTSTICK_FAILURE.TOTAL_PASSED) AS [Sum Of TOTAL_PASSED],
-
Sum(HOTSTICK_FAILURE.QTY_VISUAL_FAIL) AS [Sum Of QTY_VISUAL_FAIL],
-
Sum(HOTSTICK_FAILURE.QTY_MECH_FAIL) AS [Sum Of QTY_MECH_FAIL],
-
Sum(HOTSTICK_FAILURE.QTY_ELEC_FAIL) AS [Sum Of QTY_ELEC_FAIL]
-
-
FROM HOTSTICK_FAILURE
-
-
GROUP BY Format$([HOTSTICK_FAILURE].[DATE_ENTERED],'yyyy-mm'),
-
HOTSTICK_FAILURE.COMMODITY_DESC
-
-
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
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
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
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
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).
NeoPa 32,556
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).
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.
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?
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.
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".
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.
Nope, I can't for the life of me make it do it.
It works perfectly.....everytime??????
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!
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. ???
sorry, Ive said it before.
I hate access 2007
One more reason for me to continue doing so
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 :)
NeoPa 32,556
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?
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.
.................................................. .................................................. ...............
NeoPa 32,556
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 ;)
Yes, this makes sense to me too. And continued thanks to you both.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Laszlo Csabi |
last post by:
Hi Folks,
Can someone explain me why I'm getting the following error?
The error message I got is : "Parameter count does not match Parameter Value count."
|
by: tlyczko |
last post by:
Can anyone give me a website reference(s) wherein I can learn how to
use a form and parameter queries to generate an Access report *without*
having to reference the form or hard-code the query in...
|
by: Nemisis |
last post by:
Hi,
Is it possible to pass in an object and parameter into a function and
return it as a string.
i.e.
To make a call to the function i would put the following
Dim str as String =...
|
by: Dave |
last post by:
I need to filter an Access 2000 result set in ASP 30 using the ADO
recordset.filter.
I build the filter in pieces. The first clause of the filter is this...
WHERE word LIKE 'S%'
... to...
|
by: mikeatindo |
last post by:
I can produce parameter queries in Access but can not find how to do the same thing in Mysql. I am developing a web database (Mysql v5.02) where I want the user to choose which field or fields to...
|
by: Dave |
last post by:
I am using some recipes to do some calculation. But there are
situation that I need to give extra parameters to the existing
function. For example, the recipe has a function called:
foo (int x,...
|
by: dbuchanan |
last post by:
I need information on how to programmatically access a named parameter
within an RDLC file associated with a ReportViewer in local mode.
I also want to verify that a parameter set up like this...
|
by: BigMac4 |
last post by:
Ok i am going to sound like the novice i am but i am hoping that someone will take pity on me and help me out.
Here is what i am trying to acheive:
I have a report that i need to run using a...
|
by: Gina Louise |
last post by:
My database is essentially a big diary for lots of trainers so we can plan future training dates and record past training. There are a variety of queries we need to run on this data, e.g. look up the...
|
by: miss D |
last post by:
hi,
i have posted this question here, regarding Access SQL
i believe someone here is good in SQL. anyone can help me?
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
