Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2017
    Posts
    2

    Unanswered: replacing blank fields with a set term

    I am very new to Access, so please keep in mind when replying. I have created a database where I can track new hires and members who will either transfer to another organization or retire. When creating my query I have certain dates that apply to when the person will leave. For example, if someone is going to retire I know what date that will be, but what is not know is if that person will take vacation before leaving. This "unknown" date is used to calculate when other administrative items are due to be completed. In my query I cannot get the "unknown" date (which is a blank field) to populate with TBD.

    Expr16: IIf([TYPE OF LOSS]="RETIREMENT",[Expr13],IIf([TYPE OF LOSS]="SEPARATION",[Expr13],IIf([TYPE OF LOSS]="TRANSFER",[Expr10],Null)))

    *(Expr13 subtracts 30 days from the known retirement/separation date and Expr10 subtracts 30 days from the known transfer date.) <---this works fine no problems.

    I want to take a step further by saying if the retirement date is blank (meaning I haven't populated that field with the date yet) I want it to tell me TBD (is there something saying I can't use TBD, because it always wants to put it in brackets?)

    Something like this is what I was thinking, but get an error saying wrong number of arguments:

    IIf([RETIREMENT DATE] is null,”TBD”,[RETIREMENT DATE],IIf([TYPE OF LOSS]="RETIREMENT",[Expr13],IIf([TYPE OF LOSS]="SEPARATION",[Expr13],IIf([TYPE OF LOSS]="TRANSFER",[Expr10],Null))))

    If I do IIf([RETIREMENT DATE] Is Null,0) this works but doesn't because it assigns "0" to the TRANSFER personnel as well.

    Hope this isn't too confusing. Thanks everyone.

  2. #2
    Join Date
    Dec 2016
    Posts
    22
    I think part of the problem may be that the field is set up as a date, and "TBD" is a string. You can either have the field as a date (numeric) or as a string, but not both.

    You could set a dummy date for retirement in, and then know that a date of 1/1/2199 is a dummy date.

    From your second to last sentence, I think that that is the problem. 0 is a numeric value, which is why it logically works in the formula.

  3. #3
    Join Date
    Jan 2017
    Posts
    2
    Thanks for the quick reply, another coworker mentioned that might be the case as well.

    Is there another work around? Only reason I ask is that I forward this report to others outside of my organization. They then compile this plus other data into a single source and don't want to have to explain each time about the dummy date. Not sure how much that would throw off their information either.

  4. #4
    Join Date
    Dec 2016
    Posts
    22
    I don't know how to work around that. There must be a way, given how prevalent this problem must be in personnel issues, but I do not know of a way. Maybe someone else can jump in here?

  5. #5
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,102
    Provided Answers: 17
    You can use the Nz function in queries to return a set value instead of null:
    Code:
    Nz([RETIREMENT DATE], "TBD")
    It might help if you could provide some sample data from your tables and an example of what you need at the end of it.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •