Error 3075 & report creation (2003/7)

Topic

New Reply

Ok this is happening on a 2003 installation (snow stops me from trying it on a 2007 one!)

I’m trying to create a simple chart, but keep getting the following error;

“Function is not available in expressions in in query expression ‘(format([MonthYr],”MMM YY”))’

This is even when the source is direct from the table. But the really weird thing is that the [MonthYr] field is formatted “mmm yyyy”, and another chart works????

Could this be a restriction by the 2003 version? Any ideas will be very welcome.

Reply | Quote

Replies

What is the data type of the MonthYr field? It should work in Access 2003 with a date/time field.

Reply | Quote

Yes; it is a Date/Time field.

I don’t know if it’s relevant, but this did come up using the cahrt wizard.

Reply | Quote

I tested the chart wizard in Access 2003 (I don’t have 2007), and it handled an expression such as Format([DateField], “mmm yy”) without problems.

Could you post a stripped down and zipped copy of the database, and a description of what you were trying to do?

Reply | Quote

OK

I’ve got around it, slightly.

I went ’round a different direction; instead of using the create a report with a chart, I created a blank report & then inserted a chart & that worked.

Only trouble is, I still can’t get the query to accept another criteria, specifically on the [Reason] field (Doh!).

The criteria (I’d like to add) is;
Like [forms]![frmrptsel]![CboMediProbSel]

Here’s the current source SQL;

PARAMETERS [forms]![frmRptSel]![TxtDtFrom] DateTime, [forms]![frmRptSel]![txtDtTo] DateTime;
TRANSFORM Sum(tblMedi.Count) AS SumOfCount
SELECT DateSerial(Year([tblMedi]![MonthYr]),Month([tblMedi]![MonthYr]),1) AS TheMonth
FROM tblMedi
WHERE (((tblMedi.MonthYr) Between [forms]![frmRptSel]![TxtDtFrom] And [forms]![frmRptSel]![txtDtTo]))
GROUP BY DateSerial(Year([tblMedi]![MonthYr]),Month([tblMedi]![MonthYr]),1)
PIVOT tblMedi.Reason;

I’d like

Reply | Quote

Does this work?

PARAMETERS [forms]![frmRptSel]![TxtDtFrom] DateTime, [forms]![frmRptSel]![txtDtTo] DateTime, [forms]![frmrptsel]![CboMediProbSel] Text (255 );
TRANSFORM Sum(tblMedi.Count) AS SumOfCount
SELECT DateSerial(Year([tblMedi]![MonthYr]),Month([tblMedi]![MonthYr]),1) AS TheMonth
FROM tblMedi
WHERE (((tblMedi.Reason) Like [forms]![frmrptsel]![CboMediProbSel]) And ((tblMedi.MonthYr) Between [forms]![frmRptSel]![TxtDtFrom] And [forms]![frmRptSel]![txtDtTo]))
GROUP BY DateSerial(Year([tblMedi]![MonthYr]),Month([tblMedi]![MonthYr]),1)
PIVOT tblMedi.Reason;

Reply | Quote

Almost.

I’d forgotten that the [Reason] field is simply a code & the field (with the actual value I want displayed) is related to another table with the required field.

Anyway, with your guidance, I’ve sussed it

Thank you

Reply | Quote