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

StBond

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

Duane Hookom

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

StBond

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

Duane Hookom

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