Results 1 to 4 of 4
  1. #1
    dmhgt is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    2

    Creating a Multiple Criteria Reference Calculation in a Query

    Sorry for the lame thread title...I don't really know what else to call this issue.

    I have a database table that contains many date fields with the combination of 'Letter' and 'Number' as the key. Here are my field names:

    Letter
    Number
    Ship Date
    Induction Date
    Test Start Date
    End Date Return Date
    Test Finish Date
    Actual Turn-Around Time

    For each record, I am trying to calculate what the actual turn-around time would be based on a formula for the 'Letter' & 'Number' combination found in another table. Here is the formula table:



    Click image for larger version. 

Name:	test.jpg 
Views:	7 
Size:	7.4 KB 
ID:	16988

    For a record in the 1st table where 'Letter' = A and 'Number' = 1, is there a way in a query to calculate the Actual Turn-Around Time as End Date minus Ship Date?

    I hope this makes sense. This is the best example I could think of

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Here is what I played around with.

    Created table with fields Letter, Number, EndDate, ShipDate, Formula (to simulate a dataset that is a join of the two tables).

    In Formula field for the A1 record a text string: DateDiff("d",#1/1/2014#,#2/1/204#)

    Then in a query created field with expression: Eval([Formula])

    This returns result of 31.

    However, when I reference fields in place of the literal date values, the query expression fails.
    DateDiff("d",[ShipDate],[EndDate])

    So will need a long nested IIf expression in query or call a custom VBA function.
    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.

  3. #3
    dmhgt is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    2
    Thanks for the suggestion. I would like to avoid a huge nested IIf statement since this is something I would prefer to maintain in a table for easy updating. It sounds more and more like that's not going to work though

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The table can have two fields for the terms of the DateDiff calculation. The content of each field will be the fieldname to use. Then VBA code can translate that string value to a fieldname. Code probably easier behind a form or report than a general module although code in general module could be called by query, form, or report.
    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. calculation of day with reference of daterange
    By joshynaresh in forum Forms
    Replies: 9
    Last Post: 03-30-2014, 02:41 AM
  2. Replies: 2
    Last Post: 12-04-2013, 03:49 PM
  3. Replies: 1
    Last Post: 07-13-2011, 11:00 AM
  4. Replies: 1
    Last Post: 06-18-2011, 10:00 AM
  5. Replies: 2
    Last Post: 05-09-2011, 06:45 PM

Tags for this Thread

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