Results 1 to 7 of 7
  1. #1
    tuniwes is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    39

    calculated date field asking for a parameter when I filter it with date()?

    I don't understand what is going on here, any help would be much appreciated

    I have a calculated field called ofs that determines how many days to subtract from a date based on the day of the week

    ofs: IIf(Weekday([dell date])=2 Or Weekday([dell date])=3 Or Weekday([dell date])=4,5,3)

    Then I am using this in the calculated field below

    dd: IIf(Left([tbs.co],1)="t",[del date]-[ofs],[due date])

    Works fine until I filter with date()

    Then Access asks for parameter for OFS?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,776
    I cannot replicate the issue. It all works for me.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,049
    If your del date field is a date field then use datediff([del date],"d",[ofs])

    Look up datediff function for details.

    And start to consider naming your fields with no spaces. A general rule!!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,776
    Certainly use DateDiff if you want to calculate time unit other than days. Days is default unit when doing math with date values.

    I thought you meant Calculated type field in table. That's what I tested and it works.

    However, just tested calculated fields in query and that does trigger the popup, even with use of DateDiff().

    So, options:

    1. repeat the ofs calculation within the dd expression

    2. don't apply filter in query, use code (VBA or macro) to apply filter to form or report, such as in the OpenForm or OpenReport command

    3. use table Calculated type field
    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.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,378
    Isn't that because the calculated field (alias ofs is being referenced by the criteria, thereby creating a circular reference at worst and an ambiguous situation at the least?
    Last edited by Micron; 05-26-2017 at 03:44 PM. Reason: delete smiley when trying to use a colon
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,433
    I believe the problem is caused by the order in which clauses are evaluated. Because the WHERE clause is evaluated before the SELECT clause, the alias is unknown at the point Access (JET/ACE) is trying to evaluate/apply the criteria.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    tuniwes is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    39
    Quote Originally Posted by June7 View Post
    Certainly use DateDiff if you want to calculate time unit other than days. Days is default unit when doing math with date values.

    I thought you meant Calculated type field in table. That's what I tested and it works.

    However, just tested calculated fields in query and that does trigger the popup, even with use of DateDiff().

    So, options:

    1. repeat the ofs calculation within the dd expression

    2. do the dd calculation in textbox on form or report

    3. use table Calculated type field


    Thanks very much June7...option 1 did the trick, no need for the OFS field..added the logic into the second field and it works fine

    dd: IIf(Left([tbs.co],1)="t",[del date]-IIf(Weekday([del date])=2 Or Weekday([del date])=3 Or Weekday([del date])=4,5,3),[due date])

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

Similar Threads

  1. Replies: 4
    Last Post: 11-20-2018, 11:57 PM
  2. Replies: 20
    Last Post: 04-18-2017, 03:19 PM
  3. Add a calculated date field
    By jenncivello in forum Access
    Replies: 3
    Last Post: 07-03-2014, 09:15 AM
  4. Can I use a Parameter on a Date/Time Field?
    By Paul H in forum Queries
    Replies: 5
    Last Post: 04-24-2012, 03:12 PM
  5. Replies: 3
    Last Post: 04-01-2012, 01:40 PM

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 - Senior Forums