Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    18

    Unanswered: -

    -
    Last edited by TheGame7; 12-10-03 at 10:34.

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: Cannot add records to query containing expressions - How can I use it in a form?!

    Originally posted by TheGame7
    I have produced a query which contains 3 expressions.

    They are as follows:

    1) Calculates the length of time a place has been booked for using SUM. (Uses Hour() to convert time into hours).

    2) Works out what the cost per hour is (using an IIF statement).

    3) Mulitplies the results of Expressions 1 and 2 together.

    After adding these expressions, I lost the ability to add more data to the queries.

    This means that I cannot just base a form on the query, then enter the details into the other fields and then these automatically calculate themselves.

    How can I go about using these expressions to calculate cost once I have added data?

    I now have a long winded method where a Append Query copies the fields to another table as well as the calculated ones.

    But before it can do this, a delete query must clear it.

    This is not very efficient and I would love a better solution.

    Ideas anyone?
    How about incorperating the calculations at the form or report level instead of the query?

    Gregg

  3. #3
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by TheGame7
    anyone?
    Still here.

    Are you trying to get the total cost for a group of bookings? In other words take all the hours spent in all the bookings that a place has had and sum them all together by subtracting the end times from the beginning times?

    Just trying to get a clear picture.

    You can use these same aggregate functions in forms and reports. Set up text boxes that are unbound and in the Control Source for the box enter your expression in the form of
    =Sum((Hour([Booking_Time_End])-Hour([Booking_Time_Start]))). The query that the form is based on only has to contain the Start and End time fields. The query at this point should still be editable.

    In a report you can use the same tactic.

    For adding values to a table, you could use a recordset with an SQL statement as the source or just use the values in the form. You can open another recordset based on the table you want to add to and add the values from form to recordset or recordset to recordset.

    You can also simply use SQL to Update or Append to the table without the recordset using Update/Set or Insert Into.

    Hope this helps and that I understood your intentions.

    Gregg

  4. #4
    Join Date
    Nov 2003
    Location
    Riga, Latvia
    Posts
    36
    Why don't you use an OnChange event for the unbound control with the following code:

    Sub UnboundControl_OnChange()

    Me!Field = Me.UnboundControl.Value

    End Sub

    Should work. Though it is weird that you cannot add records into the query directly.
    Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

  5. #5
    Join Date
    Nov 2003
    Location
    Riga, Latvia
    Posts
    36

    Here's an example

    In the attached file I've created two examples. Both of them do the following: sum the InputValue1 and InputValue2 from the table and store the calculated value.

    First, consisting of table tblVariant1 and a form frmVariant1, uses the solution similar to the one I've suggested earlier, i.e., using the unbound control and inserting its value with the form BeforeUpdate event. Sorry I've probably mislead you since making the OnChange event work for unbound controls required much more coding than I thought. So, this one works.

    Second one consists of a table tblVariant2, query qryVariant2 and a form frmVariant2, which do the following thing: take the values from the InputValue1 and InputValue2 columns, and use the QryCalcField to calculate the sum. In this case, though, the calculated value is not inserted into the table -- but you can address the query in all other forms/queries/reports, since it has the exactly same fields + the calculated field that you need (notice that the form frmVariant2 is based on a query, not on a table).

    Hope this helps.
    Attached Files Attached Files
    Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

Posting Permissions

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