Results 1 to 9 of 9
  1. #1
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121

    Update Query error

    Hello,



    In the attached database I have a table called APR with rates for six month periods. It has been updated with a new rates for the periods beyond 06/30/2020. I created a select query with the following formula, however it only runs through 06/30/2020 for some reason. Once I get this to work for all dates, I need to change it to an update query so it changes the daily apr accordingly.


    IIf([Daily APR]![MDY]>=[APR]![From],IIf([Daily APR]![MDY]<=[APR]![To],[APR]![APR]/DateDiff('d',[APR]![From],[APR]![To])))
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I *think*, you are missing the arguments for when the IIFs are False?

  3. #3
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121
    even when I add an else 0 I still get the same result

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    suggest you put into words what your criteria is supposed to do - looks something like

    if dailyAPR.MDY is between APR.From and APR.To then include dailyAPR records where DailyAPR.APR=apr.apr divided by the difference between apr.from and apr.to

    which makes no sense to me at all. What is the purpose of this query? why use iif? the between part can just be applied to the MDY field. Splitting it shows that whatever this formula is supposed to do it is not doing what you expected.

    other comments - From and To are reserved words and should not be used as field names. Also you should not have fields named the same as its parent table

  5. #5
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121
    new day new perspective on this, what I should have said was:
    if dailyAPR.MDY is between APR.From and APR.To then include dailyAPR records where DailyAPR.APR=apr.apr divided by 12

    I did figure out how to use this as a formula in the query without having to update a table to reference. I was formula and update query challenged that day. Thanks for the help.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You should not use "FROM" or "TO" as field names - they are reserved words in Access.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    @steve - already pointed in in post #4

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Ajax - either great minds think alike or I need to turn off my speed reading option........


    Thanks........

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'll go with great minds....

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

Similar Threads

  1. update query error help
    By raylward102 in forum Access
    Replies: 6
    Last Post: 10-24-2019, 03:05 PM
  2. update query error
    By koncreat in forum Forms
    Replies: 7
    Last Post: 02-22-2017, 01:53 PM
  3. Update query error
    By msaccessdev in forum Access
    Replies: 1
    Last Post: 06-06-2014, 06:08 AM
  4. Update query Error
    By rd.prasanna in forum Queries
    Replies: 1
    Last Post: 10-04-2013, 03:01 AM
  5. Update query error
    By dccjr in forum Queries
    Replies: 6
    Last Post: 05-21-2013, 08:06 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