Just found another problem...

I have a table called 'tblTestBuyBA01' with the fields 'Inputdate', 'Criteria1', 'Criteria2' 'Criteria3' 'Criteria4' 'Criteria5'. The 5 criteria fields works with combo boxes where the user has to choose between the values 'Yes' and 'No'. There is a thirth default value 'Empty'.

This table is used as a checklist.

The values in the table looks like:

Inputdate Criteria1 Criteria2 Criteria3 Criteria4 Criteria5
12/02/2007 Yes Empty Yes Yes No
12/02/2007 Yes Empty Empty No No
13/02/2007 Empty Yes Yes Yes Yes
15/02/2007 No No No Yes No
16/02/2007 Yes No No Yes Yes

Now, what I would like to have is that I create a chart-report with this data.
The data should be grouped by the values 'Yes', 'No' and 'Empty'.
I would like to have a chart with 5 colums. Column 1 for Criteria1,... till 5.

So in data the chart should look like this:

        Criteria1 Criteria2 Criteria3 Criteria4 Criteria5
Yes 60% 20% 40% 80% 40%
No 20% 40% 40% 20% 60%
Empty 20% 40% 20% 0% 0%

This is how the table would look like to make a chart in Excel. The criteria should become the X-axis in the chart, the yes no empty values in the legend and the % as a Y-Axis.

I don't know if this is clear for you guys, but I'm can't seem to find a way to create this...

You can do this, but you need to hide the values. Then create labels, color them, and set their relative heights using the values in the criteria, and twips. You can't really do this with a chart. Charts in Access are useless.

Is this report in your other database? I won't be able to do anthing till Tuesday.

mmcdonal

It is indeed in the same file that I have send to you.

If you want you can try to create this one too, but I'm going to try to make it on my own with your suggestion...

You probably will not be able to check the results, because the file I have send to you contains almost no values in the tables...

ok, I have tried to make this work on my own, but I still have a little problem. I can't seem to insert the values in the unbound form. The code I'm using for this is:

Private Sub Form_Load()

Me.PtagYes.Value = DCount("[tblTestBuyBA01]![Ptag]", "tblTestBuyBA01", "[tblTestBuyBA01]![Ptag] = Ja/Oui And [tblTestBuyBA01]![Invoerdatum] >= [Forms]![frmPercentTestBuyIntermediate]![Begindatum] And [tblTestBuyBA01]![Invoerdatum] <= [Forms]![frmPercentTestBuyIntermediate]![Einddatum]")

End Sub

I have tried to move the " in several positions in the criteria part of the expression, but I can't seem to find the right one... It still always tells me there is an error.

Also when you said to pass the value to the height, I don't know if the code I wrote will be correct... (what I can't check, since I can't get the values to appear on the form)

Private Sub Form_Open(Cancel As Integer)

Me.Graph_PtagYes.Height = Me.PTagYesGraph.Value
Me.Graph_PtagNo.Height = Me.PTagNoGraph.Value
Me.Graph_PTagEmpty.Height = Me.PTagEmptyGraph.Value
Me.Graph_VolledigYes.Height = Me.VolledigYesGraph.Value
Me.Graph_VolledigNo.Height = Me.VolledigNoGraph.Value
Me.Graph_VolledigEmpty.Height = Me.VolledigEmptyGraph.Value
Me.Graph_KwaliteitenYes.Height = Me.KwaliteitenYesGraph.Value
Me.Graph_KwaliteitenNo.Height = Me.KwaliteitenNoGraph.Value
Me.Graph_KwaliteitenEmpty.Height = Me.KwaliteitenEmptyGraph.Value
Me.Graph_KoopinstYes.Height = Me.KoopinstYesGraph.Value
Me.Graph_KoopinstNo.Height = Me.KoopinstNoGraph.Value
Me.Graph_KoopinstEmpty.Height = Me.KoopinstEmptyGraph.Value
Me.Graph_VoorradigYes.Height = Me.VoorradigYesGraph.Value
Me.Graph_VoorradigNo.Height = Me.VoorradigNoGraph.Value
Me.Graph_VoorradigEmpty.Height = Me.VoorradigEmptyGraph.Value

End Sub

Don't know if this is clear for you and if you have enough information here...

1. What are you trying to do with this?
Me.PtagYes.Value = DCount("[tblTestBuyBA01]![Ptag]", "tblTestBuyBA01", "[tblTestBuyBA01]![Ptag] = Ja/Oui And [tblTestBuyBA01]![Invoerdatum] >= [Forms]![frmPercentTestBuyIntermediate]![Begindatum] And [tblTestBuyBA01]![Invoerdatum] <= [Forms]![frmPercentTestBuyIntermediate]![Einddatum]")

Write the psuedo code.

2. The reason you are not getting the heights to appear in your form is that you need to specify the height in Twips. There are 1440 twips per inch. So you might write something like if you were trying to get the graph to show up in a section that was 5" high:

Me.Graph_PtagYes.Height = ((Me.PTagYesGraph.Value * (1440 * 5))

Did that work?



mmcdonal

What I am trying to do with the DCount is to get the values into the form to be able to calculate the percentages...

I have the 5 criteria and 3 possible options per criteria (Yes, No and Empty).

I need to make a chart that shows what the percentage is for the yes, the no and the empty in every criteria for a given period of time...

So in the exemple I have given you, this would only be to count the first value (the Yes in criteria 1).

To say this another way, I have to know in how many records for criteria 1 that I have the "Yes". This should be between a given startdate and enddate that can be found on the [Forms]![frmPercentTestBuyIntermediate] form. Dates are called "begindatum" for "startdate" and "einddatum" for "enddate" on this form. I have tried to do this first in the text box, but when the form opens I get a "#name" error in every text box... So I decided to try changing this into VBA, but seems like there is something wrong in the code...

Is this clear for you or not???

thanks

I changed the code for the height, but I can't check it, because I first need to have the values inserted with the VBA code that is wrong...

The height is in centimeters in my system and not with inches. The number that is the value of me.PTagYesGraph is already a number between 0 and 10. The graphics maximum height should be 10 centimeters for 100%. Do you think the code would be correct if I change it like this?

Me.Graph_PtagYes.Height = (Me.PTagYesGraph.Value * (1440 * 10))

A twip is a twip, regardless of the unit of measurement. I think if you did this to try it:

Me.Graph_PtagYes.Height = (Me.PTagYesGraph.Value * (567 * 10))

mmcdonal

Ok, I'll give it a try.

Do you have any idea about the

Me.PtagYes.Value = DCount("[tblTestBuyBA01]![Ptag]", "tblTestBuyBA01", "[tblTestBuyBA01]![Ptag] = Ja/Oui And [tblTestBuyBA01]![Invoerdatum] >= [Forms]![frmPercentTestBuyIntermediate]![Begindatum] And [tblTestBuyBA01]![Invoerdatum] <= [Forms]![frmPercentTestBuyIntermediate]![Einddatum]")

to insert the values in the form? Is this code correct (I guess not or it would work...) or what should I change to make it work?