Results 1 to 2 of 2
  1. #1
    goldie6175 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Milwaukee, WI
    Posts
    1

    Help with formula for Query

    I will try to explain the best I can. Hope you guys can help! I have a database of animals for research. They want me to be able to tell them how many were alive by project per month. These are the fields:



    fldDOB (Date of Birth)
    fldDOP (Date of Pass)

    I have no trouble calculating the total number days alive, but if an animal was born on 1/1/2010 and died on 2/10/2010 I need to be able to tell them that the animal was alive for 10 days in February.

    Seems pretty simple, but I can't do it. Hopefully someone here a lot smarter than me.

    Thanks!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would create a little function or use something like this:

    http://support.microsoft.com/default...b;en-us;209857

    along with the DateDiff function. You would pass the birth date and the start date of your period to your function or the one above and let it return back the later of the two dates for use with the DateDiff function, and similarly pass the end date of your period and the pass date to get the earlier of the two to use in the DateDiff function. You'd probably want to have your function allow for the possibility of a Null pass date, or use the Nz() function when passing dates to the above.

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

Similar Threads

  1. access query formula
    By simpleman in forum Queries
    Replies: 6
    Last Post: 11-26-2009, 09:13 AM
  2. Replies: 0
    Last Post: 09-03-2009, 01:58 PM
  3. Concatenate Formula
    By Shingo in forum Programming
    Replies: 1
    Last Post: 07-25-2009, 09:14 AM
  4. formula on report??
    By dike969 in forum Access
    Replies: 0
    Last Post: 03-05-2007, 01:58 AM
  5. Max formula in Access
    By mohsin74 in forum Programming
    Replies: 2
    Last Post: 12-26-2006, 07:21 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