Results 1 to 15 of 15
  1. #1
    dsajones is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2012
    Posts
    19

    Reports That Were Working Now Producing Error

    Hi all.



    I have 2 reports that both feed off the same query. They are called from a very simple menu where a start date and end date are captured. Then click one of three buttons as shown here
    Click image for larger version. 

Name:	Report Menu.PNG 
Views:	25 
Size:	7.2 KB 
ID:	42544
    If I run the query it works fine and returns all rows as expected. If I run either of the reports I get the following error:

    Run-Time error 3071
    This expression is typed incorrectly, or it is too complicated to be evaluated. For example a numeric expression may contain too many complicated elements. Try simplifying the expression y assigning parts of the expression to variables.

    Only problem is that it's not giving me any clues as to which expression.

    Here is the query which runs with no problem in its own right:

    PARAMETERS [Forms]![frm Run Reports and Queries]![txtStartDate] DateTime, [Forms]![frm Run Reports and Queries]![txtEndDate] DateTime;

    SELECT Updates.FrameWork_ID, [Service User].SU_Name, Updates.Update_Date, Updates.End_Date, Updates.Update_Reason, Updates.One_Off_Cost, Updates.Change_AT_Costs, Updates.Change_Costs_Saved, Updates.Change_Costs_Avoided, GetDaysBetween(Forms![frm Run Reports and Queries]!txtStartDate,Forms![frm Run Reports and Queries]!txtEndDate,[Update_Date],Nz([End_Date],Date())) AS DaysActive, [Service User].SU_Name, FormatCurrency(((Updates.Change_AT_Costs / 7) * DaysActive)) AS Total_AT_Costs, FormatCurrency((Updates.Change_Costs_Saved/7 * DaysActive)) AS Total_Costs_Saved, FormatCurrency((Updates.Change_Costs_Avoided/7 * DaysActive)) AS Total_Costs_Avoided

    FROM [Service User] INNER JOIN Updates ON [Service User].FrameWorkID = Updates.FrameWork_ID

    WHERE (((GetDaysBetween(Forms![frm Run Reports and Queries]!txtStartDate,Forms![frm Run Reports and Queries]!txtEndDate,[Update_Date],Nz([End_Date],Date())))>0))

    ORDER BY Updates.FrameWork_ID;

    And in the summary report I just have 6 fields that get evaluated and the expressions behind each one are:

    =FormatCurrency(Sum([Total_AT_Costs]))
    =FormatCurrency(Sum([Total_Costs_Saved]))
    =FormatCurrency(Sum([Total_Costs_Avoided]))
    =FormatCurrency(Sum([One_Off_Cost]))
    =(SELECT Count(*) FROM (SELECT DISTINCT [FrameWork_ID] FROM [qry_Updates]))
    =Count([End_Date]>0)

    So if the query runs fine and as you can see there is nothing particularly complex about the expressions in the calculated fields on the report, can anyone suggest where to look? And not forgetting that both the reports were running fine a couple of weeks ago and haven't been changed. All I have done is seperate the front end and back end databases to make it more network share friendly.

    Many thanks
    David

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    This expression is typed incorrectly, or it is too complicated to be evaluated.
    usually means you have one or more nulls in your data or the wrong datatype. For example you are using formatcurrency as a function in your query - which presumably returns a string, then you try to sum and format those strings in your report.


    Edit: To find which column
    open your query and sort on each of the columns - do you get blanks or #error? If so, these need to be resolved
    if this does not solve the problem, remove each column one by one until the error goes away

  3. #3
    dsajones is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2012
    Posts
    19
    Quote Originally Posted by Ajax View Post
    usually means you have one or more nulls in your data or the wrong datatype. For example you are using formatcurrency as a function in your query - which presumably returns a string, then you try to sum and format those strings in your report.


    Edit: To find which column
    open your query and sort on each of the columns - do you get blanks or #error? If so, these need to be resolved
    if this does not solve the problem, remove each column one by one until the error goes away
    Hi Ajax,

    Thanks for your reply. However, the query itself runs without problem. The 3 buttons on the form consists of one that just runs the query and the other 2 open reports that feed directly off that same query. So I'm baffled why the query runs fine in its own right but as soon as it's called from one of the reports I get the error.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why do you need a button to 'run' a SELECT query?

    It is baffling that splitting db would cause this issue.

    Are you sure this worked before:
    =(SELECT Count(*) FROM (SELECT DISTINCT [FrameWork_ID] FROM [qry_Updates]))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    dsajones is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2012
    Posts
    19
    Quote Originally Posted by June7 View Post
    Why do you need a button to 'run' a SELECT query?

    It is baffling that splitting db would cause this issue.

    Are you sure this worked before:
    =(SELECT Count(*) FROM (SELECT DISTINCT [FrameWork_ID] FROM [qry_Updates]))
    Hi June7,

    Things are being developed so I started with the query to ensure that it worked correctly, particularly to ensure that the GetDaysBetween function was working. I was going to remove the button once I was sure the query was working correctly but the users like having it there as it gives them a quick way of extracting data to go in to Excel for a couple of crosstab reports etc.

    Yes, the select statement was working fine. That was provided by another contributor on this forum. It doesn't exist on the Detailed Report but that fails with the same error.

    I agree, splitting the db shouldn't cause this type of issue. All I've done is move all the data tables in to a backend database and then linked to them from the front end which contains all the forms, reports, queries etc.

    I'm completely baffled!

    Cheers
    David

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Well, I cannot get a SELECT like that to work in textbox. Just get #Name? error. Everything I know says it should NOT work and this is what domain aggregate functions are for.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    To narrow it down, I'd blank out all the expressions in the textboxes and add them back one at a time until the problem occurs. Then you'd have a clue.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    However, the query itself runs without problem.
    yes - but have you checked each row to make sure it contains valid data as I suggested. Your error message is about invalid data

    I don't understand why you are formatting data in a query, that should only be done in the form/report - and doesn't need a function, you use the format property of the control

    You also haven't said what datatype formatcurrency returns

    All I've done is move all the data tables in to a backend database and then linked to them from the front end which contains all the forms, reports, queries etc.
    to to be clear, you may not have changed the form/report design, but by implication you are also saying you have not added/changed any data whatsoever - and if you move the file back (i.e. to how it was before), it works OK

    @June - I provided the code - but said it should be in a separate query or could be the rowsource to a listbox

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Hmm, I've seen that error before and it wasn't due to nulls but I can't recall what the issue was. I have no idea if you're simply opening the report in code or not. If you open the report from the nav pane and get the same error but not when running the query, I would define the data types of the parameters. When specifying query parameters like that, you should include the data type property for numbers, currency and dates. Open the query property sheet and fill out the data type values for the 2 parameters and see if it makes a difference. Your report may be pulling in text rather than dates.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    dsajones is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2012
    Posts
    19
    Quote Originally Posted by Ajax View Post
    yes - but have you checked each row to make sure it contains valid data as I suggested. Your error message is about invalid data

    I don't understand why you are formatting data in a query, that should only be done in the form/report - and doesn't need a function, you use the format property of the control

    You also haven't said what datatype formatcurrency returns

    to to be clear, you may not have changed the form/report design, but by implication you are also saying you have not added/changed any data whatsoever - and if you move the file back (i.e. to how it was before), it works OK

    @June - I provided the code - but said it should be in a separate query or could be the rowsource to a listbox
    Hi Ajax and June7

    Many thanks for your additional input on this. It would appear that the FormatCurrency in the query was the culprit although as previously mentioned, nothing has changed since I originally created it other than the splitting of the dB.

    I've removed the FormatCurrency from within the query and both reports are now working.

    @Ajax - looking back at your help with the =(SELECT Count(*) FROM (SELECT DISTINCT [FrameWork_ID] FROM [qry_Updates]) and I must admit I'd missed the subtlety of what you had said that it needed to be a listbox. I had originally made it a text box but then couldn't work out how to make a query its rowsource. Not wanting to bother you again I figured that I could put it into an expression in the textbox - BUT, as @June7 said this was now returning a #name? error. And looking back at my notes I now realise that it had never worked and was on my list of things to do!.. So I've now implemented this as a list box and have created a query which has that select statement in it and it's working now

    Many many thanks for your help and patience. As you'll see from my info I am definitely a novice and have hardly done anything before with Access reports and queries so it's all a learning curve for me. Still not sure why it was working before with the FormatCurrency in the query but all I'm concerned about is that it's all working now!

  11. #11
    dsajones is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2012
    Posts
    19
    Hi Micron,

    Thanks for your help. Everything is now working OK as a result of removing the FormatCurrency from the query. Please see the previous post.

    Cheers
    David

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    I had originally made it a text box but then couldn't work out how to make a query its rowsource.
    just to answer this, the controlsource would be a dlookup into the query

  13. #13
    dsajones is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2012
    Posts
    19
    Quote Originally Posted by Ajax View Post
    just to answer this, the controlsource would be a dlookup into the query
    Hi Ajax,

    I'm not really sure what you mean by a dlookup. I've created a listbox and when it's asked me if I want to feed it from an existing table/query or type the values in, I've selected Query and selected the new query that I've created. That's producing a result on the report and it's correct. Could you explain what the dlookup means?

    Cheers
    David

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    to clarify if you use a textbox, your controlsource would be something like

    =dlookup("ttlcount","myQuery")

    and your query would be

    SELECT Count(*) as ttlCount FROM (SELECT DISTINCT [FrameWork_ID] FROM [qry_Updates])

    or you could have your query as
    SELECT DISTINCT [FrameWork_ID] FROM [qry_Updates]

    and your controlsource would then be

    =dcount("*","myQuery")


    if you use a listbox the rowsource would be the query

    SELECT Count(*) FROM (SELECT DISTINCT [FrameWork_ID] FROM [qry_Updates]

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Research domain aggregate functions - DLookup, DCount, DAvg, DSum, DMax, DMin, DStDev, DFirst, DLast.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sum Function Producing Error
    By chippy in forum Programming
    Replies: 9
    Last Post: 05-01-2018, 02:42 PM
  2. Replies: 7
    Last Post: 09-12-2017, 11:12 PM
  3. Replies: 5
    Last Post: 07-15-2015, 01:30 PM
  4. Replies: 4
    Last Post: 10-18-2013, 01:13 PM
  5. Reports not working after MS Update
    By lauradilts in forum Access
    Replies: 4
    Last Post: 01-13-2012, 09:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums