Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Posts
    138

    Unanswered: Date Add Function

    I am trying to filter dates based on the DateAdd field in my query. It will not allow me to use specific criteria in the query under the DateAdd field. I can filter if I use enter the date in the expression such as Between #04/01/2005# And #05/01/2007# in the DateAdd field. I want to filter the criteria based on the parameters I enter in a form. I am using this as my expression.

    Between [Forms]![Employee Information]![Start Date] And [Forms]![Employee Information]![End Date]

    This expression works fine in all fields except the dateadd field

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    have you considered that dateadd is a reserved-word in A?

    you might get away with using '[dateadd]' where you currently have 'dateadd' but you would be better off not using reserved words for names.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2003
    Posts
    138

    Date Add Function

    This is what is in my field in the query

    DueDate: DateAdd("m",12,[CPR])

    For criteria I have the expression stated

    Between [Forms]![Supervisors Access Form]![Start Date] And [Forms]![Supervisors Access Form]![End Date]

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ooops - looks like you know dateadd() is a function: sorry for assuming silly things.

    can you do this one-step in A?
    i never tried.

    if you don't have a humungous dataset (!!! in A?), try it two-step:
    qry1 does the dateadd
    qry2 returns rows from qry1 matching the Between

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2003
    Posts
    138
    Attempted this by making a sub query...still no luck. Is there a way to filter or define criteria based on a projected date?

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    gotta watch the footie, so someone else needs to step in.
    copy/paste your query SQL view here (one-step & the two-step attempt)

    ...is it the US-date format gremlin?

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Is it possible that one of your 3 date arguments only LOOKS like a date?
    Try enclosing each one in CDATE() and see what happens.
    Inspiration Through Fermentation

  8. #8
    Join Date
    Dec 2003
    Posts
    138
    Initial Query SQL

    SELECT Employees.LastName, Employees.FirstName, Employees.CPR, DateAdd("yyyy",1,[CPR]) AS DueDate
    FROM Employees
    GROUP BY Employees.LastName, Employees.FirstName, Employees.CPR, DateAdd("yyyy",1,[CPR]), Employees.TerminationDate
    HAVING (((Employees.TerminationDate) Is Null)) OR (((DateAdd("yyyy",1,[CPR])) Between [Forms]![Supervisors Access Form]![Start Date] And [Forms]![Supervisors Access Form]![End Date]));


    Subquery SQL

    SELECT [CPR Update Due].*, [CPR Update Due].DueDate
    FROM [CPR Update Due]
    WHERE ((([CPR Update Due].DueDate) Between [Forms]![Supervisors Access Form]![Start Date] And [Forms]![Supervisors Access Form]![End Date]));

  9. #9
    Join Date
    Dec 2003
    Posts
    138
    Ignore the first Query SQL, this is the right one. I goofed

    SELECT Employees.LastName, Employees.FirstName, Employees.CPR, DateAdd("yyyy",1,[CPR]) AS DueDate
    FROM Employees
    GROUP BY Employees.LastName, Employees.FirstName, Employees.CPR, DateAdd("yyyy",1,[CPR]), Employees.TerminationDate
    HAVING (((DateAdd("yyyy",1,[CPR])) Between [Forms]![Supervisors Access Form]![Start Date] And [Forms]![Supervisors Access Form]![End Date]) AND ((Employees.TerminationDate) Is Null));

  10. #10
    Join Date
    Dec 2003
    Posts
    138
    REDNECKGEEK

    Please provide example. I only have one date argument, I think...I guess what dies CDATE() mean????

  11. #11
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    CDate = convert to date. This takes data that looks like a date, but is really a string or possibly serial number, and converts it to date, or returns an error if it can't convert. My guess is it's the data you are pulling from the form.

    SELECT Employees.LastName, Employees.FirstName, Employees.CPR, DateAdd("yyyy",1,CDATE([CPR])) AS DueDate
    FROM Employees
    GROUP BY Employees.LastName, Employees.FirstName, Employees.CPR, DateAdd("yyyy",1,[CPR]), Employees.TerminationDate
    HAVING (((Employees.TerminationDate) Is Null)) OR (((DateAdd("yyyy",1,[CPR])) Between CDATE([Forms]![Supervisors Access Form]![Start Date]) And CDATE([Forms]![Supervisors Access Form]![End Date])));
    Inspiration Through Fermentation

  12. #12
    Join Date
    Dec 2003
    Posts
    138
    Thank You!!! It Works, How Simple!!!!

    Thank You

Posting Permissions

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