Results 1 to 8 of 8
  1. #1
    grapevine is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    22

    Acess WEBAPPS calculated field

    I am trying to write a query with access WEBAPPS to show training records where the review date is within 90 days of the current date. It appears that TODAY must be used not DATE because of the SQL Server interface but I still cannot get this simple query working. The field does contain empty fields with no date in, if perhaps this might be causing some of the problem. I am sure this must be a very simple fix, but I really cannot see what I am doing wrong. Any help gratefully received.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	12 
Size:	9.6 KB 
ID:	27080

  2. #2
    DOCBigSky is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2017
    Posts
    5
    Quote Originally Posted by grapevine View Post
    The field does contain empty fields with no date in, if perhaps this might be causing some of the problem.
    I think the null values in your fields is preventing calculation. I am having the same problem. My understanding is in order to perform a calculation in a field, all the source fields must have some value. You can take care of this when setting up by adding a default value to the field, but if you are adding in the field after the fact you have to populate the empty fields yourself for the calculations to work.

    I am pretty new at this, so it is likely that I have left something out or gotten something wrong!

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The Nz() function can be used around fields that might be Null.
    Last edited by pbaldy; 01-31-2017 at 11:58 PM. Reason: fix goof
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    DOCBigSky is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2017
    Posts
    5
    I thought NZ() didn't work in Access web apps?

  5. #5
    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 haven't used a web app. Did you try it?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    DOCBigSky is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2017
    Posts
    5
    I did, but I just did a quick drive-by to check. I'll try it again and double check my syntax more carefully. I don't have confirmation that it definitely does not work in Web Apps...

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You may very well be right. If so, perhaps an IIf() with IsNull()?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    or you could use a different formula

    >=Today()+90

    comment removed - didn't notice the reference to SQL Server in the first post
    Last edited by CJ_London; 02-01-2017 at 06:15 PM.

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

Similar Threads

  1. Replies: 1
    Last Post: 02-03-2016, 06:54 PM
  2. Replies: 8
    Last Post: 01-06-2016, 02:52 PM
  3. Replies: 2
    Last Post: 12-30-2014, 01:32 PM
  4. Replies: 2
    Last Post: 12-15-2013, 02:29 AM
  5. Replies: 1
    Last Post: 04-21-2013, 03:20 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