Results 1 to 5 of 5
  1. #1
    millby2000
    Guest

    Dates are driving me nuts, prunes aren't anybetter

    Sorry about the little humor (very little) but it helps me from going nuts. Now for my problem -

    I am using Access 97.

    I have two controls on a FORM (PTFORM) (1) Admit Date and (2) Discharge Date. Both are in a date format. Both controls are bound to separate date fields in a table (PTTBL), through a query (PTQUERY).

    My problem is related to a report that I am trying to generate. My report is set up using a Query that pulls data from the PTTBL. When I run the report a parameter box appears asking for the Beginning Date and the Ending Date. Generally, the report is run on a calendar month basis. (eg 3/1/01 to 3/31/01.) The data in the report generates the data correctly from my Table based on the Beginning and Ending dates entered.

    One of the fields in the report is a calculated field. I am trying to calculate the number of days between the Admit date and the last day of the month for whatever month period the parameter is set for. I have tried to set up an expression in the Query tied to the report such as:

    ADMITtoEOM:SUM[ENDING DATE]-[ADMIT DATE]
    The ENDING DATE is the second parameter date field entered at the time I run the report and the ADMIT DATE is from the table itself.

    When I run this report, it won't run. I get an error suggesting I set up a variable of some sort.

    Any suggestions? Have I totally confused the issue? Or do I need more prunes?

    Thanks for your help.
    Steve

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates are driving me nuts, prunes aren't anybetter

    Check out the DateDiff function in on-line help

  3. #3
    kbahr99
    Guest

    Re: Dates are driving me nuts, prunes aren't anybetter

    If I understand your code correctly, it looks like to field names in your database have a space in them? If so, you should rename them. Also avoid using field names that are reserved words (such as DATE and MODULE). Unfortunately, Access doesn't give you a warning message when you do things like that and even the best of us can fall into that pitfall.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates are driving me nuts, prunes aren't anybetter

    have you tried doing the calculation in the report instead of the query
    hth

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Dates are driving me nuts, prunes aren't anybetter

    Unless you need to find the sum total of differences for a number of records for the same person, you don't need the "SUM" part ADMITtoEOM:[ENDING DATE]-[ADMIT DATE] should do the trick.

    However, it doesn't seem to work properly if you're relying on the query assuming all undefined "variables" are query parameters, you need to actually define [ENDING DATE] as a parameter of type DateTime in the query.

    A further alternative is to use ADMITtoEOMateDiff("d",[ADMIT DATE],[ENDING DATE]).

Posting Permissions

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