Results 1 to 8 of 8
  1. #1
    dpfaff is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    16

    DateAdd function in VBA: reference date value from table instead of a static date?

    Hi, I have this code for a field value in a query, which works well to add 35 days to the date specified and append it to a table.



    Code:
    Function AddSalesDays()
    
    AddSalesDays = DateAdd("d", 35, "1/11/14")
    
    
    'weekend results bumped to Monday:
          Temp = Format(AddSalesDays, "ddd")
          If Temp = "Sun" Then
             AddSalesDays = AddSalesDays + 1
          ElseIf Temp = "Sat" Then
             AddSalesDays = AddSalesDays + 2
          End If
    
    End Function
    However, rather than specifying the static "1/11/14" date, I want to reference an existing date field CI_CREATED DATE from table WC_CLIENT_ISSUE

    But I don't know the syntax to accomplish this and was hoping someone here could assist. When I try the following, I get a type mismatch run-time error:

    Code:
    AddSalesDays = DateAdd("d", 35, "[WC_CLIENT_ISSUE].[CI_CREATED_DATE]")

  2. #2
    drexasaurus's Avatar
    drexasaurus is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Santa Ana, CA
    Posts
    60
    If you are coding, and want to use the date in the form control, you have to reference the control explicitly: Me.CI_Created_Date.value, or something like that. If you want to reference a table in the database, not necessarily tied to a form, you are going to have to retrieve the data first in a recordset or use a function like DLookup (example: DLookup("CI_CREATED_DATE", "WC_CLIENT_ISSUE", "ISSUEID=76")).
    Then you can work with the date. You can't simply reference the table and field in a string, even on a form that has a dataset loaded into it.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why even save the calculated value? Saving calculated data (data dependent on data) is usually unnecessary and often bad practice. The date can be calculated whenever needed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    drexasaurus's Avatar
    drexasaurus is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Santa Ana, CA
    Posts
    60
    Oh, also, I missed that you were doing this in a query somehow... because of how the function was written I guess. If you want to use the date from the query in the function, you'll have to pass the date to the function by changing the definition to something like
    Function AddSalesDays(dateToAlter as Date)

    And change the function to use the variable:
    DateAdd("d", 35, dateToAlter)

    And then refer to the tables column in the query designer itself, not the function directly, by passing the column to the function
    In the query designer, instead of referring to the column name directly in the "Field" (like [CI_CREATED_DATE]), type something like this:
    AlteredDate: AddSalesDays([CI_CREATED_DATE])

    This will create a column in your query called Altered date that passed CI_CREATED_DATE to the function for every row. Note that access is pretty slow about this, and it might not be a very fast query, especially if you must process a lot of rows.
    Also note that the column name is in [] brackets, NOT quotes. If you pass the name in quotes (which Access will sometimes try to do for you if it doesn't understand that you are trying to pass the column), the function will think you are trying to pass a string to the function. If you are having trouble, type it with the brackets, which will prevent access from adding the quotes for you.

  5. #5
    dpfaff is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    16
    I appreciate the responses. I'm going to try working with the info DRex posted.

    June, that's a good point and I see what you mean. Since the Issue Created Date is already saved, it isn't necessary to save the date that calculates based on it. Once I have this database functional I will probably try some enhancements. I noticed adding the new date field did slow down the import. Not sure how to achieve what I want....possibly building all the code into the report criteria?

    Thanks!

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Calculate the date when needed - a report is one place. The calculation can be in query or textbox. Maybe even in table Calculated field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    dpfaff is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    16
    I got this to do what I want:

    Expr3: AddSalesDays([WC_CLIENT_ISSUE].[CI_CREATED_DATE],35)
    in the "field" row of the Query

    Date to Call Sales Rep in the Append To row

    Updated code:
    Code:
    Option Compare Database
    Function AddSalesDays(ByVal TheDate, ByVal Interval)
    Dim Temp As String
     
    AddSalesDays = DateAdd("d", 35, TheDate)
    
    '************************************************
    'weekend results bumped to Monday:
          Temp = Format(AddSalesDays, "ddd")
          If Temp = "Sun" Then
             AddSalesDays = AddSalesDays + 1
          ElseIf Temp = "Sat" Then
             AddSalesDays = AddSalesDays + 2
          End If
    
    End Function
    Thanks again for the responses, it really helped me to understand what the code was doing.

  8. #8
    dpfaff is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    16
    I added this bit at the end to format the returned date to Short Date. Having a timestamp causes problems with querying by date range (e.g. there are Sales Call Dates equal to 2/5/14 but they wouldn't appear unless I extended the end date to 2/6/14)

    Code:
    AddSalesDays = Format([AddSalesDays], "mm/dd/yyyy")

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

Similar Threads

  1. DateAdd Function
    By jschlapi in forum Forms
    Replies: 4
    Last Post: 10-23-2013, 01:36 PM
  2. Open Report w DateAdd function
    By libraccess in forum Programming
    Replies: 3
    Last Post: 02-21-2013, 12:31 AM
  3. DateAdd function for workdays?
    By 10 Gauge in forum Programming
    Replies: 2
    Last Post: 04-06-2011, 09:20 AM
  4. DateAdd Function
    By Desstro in forum Programming
    Replies: 3
    Last Post: 04-03-2011, 01:45 PM
  5. Help Please - DateAdd Function
    By graviz in forum Queries
    Replies: 3
    Last Post: 03-02-2010, 02:34 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