#  Other Applications & Softwares  > Access Tables & Databases >  >  Generate Selected Reports

## phatus

hi again its me... i know how to make reports in ms access i have 12781 in my records consist of 6 columns namely col1 Id, col2 Student, col3 Gender, col4 school, col5 District and col6 Grade level, my problem is....

for example there are 6 different district namely District 1, District 2,District 3 bla bla bla...
and i want to view a report only district 2, only those student in district 2 will appear in the report.. is it possible... because my report only shows the Duplicate Entries.... and i dont know how to configure if i need the report only in district 2 or 3 or 4 or 1 or what so ever..... i try to google it but i dont know that is the command on it... thanks again......

hope you get my point...

----------


## alansidman

Before you create your report, create a query with the criteria for each field as you want it.  Then use the query as the recordset for your report instead of your table.  If this is something your users will be doing, you can make it easier to establish the criteria by using a search form.  Look at this tutorial for an the concept.

http://www.datapigtechnologies.com/f...earchform.html

Alan

----------


## phatus

@alansidman 

 i tried the link you gave me... i cant get the part where setting up query, im using access07 in the criteria part when i put the "*" im getting an error when saving the query i did what on the video exactly... do you have 07 guide?

----------


## alansidman

There is no 2007 guide.  The information is the same.  It is the QBE and SQL Statement.  They haven't changed.  Only the UI is different and if you can reach the QBE then you should be able to execute.  If you have an issue, post your SQL statement so that we can see what you are doing and where to correct it.

Alan

----------


## phatus

ok sir i will... its holiday today and tomorrow, ill post in when im back at the office.. thanks again.

----------


## phatus

ok here what i have done pls check the images... when i click clear the field dont clear, then when i click the run query nothing happend too....

\1

\1

----------


## alansidman

I don't see where you have created a query.  Looks like you are trying to open a table with parameters.  You need to create a query.  Use the Query Builder.

Look at this for how to build a select query.

http://support.microsoft.com/kb/304361
and 
http://www.techonthenet.com/access/queries/index.php


Alan

----------


## phatus

sir the query name is List of Students Query as you can see in the screenshot.. im trying to attache the file it self but it seems i cant attache it i put it in rar files already...

http://www.mediafire.com/?ewbfiaqwdwwqi08  sir thats the link to my data base maybe you can look at it ..

thanks a lot very much appreciated for the help...

----------


## alansidman

I looked at your db.  I could not make any changes to it--not sure why, but you must have some sort of exclusive setting.

In your query you have your fields pointing to a form named "searcform" but this form does not exist.  You need to change your criteria to reflect the name of your seach form "Form1" or change the name of your search form.  

Additionally, there is no code behind the Run button to open the query.  You will need to put a DoCmd there to open it.

good luck with this.

Also, in your code for Clear the query, add this to your code  "Me.requery"  without the quote marks

Alan

----------


## phatus

ok sir  ill try... ill post the result...

----------


## phatus

@alansidman hi im fin edting my data base it seems my button dont work really can you look at it again you can edit it now.. i didnt miss anything in the guide the query name is "maintable", table name"maintable".. when i click the query maintable a "Enter Parameter Value" appear i think the error is in the criteria.. can you look at the dbase? you can edit it now sir...

Here is the dl link sir....

http://rapidshare.com/files/428409081/Final.accdb

hope to hear from you again... if you can make the button run can you do it so that i can study it and see whats my error thanx...

----------


## alansidman

Ok.  A Couple of things.  You are getting the parameter request, because you have the wrong reference in your criteria in your form.  You need to reference your search form and not the input form.  Your search form is where you are starting your query.  Additionally, in your search form Run button, you have not named your query correctly.  It references a non existent query.  You need to pay close attention to what you are refeencing in your code  and criteria.  You seem to have difficulty in doing this.   Also this file appears to be bloated.  I would suggest you do a compact and repair on close.  It took almost five minutes to download this file. 

Alan

----------


## phatus

yes im not good in access im just starting to learn it.. step by step in the internet... sorry for that... can u fix it sir?... thanks i again your very much helpful... and attached again the file

----------


## alansidman

Your db will not let me save any changes.  You will have to make all changes. We are all new at something at some time or another.  You will have to learn to pay close attention to the details.  As a general point, when you get a parameter box pop up and you were not expecting one, it means that there is a field name in your query that Access cannot find in your tables or forms.  That is your hint to look at your criteria to make sure that it is spelled correctly.

Alan

----------


## phatus

i got the query correct when i run my maintablequery with the run mode all the names appear, the paramater value did not appear, hope its a good sign,now i think my problem is the VB code... even the clear button dont work i double check the code its correct here is my code in clear

Private Sub Command6_Click()

Me.qstudents.Value = ""
Me.qschool.Value = ""
Me.Requery

End Sub

i also try this code

Private Sub Command6_Click()

Me.qstudents.Value = ""
Me.qschool.Value = ""

End Sub

still no effect... 

my criteria is:  Like [Forms]![Searchform].[qstudents] & "*"  and Like [Forms]![Searchform].[qschool] & "*"
qstudents is the name of the blankfield in Search form and same in qschool.

note: when i write the criteria, when im in Like Forms!"2 option appear here" 1st option Formsearch, 2nd optiion List of students... i tried list of students it bring me the enter parameter value, when i select searchform when i run it it shows me all the records in my maintable "List of Students Table" .
because i have 2 forms...

even the run command dont effect here is my code in run

Private Sub Command7_Click()

DoCmd.OpenQuery "maintable", acViewNormal

End Sub

in my understanding in the DoCmd.OpenQuery "your query table name", acViewNormal
my Query table name is maintable... but it seems it dint open the query table... i make only 2 indexs which are students field and school field....

am i writing the correct code?

----------


## alansidman

I really don't know what is happening.  Are you able to post your db to this site.  Prefer if you only populate it with sample data and not all your data.  The last time it took 5 minutes to download.  Zip it and then attach it to your next message. 

I think you need to look that the clear code is linked to the correct button.  Is your query named correctly in your run code?  

I don't understand what you are trying to say here.  It does not make any sense.



```
Please Login or Register  to view this content.
```


Alan

----------


## phatus

ok here is the sample can u check it and if possible if you have to make changes kindly make it... this is my last part of my  dbase.... thanx a lot for helping sir alan

im just going to study your changes to aply it in my main dbse sir...

----------


## alansidman

Ok.  I think that I have solved your issues.
I believe that your dB is corrupted.  It will not let me save any changes to it and it will not close completely when it closes.  You should try to do the following;  Run a compact and repair.

Your query would not work because in your DoCmd.OpenQuery line you had the name of the query as "maintable".  When I looked at your db, you had actually named your query "Maintable Query."  This is the type of stuff I was referring to when I said you need to pay close attention to the details.

As to the clear the filter button, I added back the Me.requery and it runs just fine. 

As to getting a parameter box when you run the query, this will happen when you run the query independently of the search form.  If the search form is not used, then the query is looking to that form for data inserted in the text boxes you refer to in the query.  

Lastly, to get this to work on my machine, I had to create a new dB and import your form, table and query into that dB.  You may wish to do the same, since I strongly believe that your dB is corrupt.

Good luck 

Alan

----------


## phatus

thanks it solve my problem now i understand i thought the "query" i not included in the details thats why i put only maintable now i understand and also i found out that the button is ot working because theres  a security it was being disable...

you help me a lot sir... thank you very much...

----------

