set graph resource and define date criteria vba

[aman]

Hi All

I have a form and there is a graph on it which needs to be filtered using the date range on the form . When I set the row source property of the graph in the property sheet then I don't receive any error message but if the row source is set on the click event of search button then when the form is loaded the following error message appears. Also how can I set the date range criteria for the graph?

"An error occurred while sending data to the OLE server"

The graph is linked to the following query 'qry_RMS_Dashboard_Reasons_Final' which is defined as below:

TRANSFORM Sum(qry_RMS_Dashboard_Reasons.SumOfTimes) AS SumOfSumOfTimes
SELECT qry_RMS_Dashboard_Reasons.[Staff Name]
FROM qry_RMS_Dashboard_Reasons
GROUP BY qry_RMS_Dashboard_Reasons.[Staff Number], qry_RMS_Dashboard_Reasons.[Staff Name], qry_RMS_Dashboard_Reasons.[Staff Name]
ORDER BY qry_RMS_Dashboard_Reasons.[Staff Name]
PIVOT qry_RMS_Dashboard_Reasons.[1stRefuseReason];

The query qry_RMS_Dashboard_Reasons is as below:

SELECT qry_RMS_UnionReasons.[Staff Name], qry_RMS_UnionReasons.[1stRefuseReason], Sum(qry_RMS_UnionReasons.Times) AS SumOfTimes, qry_RMS_UnionReasons.[Staff Number]
FROM qry_RMS_UnionReasons
GROUP BY qry_RMS_UnionReasons.[Staff Name], qry_RMS_UnionReasons.[1stRefuseReason], qry_RMS_UnionReasons.[Staff Number];

The query qry_RMS_UnionReasons is as below:

SELECT *
FROM qry_RMS_ReturnReasons_1 UNION SELECT * FROM  qry_RMS_ReturnReasons_2;

The query qry_RMS_ReturnReasons_1 is as below:

SELECT tblstaff.[Staff Name], tbl_RMS_Paperless1.[1stRefuseReason], Count(tbl_RMS_Paperless1.[1stRefuseReason]) AS Times, tblstaff.[Staff Number]
FROM tbl_RMS_Paperless1 INNER JOIN tblstaff ON tbl_RMS_Paperless1.SupervisorRef = tblstaff.[Staff Number]
GROUP BY tblstaff.[Staff Name], tbl_RMS_Paperless1.[1stRefuseReason], tblstaff.[Staff Number]
HAVING (((tbl_RMS_Paperless1.[1stRefuseReason]) Is Not Null))
ORDER BY Count(tbl_RMS_Paperless1.[1stRefuseReason]) DESC;

The query qry_RMS_ReturnReasons_2 is as below:

SELECT tblstaff.[Staff Name], tbl_RMS_Paperless1.[2ndRefuseReason], Count(tbl_RMS_Paperless1.[2ndRefuseReason]) AS Times, tblstaff.[Staff Number]
FROM tbl_RMS_Paperless1 INNER JOIN tblstaff ON tbl_RMS_Paperless1.SupervisorRef = tblstaff.[Staff Number]
GROUP BY tblstaff.[Staff Name], tbl_RMS_Paperless1.[2ndRefuseReason], tblstaff.[Staff Number]
HAVING (((tbl_RMS_Paperless1.[2ndRefuseReason]) Is Not Null))
ORDER BY Count(tbl_RMS_Paperless1.[2ndRefuseReason]) DESC;

 

[isladogs]

Have you tried running each of the queries to check they give an output without error?

 

[aman]

All the queries work fine. If I set the row source of a graph in the property sheet then again it works but just when I try to leave the row source blank in the property sheet and set the row source of the graph on the click event of a button using vba then that error appears.

 

[isladogs]

If I set the row source of a graph in the property sheet then again it works but just when I try to leave the row source blank in the property sheet and set the row source of the graph on the click event of a button using vba then that error appears.

The row source of the graph should be set in its own property sheet NOT that of the button
You could use the button to e.g. show the graph or filter it according to criteria on your form.

As for the date question, you need to add a date filter to one of your queries

 

[aman]

If I add date filter in one of the queries then the following error message appears:

