OLE Issue and No Solution Can Be FOund

[GinaWhipp]

Hard coding would be just to test not to put into production.

The way the data hits the table should not be a problem. However, if you are linking to the Excel file and then running the query against that it could be the problem. Though the one you upload has not links and still seeing error, so probably not that.

Another thing to keep in mind is earlier versions of Access were and still are a lot more tolerant of it's newer versions.

All that said I did find something, try changing...

[Forms]![frmChart_LocationCat].[BeginDate]

to...

[Forms]![frmChart_LocationCat]![BeginDate]

Notice I replaced the DOT, do this for all the fields you are referring to and see if that helps.

 

[skilche1]

Hard coding would be just to test not to put into production.

The way the data hits the table should not be a problem. However, if you are linking to the Excel file and then running the query against that it could be the problem. Though the one you upload has not links and still seeing error, so probably not that.

Another thing to keep in mind is earlier versions of Access were and still are a lot more tolerant of it's newer versions.

All that said I did find something, try changing...

[Forms]![frmChart_LocationCat].[BeginDate]

to...

[Forms]![frmChart_LocationCat]![BeginDate]

Notice I replaced the DOT, do this for all the fields you are referring to and see if that helps.

Can you believe I have tried this? I still get the same results.

[Forms]![frmChart_LocationCat].[BeginDate]

to...

[Forms]![frmChart_LocationCat]![BeginDate]

It is obvious you are toying around with it also. Are you getting the same error message? I am running 2010 on both my work and home computer and see no difference.

 

[GinaWhipp]

Okay, this is completely wrong you can't put this...

Between [Forms]![frmChart_LocationCat]![BeginDate] And [Forms]![frmChart_LocationCat]![EndDate] And [Forms]![frmChart_LocationCat]![cboLC]

...on a field that only draws Location. The date portion needs to be in the DateCaught field. I'm going to suggest you refer back to your earlier databases since those worked.

 

[GinaWhipp]

DON'T USE... EVEN THIS NEEDS WORK

Also opening in Access 2010... and just saw another issue, you really need to fix qryQC_byLocation to...

SELECT tblQCatch.Location_ID, tblQCatch.Location_ID, tblQCatch.DateCaught, Count(tblQCatch.QCatch_ID) AS CountOfQCatch_ID, tblQCatch.Location_ID
FROM tblQCatch
GROUP BY tblQCatch.Location_ID, tblQCatch.DateCaught, tblQCatch.Location_ID
HAVING (((tblQCatch.DateCaught) Between [Forms]![frmChart_LocationCat]![BeginDate] And [Forms]![frmChart_LocationCat]![EndDate]) AND ((tblQCatch.Location_ID)=[Forms]![frmChart_LocationCat]![cboLC]));

 

[skilche1]

Okay, this is completely wrong you can't put this...

Between [Forms]![frmChart_LocationCat]![BeginDate] And [Forms]![frmChart_LocationCat]![EndDate] And [Forms]![frmChart_LocationCat]![cboLC]

...on a field that only draws Location. The date portion needs to be in the DateCaught field. I'm going to suggest you refer back to your earlier databases since those worked.

Sorry, I thought I had cleaned that up... Was doing a lot of trial and error testings...

 

[GinaWhipp]

Umm, okay the more I look at this database the more stuff needs to be fixed. Please clean up the queries including the one that is on the Chart which is pulling from fields not in the original query. Can't fully test something till all the pieces and parts are in place.

 

[skilche1]

Umm, okay the more I look at this database the more stuff needs to be fixed. Please clean up the queries including the one that is on the Chart which is pulling from fields not in the original query. Can't fully test something till all the pieces and parts are in place.

OK, it's all cleaned up. Thank you so much! I really appreciate this...

 

[GinaWhipp]

Looking now...

 

[GinaWhipp]

Okay, so the problem is you are pulling the criteria from a query that is not part of the Chart and it's not open so the query that is attached to the Chart can't find the fields. You need to remove the Criteria from qryCharting and then copy/paste the below to the RowSource of the Chart.

 PARAMETERS [Forms]![frmChart_LocationCat]![BeginDate] DateTime, [Forms]![frmChart_LocationCat]![EndDate] DateTime, Forms![frmChart_LocationCat]![cboLC] Long; TRANSFORM Count(qryCharting.Catches) AS CountOfCatches SELECT (Format([DateCaught],"mmm"" '""yy")) AS Expr1 FROM qryCharting WHERE (((qryCharting.DateCaught) Between [Forms]![frmChart_LocationCat]![BeginDate] And [Forms]![frmChart_LocationCat]![EndDate]) AND ((qryCharting.LocationCategory_ID)=[Forms]![frmChart_LocationCat]![cboLC])) GROUP BY (Year([DateCaught])*12+Month([DateCaught])-1), (Format([DateCaught],"mmm"" '""yy")) PIVOT qryCharting.LocationCategory;

 

[skilche1]

Thank you. Works now with any errors, but not its not showing all data in the graph. I'll have to tinker with it. Thank you so much.

 

[skilche1]

One question I do have. Do need to use the "Parameter" statement each time I create a chart or do I only need to use it when pulling from a query? If that is the case, then pulling data from the table would be best, right?

 

[GinaWhipp]

Oh, I forgot one piece... The

Me.Refresh

change to...

Me.[COLOR=red][B]InsertYourGraphNameHere[/B][/COLOR].Requery

 

[skilche1]

Oh, I forgot one piece... The

Already took care of it. Thanks

 

[GinaWhipp]

You must use the Parameters because of the type of Query. So, unless you change the Query type you will need to use those Parameters.