Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2002
    Posts
    6

    Unanswered: DateAdd in a Query

    Requesting help with the following:

    Table Name: calibrationlist
    Fields: Date -- last calibration date
    Frequency -- number of months that calibration will be
    due from the last calibration date.


    Created a calculated field in a query as follows:

    DueDate: =DateAdd("m",[frequency],[calibrationlist]![date]

    Need to create a report based on the above query for specific time frames so we created a prompt in the DateAdd calculated field as follows:

    Between [Type the start date:] and [Type the end date:]

    When we run the query using 1/1/02 through 12/31/02 we get the result of the months that start with the number "1" including the years 2003 and 2004.

    Also tried using a calculated DateAdd in the form to base the report off this form and field without success. Our thought was to create another field with the result of the DateAdd calculated field and somehow change it to a value. Was not successful with this either.

    Would appreciate any help you can give us.

  2. #2
    Join Date
    Sep 2002
    Location
    Coventry, Warwickshire, UK
    Posts
    74
    How have you defined the column called Date on you table. Is it text or Date/Time? If it is Text, then you will get unpredictable results. Date/Time fields are stored as decimal numbers in Access; the integer part representing the date and the decimal part representing the time.

    It is inadvisable to use reserved words (Date) by the way. I would call it something like CalibrationDate.

    Your BETWEEN statement should read

    BETWEEN #[Type the start date:]# AND #[Type the end date:]#

    and the dates must be in mm/dd/yyyy format or a format that is unambiguous.
    Roger Hampson
    XI - ecs (UK) Ltd

  3. #3
    Join Date
    Aug 2002
    Posts
    6

    Red face Additional Information Regarding DateAdd in a Query

    Thank you for your quick response.

    We followed all of your suggestions and changed the name of the field and the date format to mm/dd/yyyy. The query seems to work with either individual dates or with specific Between #9/1/2002# AND #9/26/2002# for example, but when we try to set it up for a parameter query using the Between #[Type the beginning date:]# AND #[Type the ending date:]# we get the following error:

    " expression you entered has an invalid date value "

    Do you know what is preventing that query from working properly.

    Thanks again.

    QUOTE]Originally posted by xiecsuk
    How have you defined the column called Date on you table. Is it text or Date/Time? If it is Text, then you will get unpredictable results. Date/Time fields are stored as decimal numbers in Access; the integer part representing the date and the decimal part representing the time.

    It is inadvisable to use reserved words (Date) by the way. I would call it something like CalibrationDate.

    Your BETWEEN statement should read

    BETWEEN #[Type the start date:]# AND #[Type the end date:]#

    and the dates must be in mm/dd/yyyy format or a format that is unambiguous.
    [/QUOTE]

  4. #4
    Join Date
    Sep 2002
    Location
    Ann Arbor, MI
    Posts
    47
    I have a very similar query, and the field is formated as a time/date field. I use this:

    Between [Type the begin Date] And [Type the Ending date]

    And it works flawlessly. I can type in dates in any format: 9/1/01, 09/1/02, 09/01/02, 9/01/2002 etc.

    You may want to look at your Short Date settings under the Date tab of your Reginal Options in the Control Panel. (mine are set to M/d/YY)

    Good Luck!

    p.s. are you happy with your calibration database? - Thinking of writing one to replace ours...

  5. #5
    Join Date
    Aug 2002
    Posts
    6

    DateAdd Problem

    Thank you for your response. We followed your suggestion in changing the dates to the "short date" format, but it didn't resolve the problem. Your suggestion works for a bound field with the dates. I believe the problem that we are encountering is because of the "dateadd calculated field."

    The database works very well with the exception of this problem.

    Would you know how to transfer the result of the dateadd calculation to another bound field that we could base the report off of? This could also be a solution.

    Thanks

  6. #6
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    If the date parameter is taken from a form, then use the following syntax.

    Between [forms]![Formname]![ControlName] And [forms]![Formname]![ControlName]

    David

  7. #7
    Join Date
    Aug 2002
    Posts
    6

    DateAdd Reply

    Dave,

    Your suggestion on taking the field from the form? Would we have to change the calculated field to a value first? If so, how do you change a calculated field in Access to a value?

    Thanks again for replying.

  8. #8
    Join Date
    Aug 2002
    Posts
    6

    DateAdd Query Solution

    Was able to get the query to work properly by changing the "Parameter Type" to date/time format located in the query (menu).

    This allowed me to eliminate the # sign before and after the parameter and resulted in the correct query dates.

    Thanks to all the assisted with this problem.

Posting Permissions

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