Dates are driving me nuts, prunes aren’t anybetter

Topic

New Reply

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

Reply | Quote

Replies

Check out the DateDiff function in on-line help

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 ADMITtoEOM:DateDiff(“d”,[ADMIT DATE],[ENDING DATE]).

Reply | Quote