Results 1 to 6 of 6
  1. #1
    snowdrop is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    4

    Performing calculations from a drop down list

    I'm having an issue with a query performing calculations and I hope someone can help me as I'm tearing my hair out!!!

    Basically I have a cost form that has 3 different VAT rates in a drop down list. On the form it calculates the value + VAT no problem but when I run a query with the total amount it comes back blank.

    Is there something I should be adding into the query? I've tried building an expression in the criteria to calculate all the subtotals but that doesn't work either.

    I need to sort this asap as about to go off on leave for a while and no one else knows how to work with the database. I need the reports to be accurate before going.



    Any help is gratefully appreciated.

  2. #2
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    Post your SQL statement for your query.

  3. #3
    snowdrop is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    4
    Is this what you are asking for?


    SELECT [Grant Info].[Date Approved], [Grant Info].ID, [Grant Info].Name, [Grant Info].Department, [Grant Info].[Special Accounts ID], [Grant Info].[Subject of Application], [Grant Info].[Grant approved], [Grant Info].[Total Requested], [Grant Info].[Amount approved], [Grant Info].[Funding Source], [Grant Info].[Date Grant Released], [Grant Info].Date
    FROM [Grant Info]
    WHERE ((([Grant Info].[Grant approved])=Yes) AND (([Grant Info].[Total Requested])=[Grant Info]![Subtotal 1]+[Grant Info]![Subtotal 2]+[Grant Info]![Subtotal 3]+[Grant Info]![Subtotal 4]+[Grant Info]![Subtotal 5]+[Grant Info]![Subtotal 6]+[Grant Info]![Subtotal 7]) AND (([Grant Info].Date) Between [Enter start date] And [Enter end date]));

  4. #4
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    Firstly: Using the terms Date and Name should be avoided as they are reserved words in Access and may actually cause Access to misbehave at some point. Suggest you change them, ie. gDate and gName or some other variation.

    To try and keep your query clean, I would actually do the calculation of summing the subtotal fields in a new field as an expression:

    Perhaps Subtotals: [Grant Info].[Subtotal1] + [Grant Info].Subtotal2]......

    and then do an IIf statement to compare the results with the [Total Requested].

  5. #5
    snowdrop is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    4
    I tried doing an expression like this already but it still didn't work. I also tried an expression calculating the cost+Vat but it that didn't work either.

    Any other suggestions?

  6. #6
    snowdrop is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    4
    I have now resolved this issue but thanks for your help.

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

Similar Threads

  1. Performing calculations in the form.
    By mulefeathers in forum Forms
    Replies: 4
    Last Post: 12-07-2011, 10:47 AM
  2. How to get key for drop-down list
    By mitchmcc in forum Access
    Replies: 2
    Last Post: 11-29-2011, 06:21 AM
  3. Replies: 4
    Last Post: 06-16-2011, 09:30 PM
  4. Replies: 14
    Last Post: 06-03-2010, 06:03 PM
  5. drop down list
    By tceicher in forum Access
    Replies: 5
    Last Post: 08-13-2009, 05:41 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