The Microsoft Access database engine does not recognize Forms!frm_Dashboard!txtStartDate as a valid field name or expression

 

[isladogs]

Wrap it in date delimiters:

#" & Forms!frm_Dashboard!txtStartDate & "#

 

[aman]

I have written the following criteria in a 'Date Submit' field in a query and it gives me an error 'The expression you entered has an invalid date format'

>=#Forms!frm_RMS_Dashboard!txtStartDate# and <=#Forms!frm_RMS_Dashboard!txtEndDate#

 

[isladogs]

Read the notation in my last post ...

 

[aman]

Ridders, I have tried it as well in access query wizard like below but getting error "The expression has an invalid date value"

>=#" & Forms!frm_Dashboard!txtStartDate & "#

 

[isladogs]

Dates are a pain to work with
You must also format the date in US format i.e. mm/dd/yyyy

So try this:

>=#" & Format(Forms!frm_Dashboard!txtStartDate,"mm/dd/yyyy") & "# AND <=#" & Format(Forms!frm_RMS_Dashboard!txtEndDate,"mm/dd/yyyy") & "#

or this may be easier

Between #" & Format(Forms!frm_Dashboard!txtStartDate,"mm/dd/yyyy") & "# AND #" & Format(Forms!frm_RMS_Dashboard!txtEndDate,"mm/dd/yyyy") & "#

Either of those should work ... providing the dates really are valid!

 

[aman]

Ridders, I have put following date criteria in 'DateSubmit' field in the following query which works fine:

SELECT tblstaff.[Staff Name], tbl_RMS_Paperless1.[1stRefuseReason], Count(tbl_RMS_Paperless1.[1stRefuseReason]) AS Times, tblstaff.[Staff Number]
FROM tbl_RMS_Paperless1 INNER JOIN tblstaff ON tbl_RMS_Paperless1.SupervisorRef = tblstaff.[Staff Number]
WHERE (((tbl_RMS_Paperless1.DateSubmit)>=[forms]![frm_RMS_Dashboard]![txtDateStart] And (tbl_RMS_Paperless1.DateSubmit)<=[forms]![frm_RMS_Dashboard]![txtDateEnd]))
GROUP BY tblstaff.[Staff Name], tbl_RMS_Paperless1.[1stRefuseReason], tblstaff.[Staff Number]
HAVING (((tbl_RMS_Paperless1.[1stRefuseReason]) Is Not Null))
ORDER BY Count(tbl_RMS_Paperless1.[1stRefuseReason]) DESC;

after I make this change and I run the queries I mentioned earlier named 'qry_RMS_UnionReasons ' and 'qry_RMS_Dashboard_Reasons' ,they run fine as well.no issues. But when I run the query 'qry_RMS_Dashboard_Reasons_Final' , it gives me error message "The Microsoft Access database engine does not recognize Forms!frm_Dashboard!txtStartDate as a valid field name or expression "

 

[arnelgp]

Dont expect that filtering by date will work in the final query. You don't have date field as row heading.

 

[JHB]

With crosstab queries, you've to use parameters if you wish to reference a form.
Here's how to set parameters in the Query Designer user interface:

http://www.fmsinc.com/tpapers/queries/#Parameters

 

[aman]

that's a good shout Arnelgp.But how can I make this working?

 

[aman]

This is a order of my queries so far. What changes you reckon need to be made to make date criteria working.

1. qry_RMS_ReturnReasons_1
2. qry_RMS_ReturnReasons_2
3. qry_RMS_UnionReasons
4. qry_RMS_Dashboard_Reasons
5. 'qry_RMS_Dashboard_Reasons_Final'

 

[arnelgp]

Identify which query has date field and filter it.

 

[isladogs]

Ridders, I have put following date criteria in 'DateSubmit' field in the following query which works fine:

