Results 1 to 9 of 9
  1. #1
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108

    issue with dates on reports, need to display text if date = xxx

    Hello,


    I don't even know if this is possible since I am working with a date field but thought I would ask. To avoid having null values in my tables I have a default value populated in a date field (1/1/1900). there is a related text field (VRAprobation) and it is a yes/no/"N/A" field. basically what I need to do is if the date field is 1/1/1900 I want it to show on the report as either "N/A" or just blank. I have tried the following IIF statements:


    IIf([VRAprobationStartDate]=1/1/1900,N/A,[VRAprobationStartDate])
    IIf([VRAprobation]="N/A",N/A,[VRAprobationStartDate])
    IIf([VRAprobation]="N/A",Null,[VRAprobationStartDate])
    IIf([VRAprobation]="N/A",Is Null,[VRAprobationStartDate])
    IIf([VRAprobation]="N/A",,[VRAprobationStartDate])

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    IIf([VRAprobationStartDate]=1/1/1900,”N/A”,[VRAprobationStartDate])
    Assuming that VRAprobationStartDate is a Date Data Type and not Text, when you have: 1/1/1900 like that in your formula, it is treated as "1 divided by 1 divided by 1900".
    Try this:
    Code:
    IIf([VRAprobationStartDate]=DATEVALUE("1/1/1900"),"N/A",[VRAprobationStartDate])
    or this:
    Code:
    IIf([VRAprobationStartDate]=DATESERIAL(1900,1,1),"N/A",[VRAprobationStartDate])
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    JoeM,
    Thanks for the reply, Yes the VRAprobationStartDate is a date field. when I try the provided (I tried both of them) code I get "This expression is type inccorectly , or is too complex to be evaluated." error. I copied and pasted and tried keying it in by hand.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Where exactly are you putting this calculation?
    In a Query (as a Calculated Field)?
    Directly on the Report (in the Control Source of a Text Field)? If so, be sure to put an equal sign before the word IIF, and make sure that the VRAprobationStartDate field is actually found in the Data Source for this Report.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  5. #5
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    I am adding it in the query that pulls the data for the report. The report works if I run it w/o trying anything to do anything to solve my issue.

  6. #6
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    im adding it into the Criteria field in the query

  7. #7
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    No, you cannot add it to the Criteria line in the Query. That is only for record selection purposes. You cannot change/alter values there.
    You want to put it in a new calculated field in a query.
    See: https://www.youtube.com/watch?v=pAnfjCu0c-Q
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  8. #8
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    Thanks JoeM!!! that was the issue, thank you so much!!! so any time want to manipulate the data like this I have to make a new expression/variable and do it there?

  9. #9
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    That is usually the best way to do it, IMO.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

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

Similar Threads

  1. Replies: 7
    Last Post: 09-10-2015, 04:52 PM
  2. Replies: 9
    Last Post: 04-18-2014, 08:51 PM
  3. Replies: 4
    Last Post: 04-10-2012, 01:35 PM
  4. Replies: 1
    Last Post: 02-17-2012, 04:43 PM
  5. Replies: 0
    Last Post: 07-27-2009, 07:51 AM

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