query shows calculated datediff > 24 hrs

[jjb373]

My table has two date fields both having a date and time (ex: 12/05/2005 12:05:00 PM). My goal is finding the difference between 2 times. In a query, I converted this time difference from a date/time format to look like this: __ days __hrs __min.
I was wondering how I could also show only those times(records) that are greater than 24 hours with this query or using another query along with my current one. (anything being greater than 24 hrs is shown and anything under is not displayed.

Here is my current query:
SELECT Documents.[Record Number], Documents.[Date of Appointment], Int(DateDiff("n",Documents![Date of Appointment],Documents![HX Documents])/1440) & "day " & Format(DateDiff("n",Documents![Date of Appointment],Documents![HX Documents])/1440,"h""hr ""n") & "min" AS [HX Late], Int(DateDiff("n",Documents![Date of Appointment],Documents![Prospective Documents])/1440) & "day " & Format(DateDiff("n",Documents![Date of Appointment],Documents![Prospective Documents])/1440,"h""hr ""n") & "min" AS [Prospective Late], Int(DateDiff("n",Documents![Date of Appointment],Documents![External Documents])/1440) & "day " & Format(DateDiff("n",Documents![Date of Appointment],Documents![External Documents])/1440,"h""hr ""n") & "min" AS [External Late]
FROM Documents
WHERE (((Documents.[Date of Appointment]) Between [Enter Start Date (ex: mm/dd/yyyy)] And [Enter End Date (ex: mm/dd/yyyy)]));

Any advice is greatly appreciated.. I know this query is ugly.

 

[dhookom]

If I am reading between the lines correctly, you should be able to add to your Where clause:

And [HX Documents]-[Date of Appointment]>1

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jjb373]

That works thanks!

I have 2 more questions. First, I have 3 different fields which have totals are created by the query. One being the HX Documents as you described above. The other two are "external documents" and "prospective documents". If the HX document is less than 24 hours as your where clause states above and the external document and prospective document are both greater than 24 hours (i want to show those two), how would i go about doing that? Do I need each to be a seperate query??

Also, if the field has no time or data, how can i supress or hide that. When this occurs, I get the result of: " day min" because those are labels and are constant.

I hope this is all clear enough.

 

[dhookom]

Can you provide some sample records and desired display that would illustrate your question. I am having trouble getting my head around your fields. A field name of "HX Dcouments" that stores a date value confuses me.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jjb373]

Okay here is my table:

Number, Appointment Time, HX Documents, Prospective Documents, External Documents

The query I have is for a report. It asked for a start date and end date...mainly so we can track within a time span. The query as I copied above brings back to the user the: number, appointment time, difference between appointment time and HX document time, different between appointment time and prospective document time, and difference between the appointment time and external document time. These fields are HX Late, Prospective Late, and External Late.

The where clause says show all between user entered dates and anything greater than 24 hours. ( we want to track whatever is over 24 hrs. Anything less is good and does not need to be shown.

I want the report (which comes from the query) to show each number (or record) that has any of the 3 documents over the 24 hour time. So if HX is 2 days and "prospective" is under 24 hours, still show the record but only with the HX document time. The Prospective time can be supressed or not shown.

GOAL: Show any record that has a time difference over 24 hours and supress all that don't. If the record has none over 24 hours supress the entire record.

 

[PHV]

WHERE ([Date of Appointment] Between [Enter Start Date (ex: mm/dd/yyyy)] And [Enter End Date (ex: mm/dd/yyyy)])
AND ([HX Documents]-[Date of Appointment]>1
OR [Prospective Documents]-[Date of Appointment]>1
OR [External Documents]-[Date of Appointment]>1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jjb373]

PHV,

That works fine, but some of the records have the HX documents with a time over 24 hours and then Prospective and External documents with a times under 24 hours.
This record, as a result, is not shown.
I want it to show but only show the HX document. Supress the other two times which are under 24 hours of the appointment date.

I may need to make 3 seperate queries to accomplish this, but I would like it to only be one. I would not be sure how to create a report from 3 seperate queries that only prompts the user for a start and end date (time span) once.

 

[jjb373]

dhookom, (or anyone else)
In response to your question:
Here is a sample of my Table:
Number Appt.Date/Time HX Docs Prosp.Docs
1 12/5/05 1:15pm 12/6/05 6:30PM this one blank
2 12/5/05 2:00pm 12/6/05 6:35pm 12/6/05 6:45pm
ExternalDocs
12/6/05 10:15am
12/6/05 6:37pm

The documents(docs) time stands for when we received the documents after the appointment. The goal is to display in a report all records and the document(s) that are over the 24 hour time span. Then any docs that are under it supress or hide. If that whole record (all 3 docs) are under the 24 hours supress or hide that record. This is what I am getting with my current query in my report:

num appt.date/time HXdocs ProspDocs ExtrnlDocs
1 12/5/05 1:15pm 1d 5h 15min d h min 0d 21h 0min
2 12/5/05 2:00pm 1d 4h 35min 1d 4h 45min 1d 4h 37min

I want it to look like this:
num appt.date/time HXdocs ProspDocs ExtrnlDocs
1 12/5/05 1:15pm 1d 5h 15min
2 12/5/05 2:00pm 1d 4h 35min 1d 4h 45min 1d 4h 37min

Okay the names of the fields are shown with 2 examples of data under them. I have a query that prompts for a date range (start date & end date) so the user can look at every appt.date/time during a certain month or couple months.
Also in the query i have a datediff function to get the time different between when the appt.date/time is and each respective documents time.

Thanks for the help so far, much appreciated!

 

[PHV]

Something like this ?
SELECT [Record Number], [Date of Appointment]
, IIf([HX Documents]-[Date of Appointment]>1, Int([HX Documents]-[Date of Appointment]) & 'd ' & Format([HX Documents]-[Date of Appointment],'h\h n') & 'min', '') AS [HX Late]
, IIf([Prospective Documents]-[Date of Appointment]>1, Int([Prospective Documents]-[Date of Appointment]) & 'd ' & Format([Prospective Documents]-[Date of Appointment],'h\h n') & 'min', '') AS [Prospective Late]
, IIf([External Documents]-[Date of Appointment]>1, Int([External Documents]-[Date of Appointment]) & 'd ' & Format([External Documents]-[Date of Appointment],'h\h n') & 'min', '') AS [External Late]
FROM Documents
WHERE ([Date of Appointment] Between [Enter Start Date (ex: mm/dd/yyyy)] And [Enter End Date (ex: mm/dd/yyyy)])
AND ([HX Documents]-[Date of Appointment]>1
OR Nz([Prospective Documents],[Date of Appointment])-[Date of Appointment]>1
OR Nz([External Documents],[Date of Appointment])-[Date of Appointment]>1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jjb373]

PHV,

Works great! Much much appreciated.

 

[jjb373]

One more question. My table currently has 12 values that are not "Null" and display in my report. The report comes from the query.
How can I get a total count of all records in my table(null values and non-null values). I count 16 total in the query.

In the report I created an expression that states:
=Count(Nz(Documents![Date of Appointment]))
This gives me the number 12 as I mentioned above. I am looking for the total number from the table which is 16. The last 4 are null values.

 

[CosmoKramer]

In your report try: =Count(*)

Si hoc legere scis, nimis eruditionis habes

 

[jjb373]

The result is still giving me 12. Which is the total number of documents that are not null in the query. I want the total amount of documents in the table.

Here is my code:
=Count(* ( [DocumentsLate]![HX Documents] ))

As a note, the control source data is coming from a query in this report.

 

[Golom]

Count(*) provides a count of ALL records while Count(FieldName) Provides a count of records where FieldName is NOT NULL.

Count(* FieldName) provides the same thing as Count(*) (the field name is ignored.)

 

[CosmoKramer]

In your report try this code exactly like it is, with no field name specified:

=Count(*)

Si hoc legere scis, nimis eruditionis habes

 

[jjb373]

I just did, and it still displays the total count to be 12 which is the amount of records in the query. I want the report to count how many records are in the table which is 16.

Any other suggestions or ideas? Thanks for your help

 

[CosmoKramer]

Earlier you said your query returned a total of 16 records, 12 of which had non-null values in a specific field. Now you say the query returns 12 records altogether.

So, if the query is the record source of your report and you want to count the records in your table instead, try:

=DCount("*","YourTableName")

Si hoc legere scis, nimis eruditionis habes

 

[jjb373]

Thank you very much! That is the trick.

Thanks everyone who put effort in to help me. This will have a direct impact on tracking records at a hospital.

 

[jjb373]

One more request for this. This query above is used for a report.

For each of the documents in this query, HX Documents, Prospective Documents, External Documents...I want to do a count function. I am looking to find the total of records that have data added for each one. Then I need the count for all that are over 24 hours. This is for each of the 3 types of documents.

I may need to get the total count of documents added from the Documents table and the total over 24 hours from the query.

After that I want to divide the 2 totals for that document and find a percentage.

Does anyone have any suggestions?

For example

Table Query result
Appt. Date HX Documents HX > 24hrs time
12/05/05 1:00PM 12/05/05 5:00PM 0d 4hr 0min
12/05/05 1:00PM 12/06/05 3:15PM 1d 2hr 15min
12/05/05 2:00PM 12/07/05 3:30PM 2d 1hr 30min

The total would be: 3 2
Percentage: 67%

I hope this is clear enough.

 

[jjb373]

Any ideas?