SELECT tblstaff.[Staff Name], tbl_RMS_Paperless1.[1stRefuseReason], Count(tbl_RMS_Paperless1.[1stRefuseReason]) AS Times, tblstaff.[Staff Number]
FROM tbl_RMS_Paperless1 INNER JOIN tblstaff ON tbl_RMS_Paperless1.SupervisorRef = tblstaff.[Staff Number]
WHERE (((tbl_RMS_Paperless1.DateSubmit)>=[forms]![frm_RMS_Dashboard]![txtDateStart] And (tbl_RMS_Paperless1.DateSubmit)<=[forms]![frm_RMS_Dashboard]![txtDateEnd]))
GROUP BY tblstaff.[Staff Name], tbl_RMS_Paperless1.[1stRefuseReason], tblstaff.[Staff Number]
HAVING (((tbl_RMS_Paperless1.[1stRefuseReason]) Is Not Null))
ORDER BY Count(tbl_RMS_Paperless1.[1stRefuseReason]) DESC;

after I make this change and I run the queries I mentioned earlier named 'qry_RMS_UnionReasons ' and 'qry_RMS_Dashboard_Reasons' ,they run fine as well.no issues. But when I run the query 'qry_RMS_Dashboard_Reasons_Final' , it gives me error message "The Microsoft Access database engine does not recognize Forms!frm_Dashboard!txtStartDate as a valid field name or expression "

Apologies aman
I was concentrating on other tasks & led you down a wrong path.
For some reason I thought you were trying to do the date filter in VBA

As you've already found out, you don't need to worry about date delimiters or US date formats in the query designer as Access handles all that for you.
However you DO need to do both of these when writing the equivalent query as VBA code.

As you now seem to be getting useful advice from others, I'll drop out of this thread. Good luck

 

[aman]

The following queries have date filter in it and Date field is hidden .

'qry_RMS_ReturnReason1'

SELECT tblstaff.[Staff Name], tbl_RMS_Paperless1.[1stRefuseReason], Count(tbl_RMS_Paperless1.[1stRefuseReason]) AS Times, tblstaff.[Staff Number]
FROM tbl_RMS_Paperless1 INNER JOIN tblstaff ON tbl_RMS_Paperless1.SupervisorRef = tblstaff.[Staff Number]
WHERE (((tbl_RMS_Paperless1.DateSubmit)>=[forms]![frm_RMS_Dashboard]![txtDateStart] And (tbl_RMS_Paperless1.DateSubmit)<=[forms]![frm_RMS_Dashboard]![txtDateEnd]))
GROUP BY tblstaff.[Staff Name], tbl_RMS_Paperless1.[1stRefuseReason], tblstaff.[Staff Number]
HAVING (((tbl_RMS_Paperless1.[1stRefuseReason]) Is Not Null))
ORDER BY Count(tbl_RMS_Paperless1.[1stRefuseReason]) DESC;

'qry_RMS_ReturnReason2'

SELECT tblstaff.[Staff Name], tbl_RMS_Paperless1.[2ndRefuseReason], Count(tbl_RMS_Paperless1.[2ndRefuseReason]) AS Times, tblstaff.[Staff Number]
FROM tbl_RMS_Paperless1 INNER JOIN tblstaff ON tbl_RMS_Paperless1.SupervisorRef = tblstaff.[Staff Number]
WHERE (((tbl_RMS_Paperless1.DateSubmit)>=[forms]![frm_RMS_Dashboard]![txtDateStart] And (tbl_RMS_Paperless1.DateSubmit)<=[forms]![frm_RMS_Dashboard]![txtDateEnd]))
GROUP BY tblstaff.[Staff Name], tbl_RMS_Paperless1.[2ndRefuseReason], tblstaff.[Staff Number]
HAVING (((tbl_RMS_Paperless1.[2ndRefuseReason]) Is Not Null))
ORDER BY Count(tbl_RMS_Paperless1.[2ndRefuseReason]) DESC;

 

[aman]

as this is a group by query and I don't have to group by date so that's why there is a WHERE criteria on that field and its hidden.

I think that's why my final query which is linked to graph isn't working because there is no date field in the row heading.

Any help will be much appreciated.

 

[isladogs]

as this is a group by query and I don't have to group by date so that's why there is a WHERE criteria on that field and its hidden.

I think that's why my final query which is linked to graph isn't working because there is no date field in the row heading.

Any help will be much appreciated.

Correct.
You either need to modify the underlying queries adding the date fields or build similar queries/VBA code containing the required fields.