Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error 3075 & report creation (2003/7)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error 3075 & report creation (2003/7)

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

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error 3075 & report creation (2003/7)

    Yes; it is a Date/Time field.

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error 3075 & report creation (2003/7)

    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?

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error 3075 & report creation (2003/7)

    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;

    <font face="Georgia">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;
    </font face=georgia>

    I'd like

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error 3075 & report creation (2003/7)

    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;

  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error 3075 & report creation (2003/7)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •