Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    19

    Unanswered: Parameter not recognizing year in my search,

    All,

    I have a date field, and then I have a term (e.g. 7, 8, 9). I have calculated a field in my query that looks at the date field, and then projects out X # of years based on the term. For instance, 1/1/2002 + 7= 1/1/2009. No issue there, but when I try a parameter search by month. For instance, give me all accounts that are coming up for renewal May of 2010. The result is that I get all accounts that were ever opened in May. So, something in the way I formatted this field is creating an issue.

    Here is the syntax...

    Surrender_Dt: DateAdd("yyyy",[policies with Exp Dates.Term],[Policies with Exp Dates.Written Date])

    Term being 7,8,9 and Written Date being short date.

    I know it's probably something simple.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    So

    Surrender_Dt: DateAdd("yyyy",[policies with Exp Dates.Term],[Policies with Exp Dates.Written Date])
    or
    Surrender_Dt: DateAdd("yyyy",[policies with Exp Dates].[Term],[Policies with Exp Dates].[Written Date])

    is an expression in the query? If so, is there criteria under this expression column in the query that's giving you problems? Could you possibly elaborate on "but when I try a parameter search by month" and what parameter criteria your using?

    Often times applying criteria under a calculated column in a query with date type calculations can be problematic. Sometimes it's easier to use a separate column (without the dateadd type calculation) just for the criteria (or create another query based upon this query and apply the criteria in that query).

    I guess I don't fully understand where you're applying the parameter search by month.
    Last edited by pkstormy; 04-08-10 at 19:14.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    May 2009
    Posts
    19
    So, under the expression, I did add the parameter to search all policies by a specified month and year. It did bring back results, but it was only taking into account policies across the month I specified, but not the year as well. Attached. Any thoughts?
    Attached Files Attached Files

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm assuming the linked table is not playing a role in your problem. I wouldn't put the criteria under the dateadd expression column though. Have you considered just adding the Surrender_dt field to the table as another date field versus using the dateadd with the 'Term' and 'Written Date' field or does that not work for the way the program operates?

    There may be a simple solution but I'm not seeing it. I don't really put criteria under a calculated date expression since I usually have issues. I might try another query based on this query and then put your criteria in that query (ie. under the Surrender_dt field in the 2nd query since the query then wouldn't be calculating the dateadd and applying the criteria at the same time.)
    Last edited by pkstormy; 04-08-10 at 21:45.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    May 2009
    Posts
    19
    I think I resolved my issue last night after stumbling across another post that stated to try restating the parameters under the "Query">"Parameters" then updating the "Parameter" and "Data Type" in the build view....it seemed to work.

Posting Permissions

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