Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date calculation returns invalid date? (2002)

    I've read some threads that help me understand why I'm having trouble with this but not specifically my problem.
    I have a calculated date field in my query for Maintenance Orders that returns a "Date Due" based on how many days it is assigned to be completed:
    Due By: ([DateEntered]+[DaysForCompletion]).

    The users now want a report based on this "Due By" date. I understand that this is not a true date because of it being calculated. I need to create a date parameter report based on this invalid date. I've been using "Between [Forms]![frmDate]![BeginDate] And [Forms]![frmDate]![EndDate]".

    I created another query based on the original to try to convert the date, using: Format([DueBy], "mm:dd:,yyyy")
    This returns data but it is not correct.

    Is there a way to do this in Access without a lot of programming? I'm not very good at that yet....
    Thanks so much..
    Vicky

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date calculation returns invalid date? (2002)

    You shouldn't use Format, for that returns a string value, not a date. You should be able to set the criteria

    Between [Forms]![frmDate]![BeginDate] And [Forms]![frmDate]![EndDate]

    directly in the first query, in the Due By column. No need to create a second query.

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date calculation returns invalid date? (2002)

    Thanks Hans,
    I've been trying your suggestion.
    When I put a date range into the form, say, 4/15/04 - 5/15/04, I get all data returned that is specified in other criteria:

    ( "Completion Date" is null, "Late: DateDiff("d",[Due By],Date()) is >0) ,

    but the date range does not limit my data to between these dates.
    I've had this trouble before and had to use the "Totals" and "Where" - Between [Forms]![frmDate]![BeginDate] And [Forms]![frmDate]![EndDate], but, if I do that, it asks for a parameter for the "Due By" calculation - like it doesn't recognize the calculation.

    I have a similar query that uses the same criteria except the "Between [Forms]![frmDate]![BeginDate] And [Forms]![frmDate]![EndDate]" is based on a regular date field [DateEntered], not a calculated field and it works fine.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date calculation returns invalid date? (2002)

    I don't think it is the calculation by itself - I have attached the demo I used for my previous reply; it works OK on my PC (database in Access 2000 format created with Access 2002 SP-2.)

    You might try specifying the parameters explicitly: open the query in design view, and select Query | Parameters.
    Enter [Forms]![frmDate]![BeginDate] in the Parameter column and set the Data Type to Date/Time.
    Enter [Forms]![frmDate]![EndDate] in the Parameter column in the second row and set the Data Type to Date/Time.
    Click OK and save the query.
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date calculation returns invalid date? (2002)

    Thank you so much!
    It works fine now. I didn't realize that you could specify the parameters explicitly. I've needed this in the past.
    Vicky

Posting Permissions

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