Results 1 to 8 of 8
  1. #1
    ewassmer is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    20

    Form w/ Date Range query and calculations- too complex to be evaluated

    Hi everyone, I've been working on the same form for a while now, and I've hit another dead end. I'm using Access 2003 and I'm a beginner. I have used VBA a handful of times. So bear with me.
    I have a table that consists of almost 200 records. There are fields for:
    Date
    Product (text)
    Yards On (number)
    Yards Off (number)
    Start Time (00:00:00)
    End Time (00:00:00)
    Unscheduled Downtime (in parts of an hour, as in 1 means 1 hour and 1.5 means 90 mins, I have a few of these fields)



    In this form, I would like to be able to select a date range, and then have several list boxes or text boxes display data or calculations based on the date range. These boxes include:
    Products (should show all products processed between dates chosen) **
    Average Yards On **
    Average Yards Off **
    % Yield (Avg Yards On/Avg Yards Off)
    Actual Production Hours (Start Time-End Time minus all unscheduled downtime)
    Average Yards Off Per Hour (Yards Off/Actual Production Hours)
    ** - box displays correct data when date range selected
    I was able to get all of these boxes to work in a separate simpler form that included one date only by querying everything in a combo box and hiding the fields. I'm not sure how to explain this, but I included the form (Form1) in the attached sample database.
    I am having trouble to get all of these fields to work when involving a date range, probably because depending on the date selected, the query may include 50+ records. I get an error message that says the expression is too complex to be evaluated.

    Right now, I am using two combo boxes to select the date range. For the data, I am using separate queries for each list box in the RowSource field.
    For example, the query for the Avg Yards On Field is:
    SELECT Avg(Productivity.YardsOn) FROM Productivity WHERE (((Productivity.ProdDate) Between Forms!Form2!ComboOne And Forms!Form2!ComboTwo));

    I have also tried building and saving a whole separate query (QueryBetween) and chosing which fields to show in each list box, but I get the same error message.

    Any suggestions on how to get all fields to display without getting an error messge? I seem to be using the right SQL since it worked in the other form.
    I should add that the table that the form is based on is constantly updated, so I need to keep everything related to that one table.
    Thanks!

  2. #2
    ewassmer is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    20
    An idea... DSum and DAvg? I haven't used them before, but maybe they'd help. I just have no idea how to use them.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Do you have a SQL statement in this database that is correctly calculating what you want for a single date? If not could you post that code as well so we know what the 'correct' calculation is.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Sorry I'm asking because you have two forms, form1 and form2 and in form 1 (which appears to be the single date) the PRODUCT combo box never gets populated, I get an error. But you said it was working.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I'm just looking at the formulas in your query and I see a couple of problems:

    You have these three formulas in the SQL statement driving your form:

    FormatPercent([yardsoff]/[yardson])

    (((DateDiff("n",[StartTime],[EndTime]))/60)-([SetUpTime]+[CleanUpTime]+[MaintUnschedDT]+[TestUnschedDT]+[OtherUnschedDT]+[MixUnschedDT]+[RTODT]))

    ([YardsOff]/(((DateDiff("n",[StartTime],[EndTime]))/60)-([SetUpTime]+[CleanUpTime]+[MaintUnschedDT]+[TestUnschedDT]+[OtherUnschedDT]+[MixUnschedDT]+[RTODT])))

    But you are not aliasing them, I don't know what they are supposed to mean but your FORMATPERCENT formula has a problem. If you have a yardson value of 0 your calculations are going to blow up the formula should read: iif([yardson] = 0 or isnull([yardson]), 0, [yardsoff]/[yardson])

    All three of these fields should have aliases and the whole form should be (more easily) based on a query not looking up values from a specific column of your combo box.

    I suspect that the dividing by zero is your problem though.

  6. #6
    ewassmer is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    20
    Hi, sorry for the delay, I had a few days off.

    Form 1 does populate the Product field on my program, I'm not sure why it wouldn't work for you.
    I did try to base all of these on a query, but I got the same message-- probably because of the 0 issue.

    What exactly does aliasing mean and how can I do so?
    And where in the SQL statement should I put the iif statement?

    Thanks (again, I'm very new at this)!

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Code:
    SELECT Productivity.Product, Productivity.LotNumber, Productivity.YardsOn, Productivity.YardsOff, (FormatPercent([YardsOff]/[YardsOn])), (((DateDiff("n", [StartTime], [EndTime])) / 60) - ([SetUpTime]+[CleanUpTime]+[MaintUnschedDT]+[TestUnschedDT]+[OtherUnschedDT]+[MixUnschedDT]+[RTODT])), ([YardsOff]/(((DateDiff("n", [StartTime], [EndTime])) / 60) - ([SetUpTime]+[CleanUpTime]+[MaintUnschedDT]+[TestUnschedDT]+[OtherUnschedDT]+[MixUnschedDT]+[RTODT]))), Note FROM Productivity WHERE (((Productivity.ProdDate)=Forms!Form1!CategoryID)) 
    UNION SELECT DISTINCT Null, null, null, null, null, null, null, null FROM Productivity ORDER BY Productivity.Product;
    This is the query running your second combo box (which I assume needs to have something in it to populate the rest of the form) but it when I click the down arrow on the combo box I get an error. What is this doing on your form (look at the sample database you constructed not your original database). Are you calling a function that you've written with this code? The error message I'm getting indicates that it's likely it's trying to perform a mathematical or date function on a text field.

    I took off the union part of this query statement and got a list of items in the combo box I just don't know what you're doing with the union part, is it solely so that you can get a 'null' option in your combo box?

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok I think I figured it out though I don't like the way your data is set form 2 now works as far as I can tell.

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

Similar Threads

  1. Run query by date range
    By ARickert in forum Access
    Replies: 2
    Last Post: 06-23-2011, 10:54 AM
  2. Too Complex to be evaluated - HELP!
    By awmmoore in forum Queries
    Replies: 2
    Last Post: 06-06-2011, 10:23 AM
  3. Complicated Query needs some date range help
    By KevinMCB in forum Queries
    Replies: 1
    Last Post: 01-11-2011, 12:25 PM
  4. Date range query from form
    By Steve Barnes in forum Queries
    Replies: 2
    Last Post: 07-29-2010, 07:06 PM
  5. complex calculations like in Excel
    By jacko311 in forum Database Design
    Replies: 2
    Last Post: 11-11-2009, 05:51 PM

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