Results 1 to 7 of 7

Thread: Formula in a Query in the Feild
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,663
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Formula in a Query in the Feild

    In design mode you can enter a column and make it a formula (example: MONTH1_PD: IIf([PDIR Completed] Is Null,DateDiff("d",[PDIR Due Date],Now()),"") This is just an example

    I'm need a rather complex formula

    I need a formula that says IIF [PDIR Completed] >= [PDIR Due Date], "1","0" But that's the easy part, what I need the formula to do first is check the date in the [PDIR Due Date] field. If its in the month 3 months earlier than today, then do the above. So if today is 9/13/2019, I want to check to see if the [PDIR Due Date] is in June (between 6/1/2019 and 6/30/2019. that's the part I am struggling with. Any help is appreciated

    PS I don't want to put a date criteria in the query for [PDIR Due Date]. I need a Dynamic formula that I do not have to change the query criteria every month.

    Thanks
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,036
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Formula in a Query in the Feild

    You should be able to nest an IIF statement to do the second (two IIF functions nexted within each other), and use DateDiff to get the difference in months.

    See here for some explanations and examples for nested IIF:
    http://www.simply-access.com/Nested_IIF_Statement.html
    https://support.office.com/en-us/art...0-647539c764e3

    I'll let you take a crack at it, and seehow you do. It should be pretty straightforward.
    Post back and let us know if you figure it out or still need help!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,663
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula in a Query in the Feild

    IIf statement to for the formula isn't difficult, its doing the first condition in the formula to see if the date is 3 months earlier (6/1/2019-6/30/2019) I thought maybe do some kind of EMonth(NOW,-4)+1 and Eomonth NOW(,3) but could get to a working formula
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,036
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Formula in a Query in the Feild

    Why can't you just do a DATEDIFF on those two dates, and see if the difference is 3 months?
    If you choose months ("m") instead of days ("d"), you shouldn't need to worry about day of the month (as it will ignore days), i.e.
    6/1/2019 - 9/30/2019
    6/15/2019 - 9/30/2019
    6/30/2019 - 9/1/2019
    will all return 3.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,663
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula in a Query in the Feild

    I see, so use the Date Diff as my condition (Iff DatedDiff=3)

    I was going a much longer route IIF(([PDIR Due Date]>DateSerial(Year(Now()-90),Month(Now()-90),1)) And ([PDIR Due Date]
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,036
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Formula in a Query in the Feild

    I see, so use the Date Diff as my condition (Iff DatedDiff=3)

    I was going a much longer route IIF(([PDIR Due Date]>DateSerial(Year(Now()-90),Month(Now()-90),1)) And ([PDIR Due Date]
    Yep, no need to covercomplicate it!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,723
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula in a Query in the Feild

    Don't forget that if expressions start getting real hairy, you can call a function from a calculated query field as well.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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