Results 1 to 6 of 6
  1. #1
    effeinmilton is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    3

    Want to have a null value formal return with a text notation

    Here is the formula I am using, it is been put into a unbound text box in the control source. It is a simple calculation of two years from the date of purchase.

    =DateSerial(Year([FIELD1])+2,Month([FIELD1]),Day([FIELD1]))

    If there is no purchase of this item, I want it to read, "NO PRODUCT"

    Right now the box is showing #Type!, when there is no information in FIELD1 to pull from.



    Any assistance would be greatly appreciated.

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Want to have a null value formal return with a text notation

    If (isnull([field1]) = true then "noproduct", dateserial etc)

    Year, month etc need a date field and return a null when field1 is empty.

    Then the dateserial function doesn't get the integers it needs.


    Sent from my iPhone using Tapatalk

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I think full line in the control from Andy49 code would be:

    = IIf (Isnull([Field1]), "NO PRODUCT", DateSerial(Year([FIELD1])+2,Month([FIELD1]),Day([FIELD1])))

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it is not a good idea to mix datatypes in a single control (date and text)

    you could use the dateadd function instead

    =dateadd("yyyy",2,[Field1])

    so if field1 is null, there will be nothing to show in your unbound textbox (i.e. null). However since the value is null, you can use the format property of your unbound textbox

    dd/mm/yyyy;;;"no product"

    or if you want to highlight the lack of a date, as an alternative to conditional formatting

    dd/mm/yyyy;;;[Red]"no product"

  5. #5
    effeinmilton is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    3
    Thank you it worked out perfectly!

  6. #6
    effeinmilton is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    3
    Thank you, I really appreciate the full line of the code.

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

Similar Threads

  1. Replies: 1
    Last Post: 09-15-2015, 02:16 PM
  2. Replies: 2
    Last Post: 06-03-2013, 11:02 AM
  3. Return Null as Zero
    By bbshriver in forum Reports
    Replies: 12
    Last Post: 10-19-2010, 01:49 PM
  4. No return on Null values
    By forrestapi in forum Queries
    Replies: 4
    Last Post: 10-18-2010, 08:09 AM
  5. Return 0 instead of null
    By salisbut in forum Queries
    Replies: 1
    Last Post: 08-07-2010, 12:01 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