472,980 Members | 1,990 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,980 software developers and data experts.

Problem with my chart in MS Access 2002 using a form and VB

Hi everyone,

I am new to Access and Visual Basic so things my be getting across a
bit cloudy. I only started using VB for one week.

I am having a little problem with the database that I am working on. I
am working with MS Access 2002. And I am having a problem with one of
my charts.

I will explain how everything is laid out then go into details.
The chart was created into a report and it is getting its information
from a query.
Here is the flow of information:

- Using a form I select a Start Date and an End Date. (these dates are
to limit the query to the data needed)
- When I click a command on that form, I have a VBA Function that runs
a set of validations and then it opens the Report with carrying over a
WHERE statement.
- Once the Report opens it begins to gather the needed info from the
query and limiting it with the WHERE statement from the Form(VBA) code.

Now onto the Report I have set up Record Source to point to that Query
where it gets the information from. And there are fields(text boxes) on
the Report that display the correct information pulled from that query
from the two dates provided from the form.

Now the problem is with the Chart on that same Report. It doesn't
seem to be using the WHERE statement sent to the query. It is
retrieving the information from the correct query. But the Chart will
display the information for every record in the database. It is suppose
to only display in the chart the information between the two dates sent
to the query from the form.

I know that I am bad for explaining things, sorry...

I noticed that there is another query into the Chart. Am I suppose to
modify this one too?

Here are some details:
=======================================
This is my VBA Procedure for the buttons On Click:
---------------------------------------
Private Sub Load_Report_Click()

Dim strWhere As String
Dim MyCheck
Dim MyCheckNull
Dim MyCheckNull2

On Local Error GoTo ErrorHandler

ErrorHandler:

If Err = 3075 Then

MsgBox ("Wrong Date Format used. Please use list selection")
Err.Clear

Else

MyCheckNull2 = IsNull([start_date])

If MyCheckNull2 Then

MsgBox ("Start and/or End Date box cannot be empty")

Else

MyCheckNull = IsNull([end_date])

If MyCheckNull Then

MsgBox ("Start and/or End Date box cannot be empty")

Else

MyCheck = end_date <= start_date

If MyCheck Then

MsgBox ("The **End Date** must me later that the
**Start Date**")

Else

strWhere = "[Log.Date_Received] Between #" &
[start_date] & "# AND #" & [end_date] & "#"
DoCmd.OpenReport "Directorate_Total_Chart",
acViewPreview, , strWhere

End If
End If
End If
End If
End Sub

=======================================
This is code for my query being used to retrieve that data. And the
strWhere variable being sent to the query is directed to the
Date_Received field:
---------------------------------------
SELECT Log.LogID, Log.DeveloperName, Directorate.Directorate_Group,
Log.Directorate_Name, Directorate.Directorate_Full_Name,
Log.Date_Received, Log.Date_Assigned, Log.Date_Completed,
Log.Num_Wdays, Log.Num_Days_AC, Log.Work_Estimate, Log.Num_Days_RC,
Log.By_updated, Log.Date_updated, First(Log.Date_Received) AS
FirstOfDate_Received, Last(Log.Date_Received)+356 AS
LastOfDate_Received, Count(Log.Date_Received) AS CountOfDate_Received,
Directorate_Groups.Group_Acro
FROM (Directorate_Groups INNER JOIN Directorate ON
Directorate_Groups.Group_Name = Directorate.Directorate_Group) INNER
JOIN Log ON Directorate.Directorate_Name = Log.Directorate_Name
GROUP BY Log.LogID, Log.DeveloperName, Directorate.Directorate_Group,
Log.Directorate_Name, Directorate.Directorate_Full_Name,
Log.Date_Received, Log.Date_Assigned, Log.Date_Completed,
Log.Num_Wdays, Log.Num_Days_AC, Log.Work_Estimate, Log.Num_Days_RC,
Log.By_updated, Log.Date_updated, Directorate_Groups.Group_Acro;
=======================================

Sorry for the mess. But I am still new to this stuff.

Finally, the code that I noticed in the Row Source of my Chart is as
follows:
---------------------------------------
SELECT [Group_Acro],Sum([CountOfDate_Received]) AS [Total:]
FROM [Directorate_Totals]
GROUP BY [Group_Acro];
=======================================
So like I had mentioned earlier. I know that the query is receiving and
properly using the Where Condition from my form because all other data
on my report is being displayed correctly. It is the chart in that same
report that doesn't seem to be using the WHERE Condition. But it is
retrieving the information, just not between the two dates that I
provided.

One final note that may help. If I were to add the condition BETWEEN
#2004/04/01# AND #2005/03/31# to the main query, the chart does display
correct information for those dates. But this is not what I want. I
want the user to be able to select dates from a form.

Thanks for your help. I hope I gave enough information. If more is
needed just let me know. And sorry for the lengthy request. It is my
first post on a user group.

Ciao,

Steven

Nov 13 '05 #1
3 3357
You should set up your chart's row source query with criteria based on the
start and end dates from the form.
SELECT [Group_Acro],Sum([CountOfDate_Received]) AS [Total:]
FROM [Directorate_Totals]
WHERE [DateField] Between Forms!frmYourForm![StartDate] AND
Forms!frmYourForm![EndDate]
GROUP BY [Group_Acro];

--
Duane Hookom
MS Access MVP
"StBond" <we*******@cidcanada.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Hi everyone,

I am new to Access and Visual Basic so things my be getting across a
bit cloudy. I only started using VB for one week.

I am having a little problem with the database that I am working on. I
am working with MS Access 2002. And I am having a problem with one of
my charts.

I will explain how everything is laid out then go into details.
The chart was created into a report and it is getting its information
from a query.
Here is the flow of information:

- Using a form I select a Start Date and an End Date. (these dates are
to limit the query to the data needed)
- When I click a command on that form, I have a VBA Function that runs
a set of validations and then it opens the Report with carrying over a
WHERE statement.
- Once the Report opens it begins to gather the needed info from the
query and limiting it with the WHERE statement from the Form(VBA) code.

Now onto the Report I have set up Record Source to point to that Query
where it gets the information from. And there are fields(text boxes) on
the Report that display the correct information pulled from that query
from the two dates provided from the form.

Now the problem is with the Chart on that same Report. It doesn't
seem to be using the WHERE statement sent to the query. It is
retrieving the information from the correct query. But the Chart will
display the information for every record in the database. It is suppose
to only display in the chart the information between the two dates sent
to the query from the form.

I know that I am bad for explaining things, sorry...

I noticed that there is another query into the Chart. Am I suppose to
modify this one too?

Here are some details:
=======================================
This is my VBA Procedure for the buttons On Click:
---------------------------------------
Private Sub Load_Report_Click()

Dim strWhere As String
Dim MyCheck
Dim MyCheckNull
Dim MyCheckNull2

On Local Error GoTo ErrorHandler

ErrorHandler:

If Err = 3075 Then

MsgBox ("Wrong Date Format used. Please use list selection")
Err.Clear

Else

MyCheckNull2 = IsNull([start_date])

If MyCheckNull2 Then

MsgBox ("Start and/or End Date box cannot be empty")

Else

MyCheckNull = IsNull([end_date])

If MyCheckNull Then

MsgBox ("Start and/or End Date box cannot be empty")

Else

MyCheck = end_date <= start_date

If MyCheck Then

MsgBox ("The **End Date** must me later that the
**Start Date**")

Else

strWhere = "[Log.Date_Received] Between #" &
[start_date] & "# AND #" & [end_date] & "#"
DoCmd.OpenReport "Directorate_Total_Chart",
acViewPreview, , strWhere

End If
End If
End If
End If
End Sub

=======================================
This is code for my query being used to retrieve that data. And the
strWhere variable being sent to the query is directed to the
Date_Received field:
---------------------------------------
SELECT Log.LogID, Log.DeveloperName, Directorate.Directorate_Group,
Log.Directorate_Name, Directorate.Directorate_Full_Name,
Log.Date_Received, Log.Date_Assigned, Log.Date_Completed,
Log.Num_Wdays, Log.Num_Days_AC, Log.Work_Estimate, Log.Num_Days_RC,
Log.By_updated, Log.Date_updated, First(Log.Date_Received) AS
FirstOfDate_Received, Last(Log.Date_Received)+356 AS
LastOfDate_Received, Count(Log.Date_Received) AS CountOfDate_Received,
Directorate_Groups.Group_Acro
FROM (Directorate_Groups INNER JOIN Directorate ON
Directorate_Groups.Group_Name = Directorate.Directorate_Group) INNER
JOIN Log ON Directorate.Directorate_Name = Log.Directorate_Name
GROUP BY Log.LogID, Log.DeveloperName, Directorate.Directorate_Group,
Log.Directorate_Name, Directorate.Directorate_Full_Name,
Log.Date_Received, Log.Date_Assigned, Log.Date_Completed,
Log.Num_Wdays, Log.Num_Days_AC, Log.Work_Estimate, Log.Num_Days_RC,
Log.By_updated, Log.Date_updated, Directorate_Groups.Group_Acro;
=======================================

Sorry for the mess. But I am still new to this stuff.

Finally, the code that I noticed in the Row Source of my Chart is as
follows:
---------------------------------------
SELECT [Group_Acro],Sum([CountOfDate_Received]) AS [Total:]
FROM [Directorate_Totals]
GROUP BY [Group_Acro];
=======================================
So like I had mentioned earlier. I know that the query is receiving and
properly using the Where Condition from my form because all other data
on my report is being displayed correctly. It is the chart in that same
report that doesn't seem to be using the WHERE Condition. But it is
retrieving the information, just not between the two dates that I
provided.

One final note that may help. If I were to add the condition BETWEEN
#2004/04/01# AND #2005/03/31# to the main query, the chart does display
correct information for those dates. But this is not what I want. I
want the user to be able to select dates from a form.

Thanks for your help. I hope I gave enough information. If more is
needed just let me know. And sorry for the lengthy request. It is my
first post on a user group.

Ciao,

Steven

Nov 13 '05 #2
WOW... It worked like a charm...
I entered the proper field names and added it to my chart and it
works...

Thank-you, thank-you, thank-you...

I've been bashing my head on the desk here at work all way on this one.
After 5 hours of swearing at it, I decided to try here...
And you respond back within one hour with the exact fix...

This made my day. Seems too good to be true

Thanks again, and have a great weekend. =)

Steven

Nov 13 '05 #3
Stop bashing your head in the future. Search Google Groups first and then
come here.

--
Duane Hookom
MS Access MVP
--

"StBond" <we*******@cidcanada.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
WOW... It worked like a charm...
I entered the proper field names and added it to my chart and it
works...

Thank-you, thank-you, thank-you...

I've been bashing my head on the desk here at work all way on this one.
After 5 hours of swearing at it, I decided to try here...
And you respond back within one hour with the exact fix...

This made my day. Seems too good to be true

Thanks again, and have a great weekend. =)

Steven

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

20
by: John | last post by:
Hi, I've recently upgraded from Access 97 to Access 2002 & the performance basically stinks. I have tried the following items listed below but it has only had a minor impact: 1) Upgraded Jet...
0
by: Neal Wessler | last post by:
I've created a Chart in Access97. It is a time function of a variable. When the dates along the x axis are within the same year - no problem. e.g. Apr-03 May-03 Jun-03...........Dec-03. ...
2
by: Dave | last post by:
I'm trying to follow the examples in VBAGRP9.CHM and/or VBAGR10.CHM with no success. I'm trying to Chage the Legend Colors based on Content so that I can establish a certain color format on...
9
by: Patrick.O.Ige | last post by:
I have a code below and its a PIE & BAR CHART. The values now are all static but I want to be able to pull the values from a database. Can you guys give me some ideas to do this? Thanks ...
2
by: chadw | last post by:
I am needing to chart miles per gallon by vehicle. The database structure is going to return rows like this: VEHICLE|MPG|DATE (where | is a column separator) 2003 Truck|17|11-1-04 2003...
1
by: devagupt | last post by:
i have a form called get downtime data. WHen information is entered into it and the button "get downtime data" pressed , it displays the results of a query. WHen i view the query in pivot chart view...
1
by: Wedgewood | last post by:
Group, I am trying to build a gantt chart in a MS Access for but I can't seem to get it to pick up the second data series. In excel, you create a stacked bar chart using fields "Key Date" and...
1
by: il0postino | last post by:
Apologies in advance for this newbie question! I have an Access form with an unbound embedded Excel chart on it(Called, OLEUnbound39) (Done on Access form by Insert > Object > Microsoft excel...
14
by: Wayne | last post by:
I posted about this problem over 12 months ago and even after Vista SP1 the problem still exists when running an Access database (A2003 and A2007 tested) under Vista. A workaround was suggested by...
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.