Results 1 to 5 of 5
  1. #1
    arbucklemw is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    10

    Add days to a field based on a text field

    I'm trying to figure out, using either a query or a report, how to fill an empty date field based on the date in another field, but also how many days are added to the original date for the empty field based on a text field.



    I have the below fields in my query, in addition to a few other that will have nothing to do with the calculation.

    Text field contains:
    Carrier
    Hazmat
    Passenger

    Request Date field (which will be previously populated by the user on a form)

    Due Date field
    IIf([txtCarrierType]="CARRIER", [dteUpgradeRequestDate] DateAdd(+45), (+30))

    I've tried variations of the above expression, and have googled my head off to no avail. What I want the calculation to do is to produce results that show the Due Date with 45 days added to the Request Date if the text field contains "Carrier" and 30 days added to the Request Date if either of the other 2 text options are present.

    Ultimately, i'd like to have the query populate the Due Date field in a table, but I'll cross that bridge once I figure out the above.

    Thanks much for any assistance you can give. I've taken a couple of classes following a previous post from me, but we never really dug into expressions and calculated fields.

  2. #2
    arbucklemw is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    10
    I'll add that I really don't care if this is done in a query, form, or report. Either works, and in the end I'd like the resultant date to appear on the form immediately after a user makes the choice of carrier type in the text field and enters the Request Date on the form. Maybe what I'm asking isn't doable in Access, so a query result of report result would be fine otherwise.

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you can build a table of tCarrierTypes, this will also have the Days2Add
    CARRIER,45
    Temp, 12
    etc

    In the query, connect the tData table to the tCarrierTypes and pull down the Days2Add field.
    NOW it can be used in anything: query,form,report

    =DateAdd("d",[Days2Add], dteUpgradeRequestDate)

  4. #4
    arbucklemw is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    10
    People just looked over at me at my desk as I laughed pretty loud when I read that. DOH! So simple. Thanks much!!!

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    FYI,
    Quote Originally Posted by arbucklemw View Post
    <snip>
    Request Date field (which will be previously populated by the user on a form)

    Due Date field
    IIf([txtCarrierType]="CARRIER", [dteUpgradeRequestDate] DateAdd(+45), (+30))

    I've tried variations of the above expression, and have googled my head off to no avail. What I want the calculation to do is to produce results that show the Due Date with 45 days added to the Request Date if the text field contains "Carrier" and 30 days added to the Request Date if either of the other 2 text options are present.

    <snip>

    The syntax for the DateAdd function is
    DateAdd(interval, number, date)
    interval = "d" (days)
    number = 45 or 30
    date = dteUpgradeRequestDate

    So the formula you were trying to create would be
    Code:
    =IIf([txtCarrierType] = "CARRIER",DateAdd("d", 45, [dteUpgradeRequestDate]), DateAdd("d", 30, [dteUpgradeRequestDate]))

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

Similar Threads

  1. Replies: 1
    Last Post: 12-09-2015, 10:23 AM
  2. Replies: 3
    Last Post: 10-26-2014, 06:35 PM
  3. Sum numerical filed based on other field
    By BorisGomel in forum Access
    Replies: 0
    Last Post: 07-11-2012, 09:54 AM
  4. Replies: 7
    Last Post: 02-10-2012, 08:08 PM
  5. Replies: 2
    Last Post: 05-05-2010, 02:52 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